MongoDB中不同类型对象数组的分组求和问题

你当前的做法会出问题，核心原因是：$unwind拆分sections数组后，每个拆分出的文档只包含department、location、salary三者中的一个。分组时MongoDB会把带department和location的文档归为一组（但这组没有薪资数据，求和结果为0），把仅带salary的文档单独归为另一组，最终得到两个不符合预期的结果。

正确的思路是先将同一份文档里的department、location、salary整合到同一层级，再进行分组汇总。以下提供两种实现方式：

方式一：用$filter提取目标字段 #

db.collection.aggregate([
  // 第一步：从sections数组中分别筛选出department、location、salary对应的section
  {
    $addFields: {
      deptSection: {
        $arrayElemAt: [
          { $filter: {
              input: "$sections",
              cond: { $exists: ["$$this.categoryData.department", true] }
            }
          },
          0
        ]
      },
      locSection: {
        $arrayElemAt: [
          { $filter: {
              input: "$sections",
              cond: { $exists: ["$$this.categoryData.location", true] }
            }
          },
          0
        ]
      },
      salarySection: {
        $arrayElemAt: [
          { $filter: {
              input: "$sections",
              cond: { $exists: ["$$this.categoryData.salary", true] }
            }
          },
          0
        ]
      }
    }
  },
  // 第二步：提取嵌套字段，简化后续分组操作
  {
    $addFields: {
      department: "$deptSection.categoryData.department",
      location: "$locSection.categoryData.location",
      salaryAmount: "$salarySection.categoryData.salary.amount"
    }
  },
  // 第三步：按department和location分组，汇总薪资
  {
    $group: {
      _id: {
        deptId: "$department.id",
        deptName: "$department.name",
        locId: "$location.id",
        locName: "$location.name"
      },
      totalSalary: { $sum: "$salaryAmount" }
    }
  },
  // 第四步：整理输出格式
  {
    $project: {
      _id: 0,
      department: { id: "$_id.deptId", name: "$_id.deptName" },
      location: { id: "$_id.locId", name: "$_id.locName" },
      totalSalary: 1
    }
  }
])

方式二：用$reduce合并字段（更简洁） #

db.collection.aggregate([
  // 第一步：遍历sections数组，合并所有categoryData到同一对象
  {
    $addFields: {
      mergedData: {
        $reduce: {
          input: "$sections",
          initialValue: {},
          in: { $mergeObjects: ["$$value", "$$this.categoryData"] }
        }
      }
    }
  },
  // 第二步：按department和location分组，汇总薪资
  {
    $group: {
      _id: {
        deptId: "$mergedData.department.id",
        deptName: "$mergedData.department.name",
        locId: "$mergedData.location.id",
        locName: "$mergedData.location.name"
      },
      totalSalary: { $sum: "$mergedData.salary.amount" }
    }
  },
  // 第三步：整理输出格式
  {
    $project: {
      _id: 0,
      department: { id: "$_id.deptId", name: "$_id.deptName" },
      location: { id: "$_id.locId", name: "$_id.locName" },
      totalSalary: 1
    }
  }
])

两种方式最终都会得到如下结果：

{
  "department": { "id": 1, "name": "english" },
  "location": { "id": 1, "name": "US" },
  "totalSalary": 240000
}

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