How to get group with groupby get the result with dynamic status in mongoDB

Question

I have the collection like below

{
    "_id" : ObjectId("5b6538704ba0292b6c197770"),
    "Name":"Name1",
    "Status":"Good",


},
{
    "_id" : ObjectId("5b6538704ba0292b6c197773"),
     "Name":"Name2",
    "Status":"Bad"
},
{
    "_id" : ObjectId("5b6538704ba0292b6c197774"),
    "Name":"Name3",
    "Status":"Bad"
},{
    "_id" : ObjectId("5b6538704ba0292b6c197774"),
    "Name":"Name1",
    "Status":"Bad"
},
{
    "_id" : ObjectId("5b6538704ba0292b6c197775"),
     "Name":"Name1",
    "Status":"Good"
}

I have used the query to get the status wise count like below

db.Students.aggregate( [

  { $group: {   _id:  { 
            "Name": "$Name","Status":"$Status"}, StatusCount: { $sum: 1 } } }  
        ,  { "$project": { _id: 0, Name: "$_id.Name",Status : "$_id.Status", StatusCount:1 } }
] );

The result was

{
    "Name":"Name1",
    "StatusCount" : 2,
    "Status" : "Good"

},
{
    "Name":"Name2",
    "StatusCount" : 1,
    "Status" : "Bad"       
}, {
    "Name":"Name2",
    "StatusCount" : 1,
    "Status" : "Bad"       
}, 
{
    "Name":"Name1",
    "StatusCount" : 1,
    "Status" : "Bad"       
}

The result what I am approaching is like

   {
        "Name":"Name1",
        "Good" : 2,
        "Bad"   :1        
    },
{
        "Name":"Name2",
        "Good" : 0,
        "Bad"   :1  
}

The result I am expecting to have the status of field names and count as its values. I have tried to do this but I could not make it happen. The status, for now, is only two like Good or Bad but may increase in real dataset.

Answer 1

By using the $arrayToObject operator and a final $replaceRoot pipeline step which has a $mergeObjects operator you will get your desired result.

You would need to run the following aggregate pipeline on MongoDB Server 3.4.4 or newer:

const pipeline = [
    { '$group': {
        '_id': {
            'Name': '$Name',
            'Status': '$Status'
        },
        'StatusCount': { '$sum': 1 }
    } },
    { '$group': {
        '_id': '$_id.Name',
        'counts': {
            '$push': {
                'k': '$_id.Status',
                'v': '$StatusCount'
            }
        }
    } },
    {  '$replaceRoot': {
        'newRoot': { '$mergeObjects': [ 
            { '$arrayToObject': '$counts' }, 
            { 'Name': '$_id' } 
        ] }
    } } 
];

db.Students.aggregate(pipeline);