CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-frameworkgrouping
Gabz
Gabz

Reputation: 97

MongoDB 4.2 Multiple Grouping for Nested results

I am trying to write a nested group query from the following data:

[
    {
        "mmyy": "JAN-2019",
        "location": "Boston",
        "category": "Shoes",
        "qty": 5
    },
    {
        "mmyy": "JAN-2019",
        "location": "New York",
        "category": "Shoes",
        "qty": 10
    },
    {
        "mmyy": "JAN-2019",
        "location": "Boston",
        "category": "Hats",
        "qty": 2
    },
    {
        "mmyy": "JAN-2019",
        "location": "New York",
        "category": "Hats",
        "qty": 3
    },
    {
        "mmyy": "FEB-2019",
        "location": "Boston",
        "category": "Shoes",
        "qty": 5
    },
    {
        "mmyy": "FEB-2019",
        "location": "New York",
        "category": "Hats",
        "qty": 10
    },
]

The desired result:

[
  {
    month: "JAN-2019",
    month_total: 20,
    locations:[
       { 
          name: "Boston", 
          location_total: 7,
          categories: [
            {name: "Shoes", total: 5},
            {name: "Hats", total: 2},
          ]
       }
      ...
    ]
  },
  ...
]

I am able to get up to the second level aggregation (for Month and Location), but having a hard time getting all the aggregations down to category in a nested result. This is what I have done, using multiple $group and $push:

db.sales.aggregate([
   {$group: {
      _id:{ month: "$mmyy", location: "$location"},
      total: { $sum: "$qty"} 
   }},
   { $group:{
      _id: "$_id.month", 
      monthly_total: { $sum: "$total" },
      locations: {
         $push:{
           name: "$_id.location",
           total: "$total"
         }
      }
   }}
])

Upvotes: 1

Views: 63

Answers (1)

Ashh
Ashh

Reputation: 46451

You need to $push the categories in the first $group stage.

db.collection.aggregate([
  { "$group": {
    "_id": {
      "month": "$mmyy",
      "location": "$location",
      "category": "$category"
    },
    "total": { "$sum": "$qty" }
  }},
  { "$group": {
    "_id": {
      "month": "$_id.month",
      "location": "$_id.location"
    },
    "categories": {
      "$push": {
        "name": "$_id.category",
        "total": "$total"
      }
    }
  }},
  { "$group": {
    "_id": "$_id.month",
    "locations": {
      "$push": {
        "name": "$_id.location",
        "categories": "$categories",
        "location_total": {
          "$sum": "$categories.total"
        }
      }
    }
  }}
])

Upvotes: 2

Related Questions