Nested groups with aggregation framework

Question

In my collection I have an amount of documents that each contains 4 types of categories.

group
mainCategory
subCategory
subSubCategory

I'm looking for a way to $group this by group and inside group there should be an array of mainCategory, in mainCategory there should be an array of subCategory and in the subCategory there should be an array of the subSubCategory. The result should only contain the values/names of the categories.

Expected result:

const result = [
            {
              "group": "Teknik",
              "mainCategory": [
                {
                  "name": "Gaming",
                  "subCategory": [
                    {
                      "name": "Playstation",
                      "subSubCategory": [
                        {"name": "Games"},
                        {"name": "Accessories"},
                        {"name": "Console"}
                      ]
                    },
                    {
                      "name": "Xbox",
                      "subSubCategory": [
                        {"name": "Games"},
                        {"name": "Accessories"},
                        {"name": "Console"}
                      ]
                    }
                  ]
                }, {
                  "name": "Audio",
                  "subCategory": [
                    {
                      "name": "Headphones",
                      "subSubCategory": [
                        {"name": "Wireless"},
                        {"name": "Non Wireless"},
    
                      ]
                    },
                    {
                      "name": "Speakers",
                      "subSubCategory": [
                        {"name": ""},
                      ]
                    }
                  ]
                },
    
              ]
            }
]

I think the problem is that I need to $push each and every category in the parent one and create new $groups? But when adding more $groups it's only the last one that is "saved". The other ones seems to not be "saved". Or is something else that im doing wrong?

Sample data and working code based on prev. test data.

https://mongoplayground.net/p/C0-L-rGPfwy

Product structure in the collection

productName :"Horizon Zero Dawn"
group:"Teknik"
mainCategory: "Gaming"
subCategory:"Playstation"
subSubCategory: "Games"

My aggregation

const categories = await Product.aggregate([
        {$match: {group: 'Teknik'}},
        {
          $group: {
            _id: {
              group: '$group',
              mainCategory: '$mainCategory',
              subCategory: '$subCategory',
              subSubCategory: '$subSubCategory',
            }
          },
        },
        {
          $group: {
            _id: "$_id.group",
            mainCategory: {
              $push:
                {
                  name: "$_id.mainCategory",
                  subCategory: {
                    name: "$_id.subCategory",
                    subSubCategory: {
                      name: "$_id.subSubCategory"
                    }
                  }
                }
            },
          }
        },
      ]);

With this i get 1 object for each subSubcategory, but i would like them in the same object.

"_id": "Teknik",
    "mainCategory": [
    {
            "name": "Gaming",
            "subCategory": {
                "name": "Xbox",
                "subSubCategory": {
                    "name": "Games"
                }
            }
        },
    {
            "name": "Spel & Gaming",
            "subCategory": {
                "name": "Xbox",
                "subSubCategory": {
                    "name": "Accessories"
                }
            }
        },
]

@turivishal It's in the top of the post the const result = [...] But clarified it now. — GameAtrix1
– GameAtrix1, Commented Aug 11, 2020 at 8:34
There is around 40 000 documents so i added some sample data in a link above. mongoplayground.net/p/QjjPTuQWspt . — GameAtrix1
– GameAtrix1, Commented Aug 11, 2020 at 8:47

turivishal · Accepted Answer · 2020-08-11 11:00:43Z

1

The process is to prepare from end level of array:

subSubCategory (group by below 3 level of fields)

subCategory (group by below 2 level of fields and push above prepared level 1)

mainCategory (group by below 1 level of field and push above prepared level 2)

group (project and show prepared level 3)

Lets look step by step,

your $match condition

db.collection.aggregate([
  { $match: { group: "Teknik" } },

$group by with only 3 main fields, don't add subSubCategory
this group prepare subSubCategory array

  {
    $group: {
      _id: {
        group: "$group",
        mainCategory: "$mainCategory",
        subCategory: "$subCategory"
      },
      subSubCategory: {
        $push: { name: "$subSubCategory" }
      }
    }
  },

in above $group there will be chance of duplicate subSubCategory so this will remove duplicate, if you don't want to remove then you can skip this part

  {
    $addFields: {
      subSubCategory: { $setUnion: ["$subSubCategory", [] ] }
    }
  },

now $group by only 2 main fields
this will prepares subCategory array, push name and subSubCategory array that we have prepared in above group

  {
    $group: {
      _id: {
        group: "$_id.group",
        mainCategory: "$_id.mainCategory"
      },
      subCategory: {
        $push: {
          name: "$_id.subCategory",
          subSubCategory: "$subSubCategory"
        }
      }
    }
  },

$group by main group field
this will prepare mainCategory array, push name and subCategory array that we have prepared in above group

  {
    $group: {
      _id: "$_id.group",
      mainCategory: {
        $push: {
          name: "$_id.mainCategory",
          subCategory: "$subCategory"
        }
      }
    }
  },

$project to remove _id and show group and mainCategory fields

  {
    $project: {
      _id: 0,
      group: "$_id",
      mainCategory: 1
    }
  }
])

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

edited Aug 11, 2020 at 11:00

answered Aug 11, 2020 at 9:49

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

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

5 Comments

GameAtrix1 Over a year ago

Thanky you very much! Works as intended with the data i provided. However, i noticed that I've missed to inlcude testdata that share the group but not the mainCategory. sorry about that. Ive updataded the mongoplayground with more accurate testdata and with your code. I would really appricate it if you could give me som pointers how to fix this.mongoplayground.net/p/C0-L-rGPfwy

turivishal Over a year ago

i can see subSubCategory is empty string in many documents, what is your expectation for that? and i can see that single point, let me know if i am missing.

GameAtrix1 Over a year ago

The empty string is there because it feels simpler to have an empty value instead of checking if the element exists or not every time. Some products have subSub and some don't. Sorry, I dont understand your last sentence?

GameAtrix1 Over a year ago

I'm expecting the group name "teknik" only once, and then should mainCategory be an array with all the mainCategories (Mobil, Spel & Gaming, Ljud & Bild) in them. I do not want to remove main object when the subSub is empty as in above.

turivishal Over a year ago

i have updated answer you can check, the problem was in last group, need to group by only group field, let me know if is there any problem,

Collectives™ on Stack Overflow

Nested groups with aggregation framework

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related