MongoDB条件聚合：originId匹配替换orders嵌套collaborators数据

现有文档结构 #

集合内单条文档结构如下：

{
  "_id" : "af5c00e4-d3a8-419d-8793-c0cf328802ec",
  "collaborators" : [
    {
      "_id" : "9bd2eee8-bf6c-4c6f-bab7-d2d175aed807",
      "origin" : [{"originId" : "123"}],
      "firstName" : "Parveen",
      "lastName" : "Vendor",
      "email" : "pk@gmail.com"
    },
    {
      "_id" : "234324-bf6c-4c6f-bab7-d2d175aed807",
      "origin" : [{"originId" : "1234"}],
      "firstName" : "Parveen123",
      "lastName" : "34",
      "email" : "abc@gmail.com"
    }
  ],
  "orders" : [
    {
      "totalAmount" : 10,
      "collaborators" : [
        {
          "origin" : [{"originId" : "123"}],
          "type" : "Supplier"
        },
        {
          "origin" : [{"originId" : "1233"}],
          "type" : "Supplier"
        }
      ]
    }
  ]
}

处理规则 #

• 遍历orders数组下所有订单的嵌套协作者列表，提取每个协作者条目的origin.originId
• 将提取到的originId和文档顶层collaborators数组中各协作者的originId做匹配
• 匹配成功的条目，直接替换为顶层对应协作者的完整数据
• 未匹配到的条目，保留原有全部字段不做修改

处理后的预期输出结构：

{
  "_id" : "af5c00e4-d3a8-419d-8793-c0cf328802ec",
  "collaborators" : [
    {
      "_id" : "9bd2eee8-bf6c-4c6f-bab7-d2d175aed807",
      "origin" : [{"originId" : "123"}],
      "firstName" : "Parveen",
      "lastName" : "Vendor",
      "email" : "pk@gmail.com"
    },
    {
      "_id" : "234324-bf6c-4c6f-bab7-d2d175aed807",
      "origin" : [{"originId" : "1234"}],
      "firstName" : "Parveen123",
      "lastName" : "34",
      "email" : "abc@gmail.com"
    }
  ],
  "orders" : [
    {
      "totalAmount" : 10,
      "collaborators" : [
        {
          "_id" : "9bd2eee8-bf6c-4c6f-bab7-d2d175aed807",
          "origin" : [{"originId" : "123"}],
          "firstName" : "Parveen",
          "lastName" : "Vendor",
          "email" : "pk@gmail.com"
        },
        {
          "origin" : [{"originId" : "1233"}],
          "type" : "Supplier"
        }
      ]
    }
  ]
}

聚合实现代码 #

查询阶段直接使用如下聚合管道即可完成替换，如需持久化修改结果，可以在管道末尾追加$out或$merge阶段写入集合：

db.替换为你的实际集合名.aggregate([
  {
    $set: {
      orders: {
        $map: {
          input: "$orders",
          as: "order",
          in: {
            $mergeObjects: [
              "$$order",
              {
                collaborators: {
                  $map: {
                    input: "$$order.collaborators",
                    as: "orderCol",
                    in: {
                      $let: {
                        vars: {
                          matchedCol: {
                            $first: {
                              $filter: {
                                input: "$collaborators",
                                as: "topCol",
                                cond: {
                                  $eq: [
                                    { $arrayElemAt: ["$$orderCol.origin.originId", 0] },
                                    { $arrayElemAt: ["$$topCol.origin.originId", 0] }
                                  ]
                                }
                              }
                            }
                          }
                        },
                        in: { $ifNull: ["$$matchedCol", "$$orderCol"] }
                      }
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

上述代码适配给出的样例结构：默认每个协作者的origin数组仅包含1个元素，取第一个元素的originId做匹配。如果origin数组存在多个originId需要调整匹配逻辑，修改cond内的判断条件即可。

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