Nested group in mongodb aggregation pipeline

Question

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

Your out come is still unclear to me. Why other documents don't have types array. And why the one with types array have no details but it is nested in documents inside array. — Hussam
– Hussam, Commented Feb 14, 2022 at 11:55
The example above is a simplification to explain what I want: if you run the query in the mongodb playground you'll see all the categories grouped with all the subcategories inside..but you'll find subcategory with the same name like "Restaurants"..I want also them to be grouped in a array that I call "types". The array "types" must be created only if there are more than one subcategory with the same name..hope I explain myself — user16469315
– user16469315, Commented Feb 14, 2022 at 12:07
I suggest that you keep your interface consistent, keep the types array if there is no document keep it empty. — Hussam
– Hussam, Commented Feb 14, 2022 at 12:13

Hussam · Accepted Answer · 2022-02-14 12:16:44Z

1

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

answered Feb 14, 2022 at 12:16

Hussam

1,72612 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user16469315 Over a year ago

Thank you, exactly what I need

Collectives™ on Stack Overflow

Nested group in mongodb aggregation pipeline

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related