i am trying to return aggregation result based on user's role and active status sample doc

{
 "email": "[email protected]",
 "firstName": "doe",
 "lastName": "bghh",
 "phone": " 919016703350",
 "password":"$.5GIV3q9JqzRqY/lP2",
 "status": "verified",
 "role": "admin",
 "isActive": true
}

 const user = await User.aggregate([
    { $project: { isActive: 1, role: 1 } },
    { $group: { _id: { role: '$role', isActive: '$isActive' }, all: { $sum: 1 } } },
 ])

above query result below

 {
  "user": [
    {
        "_id": {
            "role": "user",
            "isActive": false
        },
        "all": 1
    },
    {
        "_id": {
            "role": "vendor",
            "isActive": true
        },
        "all": 1
    },
    {
        "_id": {
            "role": "user",
            "isActive": true
        },
        "all": 2
    },
    {
        "_id": {
            "role": "vendor",
            "isActive": false
        },
        "all": 1
    },
    {
        "_id": {
            "role": "admin",
            "isActive": true
        },
        "all": 1
    }
 ]

}

expecting result like, help to write query to achieve below result

{ role:{user:{all:3, active:2 }, admin:{all:1, active:1}, vendor:{all:2, active:2}}}

CodePudding user response：

Try this one:

db.collection.aggregate([
   { $project: { isActive: 1, role: 1 } },
   {
      $group: {
         _id: '$role',
         all: { $sum: 1 },
         active: { $sum: { $cond: ['$isActive', 1, 0] } }
      }
   },
   {
      $group: {
         _id: null,
         role: { $push: "$$ROOT" }
      }
   },
   {
      $set: {
         role: {
            $map: {
               input: "$role",
               in: { k: "$$this._id", v: { all: "$$this.all", active: "$$this.active" } }
            }
         }
      }
   },
   { $set: { role: { $arrayToObject: "$role" } } },
   { $replaceWith: { role: "$role" } }
])

Mongo Playground