How to group by embedded values and set a name to it with total count in mongodb

Question

My Collection :

Sample Collection Json Structure:

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

Goal is to get the count of status for approve and reject for individual busID.

db.suraj_coll.aggregate([
    {"$group" : {_id:{busId:"$value.busId",status:"$value.status.code.value"}, count:{$sum:1}}}
])

The above query returns the output (Sample Output):

{ 
    "_id" : {
        "busId" : 200.0, 
        "status" : "Cancel"
    }, 
    "count" : 2.0
}
{ 
    "_id" : {
        "busId" : 100.0, 
        "status" : "Accept"
    }, 
    "count" : 1.0
}
{ 
    "_id" : {
        "busId" : 100.0, 
        "status" : "Complete"
    }, 
    "count" : 2.0
}

But I would like to put a condition that, whenever it finds the value $in(Complete,Accept), It changes the value to Approved and makes the count 3. I think it can be done using the $cond (aggregation), but finding it difficult to change the name as well,To be more specify , I would like to have the output as :

 { 
        "_id" : {
            "busId" : 200.0, 
            "status" : "Rejected"
        }, 
        "count" : 2.0
    }
    { 
        "_id" : {
            "busId" : 100.0, 
            "status" : "Approved"
        }, 
        "count" : 3
    }
    

So basically whenever it finds Accept/Complete, it increments the value / sum the counts and store against a name Approved. and for cancel changes the value to Rejected.

Any advice/solution will help.

Answer 1

That's great. What you need to do is, just add another field to identify whether its approved or not before grouping, It will make you much easier.

{
    $addFields: {
      "value.available": {
        $cond: [
          {
            $in: [
              "$value.status.code.value",
              [
                "Accept",
                "Complete"
              ]
            ]
          },
          "Approved",
          "Rejected"
        ]
      }
    }
  }

Working Mongo playground