Good day SO Community,

I would like to ask for your help in creating the correct aggregation pipeline for a sample 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"]
      }
    ]
  }

I have tried this pipeline but it's not grouping the subgroups.

  {
    "$group": {
      "_id": "$group",
      "subgroup": {
        "$addToSet": {
          "_id": "$subgroup",
          "name": "$name",
          count: {
            $sum: 1
          }
        }
      },
      count: {
        $sum: 1
      }
    }
  }

The aggregation pipeline and actual output can be seen in the playground: https://mongoplayground.net/p/MO1fCf21Rez

Thank you!

CodePudding user response：

1. $group - Group by group and subgroup. Perform count and add name into names array.

2. $group - Group by group. Perform total count and add the object for subgroup into subgroup array.

db.students.aggregate([
  {
    $group: {
      _id: {
        group: "$group",
        subgroup: "$subgroup"
      },
      names: {
        $push: "$name"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.group",
      "subgroup": {
        $addToSet: {
          "_id": "$_id.subgroup",
          "names": "$names",
          count: "$count"
        }
      },
      count: {
        $sum: "$count"
      }
    }
  }
])

Demo @ Mongo Playground