我有以下 MongoDB 檔案。
{
"_id" : ObjectId("62406bfaa1d66f8d99c6e97d"),
"skill": "Programming Language"
"supply" : [
{
"employeeName" : "A1",
"skillRating" : 3
},
{
"employeeName" : "A2",
"skillRating" : 4
},
{
"employeeName" : "A3",
"skillRating" : 4
},
{
"employeeName" : "A4",
"skillRating" : 4
},
{
"employeeName" : "A5",
"skillRating" : 3
},
{
"employeeName" : "A6",
"skillRating" : 4
},
{
"employeeName" : "A7",
"skillRating" : 2
},
{
"employeeName" : "A8",
"skillRating" : 2
},
{
"employeeName" : "A9",
"skillRating" : 4
},
{
"employeeName" : "A10",
"skillRating" : 3
},
{
"employeeName" : "A11",
"skillRating" : 3
},
{
"employeeName" : "A12",
"skillRating" : 3
},
{
"employeeName" : "A13",
"skillRating" : 2
},
{
"employeeName" : "A14",
"skillRating" : 4
},
{
"employeeName" : "A15",
"skillRating" : 4
}
]
}
如何撰寫 Mongodb 查詢以產生以下輸出（即：獲取匹配技能的每個值的出現次數）
{
skillName : "Programming Language",
skillRating1: 0, <-- Count of skillRating with value 1
skillRating2: 3, <-- Count of skillRating with value 2
skillRating3: 5, <-- Count of skillRating with value 3
skillRating4: 7, <-- Count of skillRating with value 4
skillRating5: 0 <-- Count of skillRating with value 5
}
[注：我正在學習撰寫Mongodb查詢]
uj5u.com熱心網友回復：
這是另一個版本，它也報告skillRatings 計數為零。這個聚合管道本質上與@varman 的答案相同，并添加了一個復雜的（無論如何對我來說）"$set"/"$map"來創建額外的欄位。
db.collection.aggregate([
{
"$unwind": "$supply"
},
{
"$group": {
"_id": { "_id": "$_id", "avg": "$supply.avgSkillRating" },
"count": { "$count": {} },
"skillName": { "$first": "$skill" }
}
},
{
"$group": {
"_id": "$_id._id",
"skillName": { "$first": "$skillName" },
"data": {
"$push": {
"_r": "$_id.avg",
"k": { $concat: [ "skillRating", { $toString: "$_id.avg" } ] },
v: "$count"
}
}
}
},
{
"$set": {
"data": {
"$map": {
"input": { "$range": [ 1, 6 ] },
"as": "rate",
"in": {
"$let": {
"vars": {
"idx": { "$indexOfArray": [ "$data._r", "$$rate" ] }
},
"in": {
"$cond": [
{ "$gte": [ "$$idx", 0 ] },
{
"k": {
"$getField": {
"field": "k",
"input": { "$arrayElemAt": [ "$data", "$$idx" ] }
}
},
"v": {
"$getField": {
"field": "v",
"input": { "$arrayElemAt": [ "$data", "$$idx" ] }
}
}
},
{
"k": { $concat: [ "skillRating", { $toString: "$$rate" } ] },
"v": 0
}
]
}
}
}
}
}
}
},
{ "$set": { "data": { "$arrayToObject": "$data" } } },
{ "$replaceWith": { "$mergeObjects": [ "$$ROOT", "$data" ] } },
{ "$unset": [ "data", "_id" ] }
])
試試mongoplayground.net。
uj5u.com熱心網友回復：
你可以去聚合，
$unwind解構陣列$group通過 _id 和 avg 獲得 avg 的總和$arrayToObject在 的幫助下使欄位反對$concat。因為我們需要skillRating1,skillRating2...$replaceRoot獲取物件到根檔案$project決定是否顯示
這是代碼，
db.collection.aggregate([
{ "$unwind": "$supply" },
{
"$group": {
"_id": { _id: "$_id", avg: "$supply.avgSkillRating" },
"count": { "$sum": 1 },
"skill": { "$first": "$skill" }
}
},
{
"$group": {
"_id": "$_id._id",
"skill": { "$first": "$skill" },
"data": {
$push: {
k: {
$concat: [ "avgSkillRating", { $toString: "$_id.avg" } ]
},
v: "$count"
}
}
}
},
{ "$addFields": { "data": { "$arrayToObject": "$data" } } },
{
"$replaceRoot": {
"newRoot": { "$mergeObjects": [ "$$ROOT", "$data" ] }
}
},
{ "$project": { data: 0 } }
])
作業Mongo游樂場
uj5u.com熱心網友回復：
也許是這樣的：
db.collection.aggregate([
{
$unwind: "$supply"
},
{
$group: {
_id: "$supply.avgSkillRating",
cnt: {
$push: "$supply.avgSkillRating"
},
skill: {
$first: "$skill"
}
}
},
{
$project: {
z: [
{
"k": {
"$concat": [
"avgSkillRating",
{
$toString: "$_id"
}
]
},
"v": {
$size: "$cnt"
}
}
],
skill: 1
}
},
{
$replaceRoot: {
newRoot: {
"$mergeObjects": [
{
"$arrayToObject": "$z"
},
{
skillName: "$skill"
}
]
}
}
},
{
$group: {
_id: "$skillName",
x: {
$push: "$$ROOT"
}
}
},
{
"$replaceRoot": {
"newRoot": {"$mergeObjects": "$x"}
}
}
])
解釋：
- 展開供應陣列
- 將 avgSkillRating 分組到陣列 cnt （可以計數）
- 用 k,v 形成 z 陣列，適用于 arrayToObject
- 合并物件以形成鍵和值
- 組加入物件，只留下一個技能名稱
- 用新形成的檔案替換根檔案，并提供必要的詳細資訊。
操場
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/450652.html