MongoDB - Group Across Two Fields & Then Group On Remaining Third Field

I am using MongoDB (via Mongoose with a NodeJS/Express backend) and have a data structure where I first need to group by two fields, and then once grouped at that level, then group by a third field in the database.

My data structure is as follows (cut down for simplicity):

{
{
   brand: "AMD",
   series: "5000",
   model: "5900x",
   userRating: 5
},
{
   brand: "AMD",
   series: "5000",
   model: "5900x",
   userRating: 2
},
{
   brand: "AMD",
   series: "5000",
   model: "5600x",
   userRating: 3
},
{
   brand: "AMD",
   series: "3000",
   model: "3900x",
   userRating: 5
},
{
   brand: "Intel",
   series: "i9",
   model: "12900k",
   userRating: 5
},
{
   brand: "Intel",
   series: "i9",
   model: "12900k",
   userRating: 4
}
}

As you will note the individual CPU model can repeat multiple times.

As a result, what I am trying to do is as follows:

1. Group by BOTH brand and series

2. Once grouped by brand and series, then group at the individual model level and average the userRating of that model.

Therefore the desired end result of the data is as follows

{
{
   brand: "AMD",
   series: "5000",
   data: [
      {
       model: "5900x"
       avgRating: 3.5
      },
      {
      model: "5600x"
       avgRating: 3
      }      
      ]
},
{
   brand: "AMD",
   series: "3000",
   data: [
      {
       model: "3900x"
       avgRating: 5
      } 
      ]
},
{
   brand: "Intel",
   series: "i9",
   data: [
      {
       model: "12900k"
       avgRating: 4.5
      } 
      ]
}

}

I have tried 100 and one ways to try and get this to work but I am still stumped.

The closest I have managed to get so far on the back of 2 nights looking at this is as follows:

const aggregate = await CpuReviews.aggregate([
      {
        $group: {
          _id: {
            groupId: { series: "$series", brand: "$brand" },
            model: "$model",
            userRating: { $avg: "$userRating" },
          },
        },
      },
      {
        $group: {
          _id: "$_id.groupId",
          data: {
            $push: {
              model: "$_id.model",
              userRating: { $avg: "$_id.userRating" },
            },
          },
        },
      },
    ]);

This ends up in a data structure like this:

{
        "_id": {
            "series": "3000",
            "brand": "amd"
        },
        "data": [
            {
                "model": "Ryzen 9 3950X",
                "userRating": 5678
            }
        ]
    },

I have also tried using $project to rework the data but couldn't take what I have above and get it any closer (I'd typically get thrown an internal server error).

Thanks in advance for any insight you can provide on where I am going wrong and how to get back to the right path.

The problem is you have grouped by all the fields, correct as below,

• $group by series, brand, and model fields and get average
• $group by series and brand fields and construct the data array with model and average field
• $project to show required fields and format the result

const aggregate = await CpuReviews.aggregate([
  {
    $group: {
      _id: {
        series: "$series",
        brand: "$brand",
        model: "$model"
      },
      avgRating: { $avg: "$userRating" }
    }
  },
  {
    $group: {
      _id: {
        series: "$_id.series",
        brand: "$_id.brand"
      },
      data: {
        $push: {
          model: "$_id.model",
          avgRating: "$avgRating"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      series: "$_id.series",
      brand: "$_id.brand",
      data: 1
    }
  }
])

Playground