Nested group in mongodb aggregation pipeline-CodePudding

I'm using Mongo DB and I have a list of object that I need to group in category -> subcategory -> different types of subcategory grouped together. You can find the code in this mongodb playground > https://mongoplayground.net/p/BfR7FT28XKN

I create the group category -> all the sub category. But how can I group the subcategory with the same name?

My goal is to achieve something like that:

{
    "_id": "Food and Drink",
    "subCategories": [
      {
        "details": "Wine Bar",
        "subCategory": "Bar",
      },
      {
        "details": "Jazz and Blues Cafe",
        "subCategory": "Nightlife",
      },
      {
        "subCategory": "Restaurants",
        "types": [
              {
                "details": "Mediterranean",
                "subCategory": "Restaurants",
              },
              {
                "details": "Mexican",
                "subCategory": "Restaurants",
              }
           ]
       },
      
    ]
  }

Thanks

CodePudding user response：

Here's what you need. You need to group by both category and sub category first. I have pushed complete document in types. You can filter the fields or do changes as per your requirements.

[{
"$match": {
  "category": {
    "$ne": null
  },
  "subCategory": {
    "$ne": null
  }
 }
},
{
 $group: {
  _id: {
    cat: "$category",
    sub: "$subCategory",
    
   },
   docs: {
    $push: "$$ROOT"
   }
 }
},
{
$group: {
  _id: "$_id.cat",
  subCategories: {
    $push: {
      subCategory: "$_id.sub",
      types: "$docs"
    }
  }
 }
}]

Remove types: "$docs" from last stage and this is what you'd get

[{
"_id": "Community",
"subCategories": [
  {
    "subCategory": "Education"
  },
  {
    "subCategory": "Disabled Persons Services"
  }
 ]
},
{
 "_id": "Food and Drink",
 "subCategories": [
  {
    "subCategory": "Bar"
  },
  {
    "subCategory": "Nightlife"
  },
  {
    "subCategory": "Restaurants"
  }
 ]
}]

Playground https://mongoplayground.net/p/nwyAivJOZj5