2-level group by for objects in an array

[joseph-d-p (Joseph Andrew de la Pena)]

Good day MongoDB Community,

I would like to ask for your help in creating the correct pipeline for my data:

    {
      "group": "A",
      "subgroup": "A1",
      "name": "Abby"
    },
    {
      "group": "A",
      "subgroup": "A2",
      "name": "Andy"
    },
    {
      "group": "A",
      "subgroup": "A2",
      "name": "Amber"
    },
    {
      "group": "B",
      "subgroup": "B1",
      "name": "Bart"
    }

I want to group by group first, then for each group, group by subgroup.
The names will also go to their respective subgroup and the count is showing the actual count.

My expected output is as follows:

  {
    "_id": "B",
    "count": 1,
    "subgroup": [
      {
        "_id": "B1",
        "count": 1,
        "names": ["Bart"]
      }
    ]
  },
  {
    "_id": "A",
    "count": 3,
    "subgroup": [
      {
        "_id": "A1",
        "count": 1,
        "names":[ "Abby"]
      },
      {
        "_id": "A2",
        "count": 2,
        "names": ["Amber", "Andy"]
      }
    ]
  }

The aggregation and actual output can be seen in the playground:

Mongo playground

[SourabhBagrecha]

Hi @joseph-d-p, welcome back to the community.
Based on the expected output and input provided by you, you can use a combination of two group stages to achieve this.
Take a look at the following pipeline:

db.nestedGroup.aggregate([
  {
    $group: {
      _id: "$subgroup",
      topGroup: {$first: "$group"},
      names: {
        $push: "$name"
      }, 
      count: {
        $sum: 1
      }
    }
  }, 
  {
    $group: {
      _id: "$topGroup", 
      subGroup: {$push: "$$ROOT"}, 
      count: {$sum: "$count"}
    }
  },
  {
    $project: {
      count: 1,
      "subGroup.names": 1,
      "subGroup.count": 1,
      "subGroup._id": 1,
    }
  }
])

Here, the first group stage is to group the subgroups like B1, A2 etc, and collect the names in an array.
The second group stage is there to group those subgroups on the basis of their respective topGroup values like: A, B etc while simultaneously summing up the total count.

Please note that this is untested, so you might want to do your own testing to ensure it works with your data and all the edge cases are handled correctly .

If you have any doubts, please feel free to reach out to us.

Thanks and Regards.
Sourabh Bagrecha,
MongoDB