MongoDB数组$lookup聚合：多字段匹配求和实现问题

原写法的问题在于，$lookup的子管道无法直接遍历外部的数组元素进行批量匹配。要实现需求，需要先将查询条件数组拆分为单个文档，再逐个关联查询，最后合并结果并计算金额总和。

完整聚合管道代码如下：

db.orders.aggregate([
  { $match: { shopId: "s1" } },
  {
    $addFields: {
      arraysElemsForlookup: [
        { consumerId: "c1", dispatchId: "d1", orderId: 1 },
        { consumerId: "c5", dispatchId: "d6", orderId: 5 },
        { consumerId: "c2", dispatchId: "d2", orderId: 2 }
      ]
    }
  },
  // 拆分查询条件数组，每个元素生成单独文档
  { $unwind: "$arraysElemsForlookup" },
  // 针对每个查询条件关联amounts集合
  {
    $lookup: {
      from: "amounts",
      let: {
        consumerId: "$arraysElemsForlookup.consumerId",
        dispatchId: "$arraysElemsForlookup.dispatchId",
        orderId: "$arraysElemsForlookup.orderId"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$consumerId", "$$consumerId"] },
                { $eq: ["$dispatchId", "$$dispatchId"] },
                { $eq: ["$orderId", "$$orderId"] }
              ]
            }
          }
        }
      ],
      as: "matchedAmounts"
    }
  },
  // 合并拆分的文档，计算总金额并还原原数组结构
  {
    $group: {
      _id: "$_id",
      shopId: { $first: "$shopId" },
      consumerId: { $first: "$consumerId" },
      dispatches: { $first: "$dispatches" },
      arraysElemsForlookup: { $push: "$arraysElemsForlookup" },
      totalAmount: {
        $sum: {
          $sum: "$matchedAmounts.amount" // 先取每个匹配结果的金额和，再累加所有分组
        }
      },
      lookupData: { $push: "$matchedAmounts" } // 保留所有匹配详情（可选）
    }
  }
])

关键说明： #

• $unwind：将arraysElemsForlookup数组拆分为单个文档，确保每个查询条件都能单独执行关联。
• $lookup：子管道中使用$$引用外部绑定的单个查询条件，精准匹配amounts的三个字段。
• $group：按原订单_id合并文档，用$sum嵌套计算所有匹配到的金额总和，同时用$push还原arraysElemsForlookup的数组结构。

输出结果示例： #

针对提供的测试数据，最终会得到：

{
  "_id": 1,
  "shopId": "s1",
  "consumerId": "c1",
  "dispatches": [{"_id": "d1", "status": "assigned", "toBeSentOn": ISODate("2024-05-15T10:00:00Z")}],
  "arraysElemsForlookup": [
    {"consumerId": "c1", "dispatchId": "d1", "orderId": 1},
    {"consumerId": "c5", "dispatchId": "d6", "orderId": 5},
    {"consumerId": "c2", "dispatchId": "d2", "orderId": 2}
  ],
  "totalAmount": 100,
  "lookupData": [[{"_id": "a1", "shopId": "s1", "orderId": 1, "consumerId": "c1", "dispatchId": "d1", "amount": 100}], [], []]
}

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