温馨提示:本文翻译自stackoverflow.com,查看原文请点击:其他 - MongoDB 4.2 Multiple Grouping for Nested results
aggregation-framework grouping mongodb mongodb-query

其他 - MongoDB 4.2多重分组以获取嵌套结果

发布于 2020-03-31 23:40:44

我正在尝试从以下数据编写嵌套的组查询:

[
    {
        "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
    },
]

预期结果:

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

我能够进行第二级汇总(针对“月份”和“位置”),但是很难将所有汇总归类到嵌套结果中。这是我使用多个$group做的$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"
         }
      }
   }}
])

查看更多

提问者
Gabz
被浏览
165
Ashh 2020-01-31 22:04

你需要$pushcategories第一$group阶段。

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"
        }
      }
    }
  }}
])