MongoDB中按年份键值合并子文档的实现方案

问题原因 #

你当前的写法会导致合并后数据丢失，核心原因是：三个分组生成的对象都以年份作为顶级键，当$mergeObjects处理这些对象时，后处理的对象会覆盖前面相同年份键对应的整个值，最终只剩最后一个scope的数据。

正确聚合方案 #

我们需要先将不同scope的排放数据统一格式，拆分到同一层级，再按年份合并所有scope字段，最终整理成目标结构。以下是完整的聚合流程（假设你的原文档结构如下）：

原文档示例

{
  "companyId": "comp_001",
  "ghg_emissions_scope1": [{"year": 2020, "value": 150}, {"year": 2021, "value": 165}],
  "ghg_emissions_scope2": [{"year": 2020, "value": 230}, {"year": 2021, "value": 245}],
  "ghg_emissions_scope3": [{"year": 2020, "value": 410}, {"year": 2021, "value": 430}]
}

聚合查询步骤

1. 统一格式并合并数组
   将三个scope的排放数据转换成包含年份和对应排放值的统一结构，再合并为单个数组：

{
  $project: {
    companyId: 1,
    emissions: {
      $concatArrays: [
        {
          $map: {
            input: "$ghg_emissions_scope1",
            as: "s1",
            in: { year: "$$s1.year", scope1: "$$s1.value" }
          }
        },
        {
          $map: {
            input: "$ghg_emissions_scope2",
            as: "s2",
            in: { year: "$$s2.year", scope2: "$$s2.value" }
          }
        },
        {
          $map: {
            input: "$ghg_emissions_scope3",
            as: "s3",
            in: { year: "$$s3.year", scope3: "$$s3.value" }
          }
        }
      ]
    }
  }
}

2. 展开数组
   将合并后的数组拆分为单个文档，方便后续按年份分组：

{ $unwind: "$emissions" }

3. 按年份合并排放字段
   以companyId和year为分组键，用$mergeObjects合并同一年份的所有scope数据：

{
  $group: {
    _id: { companyId: "$companyId", year: "$emissions.year" },
    yearEmissions: { $mergeObjects: "$emissions" }
  }
}

4. 整理结构并生成年份键的对象
   去除冗余字段，将年份转为字符串作为键，最终生成目标结构：

{
  $project: {
    _id: 0,
    companyId: "$_id.companyId",
    yearEntry: {
      k: { $toString: "$_id.year" },
      v: {
        scope1: "$yearEmissions.scope1",
        scope2: "$yearEmissions.scope2",
        scope3: "$yearEmissions.scope3"
      }
    }
  }
},
{
  $group: {
    _id: "$companyId",
    emissionsByYear: { $push: "$yearEntry" }
  }
},
{
  $project: {
    _id: 0,
    companyId: "$_id",
    emissionsByYear: { $arrayToObject: "$emissionsByYear" }
  }
}

最终输出结果

{
  "companyId": "comp_001",
  "emissionsByYear": {
    "2020": { "scope1": 150, "scope2": 230, "scope3": 410 },
    "2021": { "scope1": 165, "scope2": 245, "scope3": 430 }
  }
}

内容的提问来源于stack exchange，提问作者Luca Riccitelli