How to group documents to-CodePudding

I have the following documents in my permission collection,

[
  {
    _id: new ObjectId("62f695063cf6055e6a50fd6d"),
    permissionGroupName: 'User Management',
    permissionName: 'Create User',
    __v: 0
  },
  {
    _id: new ObjectId("62f695063cf6055e6a50fd6f"),
    permissionGroupName: 'User Management',
    permissionName: 'Edit User',
    __v: 0
  },
  {
    _id: new ObjectId("62f695063cf6055e6a50fd71"),
    permissionGroupName: 'User Management',
    permissionName: 'Delete User',
    __v: 0
  },
  {
    _id: new ObjectId("62f695073cf6055e6a50fd73"),
    permissionGroupName: 'User Management',
    permissionName: 'Approve User',
    __v: 0
  },
  {
    _id: new ObjectId("62f8a73c79a839650a183182"),
    permissionGroupName: 'Country Management',
    permissionName: 'Approve Country',
    __v: 0
  },
  {
    _id: new ObjectId("62f8ad3c79a839650a113184"),
    permissionGroupName: 'Country Management',
    permissionName: 'Delete Country',
    __v: 0
  },
]

I am trying to group the permissionNames by the permissionGroupName using the aggregation pipeline.

For example, I want to transform the above documents to,

[
  {
    permissionGroupName: "User Managemet",
    permissions: ["Create User", "Edit User", "Delete User", "Approve User"],
  },
  {
    permissionGroupName: "Country Managemet",
    permissions: ["Approve Country", "Delete Country"],
  },
];

What I have tried

I tried the following code,

[
      {
        $group: {
          _id: {
            permissionGroupName: "$permissionGroupName",
            permissionName: "$permissionName",
          },
        },
        $group: {
          _id: "$permissionGroupName",
          permissions: {
            $push: {
              permission: "$_id.permissonName",
            },
          },
        },
      },
    ]

and received an invalid output as below,

[
  { _id: 'Country Management', permissions: [ {} ] },
  { _id: 'User Management', permissions: [ {}, {}, {}, {} ] }
]

Can someone please help?

Thank You.

CodePudding user response：

you just need one $group stage and a $project

group by permissionGroupName and push permissionName into permissions and then use $project to get your desired format

db.collection.aggregate([
  {
    "$group": {
      "_id": "$permissionGroupName",
      "permissions": {
        "$push": "$permissionName"
      }
    }
  },
  {
    "$project": {
      "permissionGroupName": "$_id",
      "permissions": 1,
      "_id": 0
    }
  }
])

test it here