Warm tip: This article is reproduced from stackoverflow.com, please click
aggregation-framework grouping mongodb mongodb-query

MongoDB 4.2 Multiple Grouping for Nested results

发布于 2020-03-31 22:59:46

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"
         }
      }
   }}
])
Questioner
Gabz
Viewed
99
分享
Ashh 2020-01-31 22:04

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"
        }
      }
    }
  }}
])
Gabz 2020-01-31 21:27:38

Actually there seems to be a strange behaviour when I do this. Under each "Location", I get a listing of duplicate "categories", where the same category occurs multiple times with the total split between them.

Ashh 2020-01-31 21:29:20

Could you show me that here mongoplayground.net/p/lxnRgQatfYx

Gabz 2020-01-31 21:52:00

Sure. It happens if you have multiple entries for each "category" for a location during any month. see here: https://mongoplayground.net/p/J7sIWG5i0My

Ashh 2020-01-31 22:04:32

Updated my answer

Related issues

1
Mongoose: CastError at path "_id" when ID is not being called
2
Mongoose: How to get documents that their subdocument includes a value ( Many to one relationship )
3
How to count specific documents in a row using aggregation?
4
Mongo db Collection find returns from the first entry not from last from client side
5
MongoDB object type is not saved
6
MongoDB $lookup and $map array of objects
7
Nodejs Expressjs Mongodb Javascript Unit Test Using Mocha Chai Sinon for random Nodejs Project
8
Using Aggregate function in MongoDB to Divide and Mutliply
9
mongorestore uncaught exception: syntax error
10
How to get deleted documents from DeleteMany middleware in Mongoose?