基于匹配字符串，使用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查询]

[说明：我正在学习编写Mongodb查询]

- Mahavir Munot

Answer 1

也许是这样的：

或许可以像这样：

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"}
  }
 }
])

解释：

1. 将供应数组展开
2. 将avgSkillRating分组到数组cnt中（以便进行计数）
3. 使用适合于arrayToObject的k,v形式形成z数组
4. mergeObjects以形成键和值
5. 分组以连接对象并仅保留单个skillName
6. 用新形成的文档替换根文档，其中包含必要的细节。

playground

- R2D2

Answer 2

您可以使用聚合操作：

• $unwind 将数组拆分为单个元素
• $group 按 _id 分组并获取平均值的总和
• $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进行工作

- varman

Answer 3

这里有另一个版本，它还报告零计数的skillRating。这个汇聚管道本质上与@varman's answer相同，并添加了一个复杂（对我来说）的"$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。

- rickhg12hs

---

这是一个很好的答案，我无法做到，因为范围没有指定。 - varman