How to do muliple grouping in MongoDB?

Question

I have been trying to achieve a result for my mongodb documents. My documents look like this:

{
    "_id" : "97f98668-bdc0-441c-ab12-15c0f4313e30",
    "row_id" : "927b1d4f-6357-44af-8cdb-daca7a6f89fd",
    "data" : {
        "info" : [ 
            {
                "name" : "dog",
                "value" : [ 
                    "3"
                ],
                "score" : 42
            }
        ]
    }
}

/* 2 */
{
    "_id" : "f1994888-8c22-4d29-8523-4ff3c504665a",
    "row_id" : "927b1d4f-6357-44af-8cdb-daca7a6f89fd",
    "data" : {
        "info" : [ 
            {
                "name" : "dog",
                "value" : [ 
                    "2"
                ],
                "score" : 70
            }
        ]
    }
}

/* 3 */
{
    "_id" : "6522979b-68f3-4f62-859c-93786b23caaa",
    "row_id" : "927b1d4f-6357-44af-8cdb-daca7a6f89fd",
    "data" : {
        "info" : [ 
            {
                "name" : "cat",
                "value" : [ 
                    "2"
                ],
                "score" : 62
            }
        ]
    }
}

I want to group them by their row_id first and then by info.name. The final structure looks like this:

{
    "row_id": "927b1d4f-6357-44af-8cdb-daca7a6f89fd",
    "data":{
        "info" : [ 
            {
                "name" : "dog",
                "values" : [ 
                    {"value": "2", "score": 70},
                    {"value": "3", "score": 42}
                ]
            },
            {
                "name" : "cat",
                "values" : [ 
                    {"value": "2", "score": 62}
                ]
            },
        ]
    }
}

I tried to do multiple grouping but didn't work as expected

Answer 1

Since you have nested objects in arrays, you'll have to $unwind the arrays first. The you can do $group by row_id and name first, followed by $group by row_id only

db.collection.aggregate([
  {
    $unwind: "$data.info" // unwind array first, you cannot group on objects inside an array
  },
  {
    $unwind: "$data.info.value" // unwind value to have only one value per record
  },
  {
    $group: {
      _id: { // group by row_id and name
        row_id: "$row_id",
        name: "$data.info.name"
      },
      values: {
        $push: {
          value: "$data.info.value",
          score: "$data.info.score"
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id.row_id", // group again by row_id only
      data_info: {
        $push: {
          name: "$_id.name",
          values: "$values"
        }
      }
    }
  },
  {
    $project: { // project to desired shape
      _id: false,
      row_id: "$_id",
      data: {
        info: "$data_info"
      }
    }
  }
])

Mongo Playground