Multiple grouping in mongodb

Question

Sample Colloection Data :

  { 
    "_id" : ObjectId("5f30df23243ffsdfwer3d14568bf"), 
    "value" : {
        "busId" : 200.0, 
        "status" : {
            "code" : {
                "id" : 1.0, 
                "key" : "2100", 
                "value" : "Complete"
            }
        }
    }
}

My Query does provides the right result, but would like to squeeze the output more by using multiple grouping or $project or any other aggregators.

mongo Query:

db.suraj_coll.aggregate([
  {
    $addFields: {
      "value.available": {
        $cond: [
          {
            $in: [
              "$value.status.code.value",
              [
                "Accept",
                "Complete"
              ]
            ]
          },
          "Approved",
          "Rejected"
        ]
      }
    }
  },
  {
    "$group": {
      "_id": {
        busID: "$value.busId",
        status: "$value.available"
      },
      "subtotal": {
        $sum: 1
      }
    }
  }
  
])

Output:

/* 1 */
{
    "_id" : {
        "busID" : 200.0,
        "status" : "Approved"
    },
    "subtotal" : 3.0
}    
/* 2 */
{
    "_id" : {
        "busID" : 200.0,
        "status" : "Rejected"
    },
    "subtotal" : 1.0
}

Is it possible to squeeze the output more by using any further grouping ? Output should look like below

  {
    "_id" : {
        "busID" : 200.0,
        "Approved" : 3.0
        "Rejected" : 1.0   
    }
}

tried with $project, by keeping the count in a doc , but couldn't place the count against Approve or Rejected. Any suggestion would be great.

Answer 1

You can use more two pipelines after your query,

• $group by busID and push status and count in status
• $project to convert status array to object using $arrayToObject and merge with busID using $mergeObjects

  {
    $group: {
      _id: "$_id.busID",
      status: {
        $push: {
          k: "$_id.status",
          v: "$subtotal"
        }
      }
    }
  },
  {
    $project: {
      _id: {
        $mergeObjects: [
          { busID: "$_id" },
          { $arrayToObject: "$status" }
        ]
      }
    }
  }

Playground