Mongodb查询以根据匹配字符串的值获取字段计数

Question

我有以下 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 查询]

Answer 1

你可以使用聚合，

• $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 playground