Mongodb Aggregate Nested Group

Question

I have assigned person, status inside my collection simply like below

[
 {"ASSIGN_ID": "583f84bce58725f76b322398", "STATUS": 1},
 {"ASSIGN_ID": "583f84bce58725f76b322398","STATUS": 4},
 {"ASSIGN_ID": "583f84bce58725f76b322398","STATUS": 4},
 {"ASSIGN_ID": "583f84bce58725f76b322398","STATUS": 3},
 {"ASSIGN_ID": "583f84bce58725f76b322311","STATUS": 1},
 {"ASSIGN_ID": "583f84bce58725f76b322311","STATUS": 3},
 {"ASSIGN_ID": "583f84bce58725f76b322322","STATUS": 1},
 {"ASSIGN_ID": "583f84bce58725f76b322322","STATUS": 4}
]

i want to group this data by ASSIGN_ID and inside that by STATUS count of each STATUS same as below.

[  
   {  
      "ASSIGN_ID":"583f84bce58725f76b322398",
      "STATUS_GROUP":[  
         {  
            "STATUS":1,
            "COUNT":1
         },
         {  
            "STATUS":3,
            "COUNT":1
         },
         {  
            "STATUS":4,
            "COUNT":2
         }
      ]
   },
   {  
      "ASSIGN_ID":"583f84bce58725f76b322311",
      "STATUS_GROUP":[  
         {  
            "STATUS":1,
            "COUNT":1
         },
         {  
            "STATUS":3,
            "COUNT":1
         }
      ]
   },
   {  
      "ASSIGN_ID":"583f84bce58725f76b322322",
      "STATUS_GROUP":[  
         {  
            "STATUS":1,
            "COUNT":1
         },
         {  
            "STATUS":4,
            "COUNT":1
         }
      ]
   }
]

however, i have write code and it only grouped by STATUS only. please find the query below.

Inspection.aggregate([
    {$group: {
    "_id": '$STATUS',
    "count" :  { $sum : 1 }}}], function (err, result) {
}});

please help me to fix this.

Solution

Use this command, it'll work for your sample case, hope it'll fix your problem ,

db.testCollection.aggregate([{
    $group: {
        _id: {
            ASSIGN_ID: "$ASSIGN_ID",
            STATUS: "$STATUS"
        },
        count: {
            "$sum": 1
        }
    }
}, {
    $group: {
        _id: "$_id.ASSIGN_ID",
        STATUS_GROUP: {
            $push: {
                STATUS: "$_id.STATUS",
                count: "$count"
            }
        }
    }
}])

output in shell :

{
    "_id": "583f84bce58725f76b322398",
    "STATUS_GROUP": [{
        "STATUS": 3,
        "count": 1
    }, {
        "STATUS": 4,
        "count": 2
    }, {
        "STATUS": 1,
        "count": 1
    }]
}, {
    "_id": "583f84bce58725f76b322311",
    "STATUS_GROUP": [{
        "STATUS": 3,
        "count": 1
    }, {
        "STATUS": 1,
        "count": 1
    }]
}, {
    "_id": "583f84bce58725f76b322322",
    "STATUS_GROUP": [{
        "STATUS": 1,
        "count": 1
    }, {
        "STATUS": 4,
        "count": 1
    }]
}

Comments

[Sameera Danthanarayna]

Thanks, it does work! also suppose the collection has another column. how do i add that particular column to this result. hope you got my question

[Lokesh Bajracharya]

can someone suggest me how about we also want the total count of all the status in the same iteration?

[Otis-iDev]

Thank you for this it really saved me a lot of research time.