如何通过MongoDB聚合查询关联集合并统计工时总和？

现有两个MongoDB Schema：CostCode 和 Timesheet。每个Timesheet会关联一个或多个成本代码，其costCodes数组中的每个元素包含CostCode的ID引用以及hoursWorked字段。需要查询所有CostCode文档，每个文档要附带关联的所有Timesheet记录中hoursWorked的总和。

CostCode Schema #

{
  label: { type: String, required: false },
  description: { type: String },
  budgetedHours: { type: Number, required: false },
}

Timesheet Schema #

{
  costCodes: [{
    costCode: { type: Schema.ObjectId, ref: 'CostCode' },
    percentage: { type: Number },
    hoursWorked: { type: Number },
  }],
  startDateTimeUTC: { type: Date, required: true },
  endDateTimeUTC: { type: Date },
}

期望输出 #

[
  {
    _id: "6447dedc26faeedd35b62c0c",
    label: "Cost Code 1",
    description: "",
    budgetedHours: 100,
    totalHoursWorked: 53
  },
  {
    _id: "6445e11b27d9bbb7779bb89b",
    label: "Cost Code 2",
    description: "",
    budgetedHours: 150,
    totalHoursWorked: 42
  }
]

解决方案 #

可以通过MongoDB的聚合框架实现，核心思路是先从Timesheet中提取并分组计算每个CostCode的工时总和，再与CostCode集合关联合并数据：

CostCode.aggregate([
  // 关联Timesheet集合，筛选当前CostCode对应的所有工时记录
  {
    $lookup: {
      from: 'timesheets',
      let: { costCodeId: '$_id' },
      pipeline: [
        { $unwind: '$costCodes' },
        { $match: { $expr: { $eq: ['$costCodes.costCode', '$$costCodeId'] } } },
        { $project: { hoursWorked: '$costCodes.hoursWorked' } }
      ],
      as: 'timesheetHours'
    }
  },
  // 计算工时总和，无工时记录时默认返回0
  {
    $addFields: {
      totalHoursWorked: {
        $sum: '$timesheetHours.hoursWorked'
      }
    }
  },
  // 移除临时字段，精简输出结构（可选）
  { $project: { timesheetHours: 0 } }
])

关键步骤说明 #

1. $lookup阶段：使用带自定义管道的关联查询，先通过$unwind展开Timesheet的costCodes数组，再匹配当前CostCode的ID，最终只保留关联的工时数据。
2. $addFields阶段：通过$sum对提取到的工时数组求和，空数组会自动返回0，避免出现null值。
3. $project阶段：可选操作，移除临时生成的timesheetHours数组，让输出结构更符合期望。

扩展：按时间范围筛选Timesheet #

如果需要只统计某个时间区间内的工时，可以在$lookup的管道中添加日期匹配条件：

{ $match: {
  $expr: { $eq: ['$costCodes.costCode', '$$costCodeId'] },
  startDateTimeUTC: { $gte: new Date('2024-01-01') },
  endDateTimeUTC: { $lte: new Date('2024-12-31') }
} }

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