MongoDB 4.4如何根据type字段实现条件式lookup关联查询

需求说明 #

现有集合中包含destinations数组字段，数组元素结构如下：

[{
   type: 1,
   sold_to_id: 'xxxxx'
 },
 {
  type: 2,
  sold_to_id: 'yyyy',
 }]

需要实现：当type=1时关联customers集合，type≠1时关联users集合，最终将关联到的name字段赋值给destinations元素的sold_to_ref。

输入数据示例 #

db={
  "collection": [
    { 
      "contact": ['adf', 'dsf', 'sdd'],
      "destinations": [
        { type: 1, sold_to_id: "xxxxx" },
        { type: 1, sold_to_id: "yyyy" },
        { type: 2, sold_to_id: "zzz" },
        { type: 2, sold_to_id: "www" }
      ]
    }
  ],
  "customers": [
    { _id: "xxxxx", name: "Customer1" },
    { _id: "yyyy", name: "Customer2" }
  ],
  "users": [
    { _id: "zzz", name: "User1" },
    { _id: "www", name: "User2" }
  ]
}

期望输出 #

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "contact": ['adf', 'dsf', 'sdd'],
    "destinations": [
      { type: 1, sold_to_id: "xxxxx", sold_to_ref: "Customer1" },
      { type: 1, sold_to_id: "yyyy", sold_to_ref: "Customer2" },
      { type: 2, sold_to_id: "zzz", sold_to_ref: "User1" },
      { type: 2, sold_to_id: "www", sold_to_ref: "User2" }
    ]
  }
]

正确聚合管道实现 #

db.collection.aggregate([
  // 展开destinations数组，逐个处理每个元素
  { $unwind: "$destinations" },
  // 关联customers集合，仅匹配type=1的情况
  {
    $lookup: {
      from: "customers",
      let: {
        type: "$destinations.type",
        sid: "$destinations.sold_to_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$$type", 1] },
                { $eq: ["$_id", "$$sid"] }
              ]
            }
          }
        }
      ],
      as: "customer_ref"
    }
  },
  // 关联users集合，仅匹配type≠1的情况
  {
    $lookup: {
      from: "users",
      let: {
        type: "$destinations.type",
        sid: "$destinations.sold_to_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $ne: ["$$type", 1] },
                { $eq: ["$_id", "$$sid"] }
              ]
            }
          }
        }
      ],
      as: "user_ref"
    }
  },
  // 合并两个关联结果，提取name作为sold_to_ref
  {
    $addFields: {
      "destinations.sold_to_ref": {
        $cond: {
          if: { $eq: ["$destinations.type", 1] },
          then: { $arrayElemAt: ["$customer_ref.name", 0] },
          else: { $arrayElemAt: ["$user_ref.name", 0] }
        }
      }
    }
  },
  // 移除临时关联字段
  { $project: { customer_ref: 0, user_ref: 0 } },
  // 重新聚合destinations数组
  {
    $group: {
      _id: "$_id",
      contact: { $first: "$contact" },
      destinations: { $push: "$destinations" }
    }
  }
])

方案说明 #

1. $unwind：将destinations数组拆分为单个文档，便于对每个元素单独执行关联操作。
2. 两次$lookup：分别针对customers和users集合，通过$expr结合变量实现条件匹配，确保只有符合type条件的文档才会被关联。
3. $addFields：使用$cond判断type值，从对应的关联结果数组中提取name字段，赋值给destinations.sold_to_ref。
4. $group：将拆分后的文档重新聚合为原始结构，恢复destinations数组。

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