skip to Main Content

I have the following Mongodb document.

{
  "_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
    }
  ]
}

How can I write a Mongodb query to produce the following output (i.e.: Get the count of occurrence of each value for a matching skill)

{
  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
}
[Note: I am learning to write Mongodb queries]

3

Answers


  1. 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:

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

    playground

    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
  3. Here’s another version that also reports skillRatings with a zero count. This aggregation pipeline is essentially identical to @varman’s answer and adds a complex (to me anyway) "$set"/"$map" to create the extra fields.

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

    Try it mongoplayground.net.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search