MongoDB嵌套聚合：实现各层级按营收取Top N的查询需求

数据结构 #

{
  Region: "Europe",
  Country: "Luxembourg",
  Category: "Snacks",
  Sales Channel: "Offline",
  Order Priority: "C",
  Units Sold: 9357,
  Unit Price: 421.89,
  Unit Cost: 364.69,
  Total Revenue: 3947624.73,
  Total Cost: 3412404.33,
  Total Profit: 535220.4,
}

需求 #

执行聚合查询，返回按营收排序的top n 地区，每个地区下包含按营收排序的top x 国家，每个国家下包含按营收排序的top y 品类，预期返回结构如下：

{
  "Region": "Asia",
  "Total": 12345,
  "Countries": [{
    "country": "China",
    "Total Revenue": 1234,
    "Categories": {
      "Snacks": {
        "Revenue": 123
      },
      "Cosmetics": {
        "Revenue": 123
      }
    }
  }, {
    "country": "India",
    "Total Revenue": 1234,
    "Categories": {
      "Snacks": {
        "Revenue": 123
      },
      "Cosmetics": {
        "Revenue": 123
      }
    }
  }]
}

现有问题 #

已实现按地区分组返回国家总营收的查询，但无法拆分到品类层级。现有查询及返回结果如下：

现有查询 #

[{
    "$group": {
      "_id": {
        "region": "$Region",
        "country": "$Country",
        "type": "$Category"
      },
      "Revenue": {
        "$sum": "$Total Revenue"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.region",
      "Revenue": {
        "$sum": "$Revenue"
      },
      "values": {
        "$push": {
          "country": "$_id.country",
          "Revenue": {
            "$sum": "$Revenue"
          }
        }
      }
    }
  },
  {
    "$project": {
      "values": {
        "$slice": [
          "$values",
          5
        ]
      }
    }
  },
  {
    "$sort": {
      "_id": 1
    }
  },
  {
    "$limit": 2
  }
]

现有返回结果 #

{
  "_id": "Asia",
  "Countries": [{
      "country": "Sri Lanka",
      "Revenue": 731282793.7
    },
    {
      "country": "Vietnam",
      "Revenue": 634208761.24
    }
  ]
}

解决方案 #

通过三层聚合+排序+切片实现需求，以下是完整查询（示例中n=2，x=5，y=2，可根据实际需求调整数值）：

[
  // 1. 按地区-国家-品类分组，计算单个品类的营收总和
  {
    "$group": {
      "_id": {
        "region": "$Region",
        "country": "$Country",
        "category": "$Category"
      },
      "categoryRevenue": { "$sum": "$Total Revenue" }
    }
  },
  // 2. 按地区-国家分组，汇总国家总营收并处理品类的Top y筛选
  {
    "$group": {
      "_id": {
        "region": "$_id.region",
        "country": "$_id.country"
      },
      "countryTotalRevenue": { "$sum": "$categoryRevenue" },
      "categories": {
        "$push": {
          "name": "$_id.category",
          "Revenue": "$categoryRevenue"
        }
      }
    }
  },
  {
    "$addFields": {
      // 对品类按营收降序排序，取Top y（此处y=2）
      "sortedCategories": { "$slice": [{ "$sortArray": { "input": "$categories", "sortBy": { "Revenue": -1 } } }, 2] }
    }
  },
  {
    "$project": {
      "countryTotalRevenue": 1,
      // 将排序后的品类数组转换为预期的键值对格式
      "Categories": {
        "$arrayToObject": {
          "$map": {
            "input": "$sortedCategories",
            "as": "cat",
            "in": { "k": "$$cat.name", "v": { "Revenue": "$$cat.Revenue" } }
          }
        }
      }
    }
  },
  // 3. 按地区分组，汇总地区总营收并处理国家的Top x筛选
  {
    "$group": {
      "_id": "$_id.region",
      "regionTotalRevenue": { "$sum": "$countryTotalRevenue" },
      "countries": {
        "$push": {
          "country": "$_id.country",
          "Total Revenue": "$countryTotalRevenue",
          "Categories": "$Categories"
        }
      }
    }
  },
  {
    "$addFields": {
      // 对国家按营收降序排序，取Top x（此处x=5）
      "sortedCountries": { "$slice": [{ "$sortArray": { "input": "$countries", "sortBy": { "Total Revenue": -1 } } }, 5] }
    }
  },
  // 4. 处理地区的Top n筛选，调整输出字段匹配预期结构
  {
    "$project": {
      "_id": 0,
      "Region": "$_id",
      "Total": "$regionTotalRevenue",
      "Countries": "$sortedCountries"
    }
  },
  {
    "$sort": { "Total": -1 }
  },
  {
    "$limit": 2 // 此处n=2
  }
]

关键说明 #

• $sortArray与$slice：MongoDB 5.0及以上版本支持$sortArray直接对数组排序，配合$slice快速实现Top N筛选；若使用旧版本，可通过$unwind→$sort→$group→$push→$slice的组合替代$sortArray
• $arrayToObject：将品类数组转换为需求中的键值对格式，使返回结构更直观
• 三层聚合逻辑：从最细的品类层级逐步向上汇总到国家、地区，每层完成对应层级的排序与切片，确保最终结果符合要求

内容的提问来源于stack exchange，提问作者sandeep.kgp