Mongodb query to get count of field based on the value-CodePudding

I have the following Mongodb document.

{
  "_id" : ObjectId("62406bfaa1d66f8d99c6e97d"),
  "skill": "Programming Language"
  "supply" : [
    {
        "employeeName" : "A1",
        "avgSkillRating" : 3
    }, 
    {
        "employeeName" : "A2",
        "avgSkillRating" : 4
    }, 
    {
        "employeeName" : "A3",
        "avgSkillRating" : 4
    }, 
    {
        "employeeName" : "A4",
        "avgSkillRating" : 4
    }, 
    {
        "employeeName" : "A5",
        "avgSkillRating" : 3
    }, 
    {
        "employeeName" : "A6",
        "avgSkillRating" : 4
    }, 
    {
        "employeeName" : "A7",
        "avgSkillRating" : 2
    }, 
    {
        "employeeName" : "A8",
        "avgSkillRating" : 2
    }, 
    {
        "employeeName" : "A9",
        "avgSkillRating" : 4
    }, 
    {
        "employeeName" : "A10",
        "avgSkillRating" : 3
    }, 
    {
        "employeeName" : "A11",
        "avgSkillRating" : 3
    }, 
    {
        "employeeName" : "A12",
        "avgSkillRating" : 3
    }, 
    {
        "employeeName" : "A13",
        "avgSkillRating" : 2
    }, 
    {
        "employeeName" : "A14",
        "avgSkillRating" : 4
    }, 
    {
        "employeeName" : "A15",
        "avgSkillRating" : 4
    }
  ]
}

How can I write a Mongodb query to produce the following output.

{
  skillName : "Programming Language",
  skillRating1: 0,  <-- Count of avgSkillRating with value 1
  skillRating2: 3,  <-- Count of avgSkillRating with value 2
  skillRating3: 5,  <-- Count of avgSkillRating with value 3
  skillRating4: 7,  <-- Count of avgSkillRating with value 4
  skillRating5: 0  <-- Count of avgSkillRating with value 5
}

[Note: I am learning to write Mongodb queries]

CodePudding user response：

You can go with aggregation,

$unwind to deconstruct the array
$group to get the sum of avg by _id and the avg
$arrayToObject to make the field to object with the help of $concat. Because we need the skillRating1,skillRating2...
$replaceRoot to get the object to root document
$project to decide whether to show or not

Here is the code,

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

Working Mongo playground

CodePudding user response：

Maybe something like this:

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

Explained:

Unwind the supply array
group avgSkillRating to array cnt ( to be possible to count )
form z array with k,v suitable for arrayToObject
mergeObjects to form the keys and values
group to join the objects and leave only single skillName
replace the root document with the newly formed document with the necesary details.

playground