如何通过MongoDB聚合将含ID的对象数组替换为对应数据

现有两个MongoDB集合collectionA与collectionB，需将collectionA中每个文档的products数组内的productOwners字段（ObjectId数组）替换为collectionB中对应的完整用户数据。

示例数据 #

db={
  collectionA: [
    {
      "id": ObjectId("63b7c24c06ebe7a8fd11777b"),
      "uniqueRefId": "UUID-2023-0001",
      "products": [
        {
          "productIndex": 1,
          "productCategory": ObjectId("63b7c24c06ebe7a8fd11777b"),
          "productOwners": [
            ObjectId("63b7c2fd06ebe7a8fd117781")
          ]
        },
        {
          "productIndex": 2,
          "productCategory": ObjectId("63b7c24c06ebe7a8fd11777b"),
          "productOwners": [
            ObjectId("63b7c2fd06ebe7a8fd117781"),
            ObjectId("63b7c12706ebe7a8fd117778")
          ]
        },
        {
          "productIndex": 3,
          "productCategory": "",
          "productOwners": ""
        }
      ]
    }
  ],
  collectionB: [
    {
      "_id": ObjectId("63b7c2fd06ebe7a8fd117781"),
      "fullname": "Jim Corbett",
      "email": "jim.corbett@pp.com"
    },
    {
      "_id": ObjectId("63b7c12706ebe7a8fd117778"),
      "fullname": "Carry Minatti",
      "email": "carry.minatty@pp.com"
    }
  ]
}

期望输出 #

[
    {
        "id": ObjectId("507f1f77bcf86cd799439011"),
        "uniqueRefId": "UUID-2023-0001",
        "products": [
            {
                "productIndex": 1,
                "productCategory": ObjectId('614g2f77bff86cd755439021'),
                "productOwners": [
                    {
                        "_id": ObjectId("63ac1e59c0afb8b6f2d41acd"),
                        "fullname": "Jim Corbett",
                        "email": "jim.corbett@pp.com"
                    }
                ]
            },
            {
                "productIndex": 2,
                "productCategory": ObjectId('614g2f77bff86cd755439021'),
                "productOwners": [
                    {
                        "_id": ObjectId("63ac1e59c0afb8b6f2d41acd"),
                        "fullname": "Jim Corbett",
                        "email": "jim.corbett@pp.com"
                    },
                    {
                        "_id": ObjectId("63ac1e59c0afb8b6f2d41ace"),
                        "fullname": "Carry Minatti",
                        "email": "carry.minatty@pp.com"
                    }
                ]
            },
            {
                "productIndex": 3,
                "productCategory": "",
                "productOwners": ""
            }
        ]
    }
]

已尝试的查询（未达预期） #

db.collectionA.aggregate([
  {
    "$lookup": {
      "from": "collectionB",
      "localField": "products.productOwners",
      "foreignField": "_id",
      "as": "inventory_docs"
    }
  }
])

正确的聚合查询方案 #

你之前的查询无法满足需求，因为直接对嵌套数组的字段做$lookup会把所有匹配的用户文档放到一个顶层数组里，无法对应到products数组的每个元素中。需要通过拆分、关联、重组的步骤实现：

db.collectionA.aggregate([
  // 拆分products数组为单个文档
  { "$unwind": "$products" },
  // 关联collectionB获取用户数据，处理productOwners为空或非数组的情况
  {
    "$lookup": {
      "from": "collectionB",
      "let": { "owners": "$products.productOwners" },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$cond": [
                { "$and": [
                  { "$ne": ["$$owners", ""] },
                  { "$isArray": "$$owners" }
                ] },
                { "$in": ["$_id", "$$owners"] },
                false
              ]
            }
          }
        }
      ],
      "as": "matchedOwners"
    }
  },
  // 替换productOwners字段：如果原字段是有效数组则用匹配到的用户，否则保留原内容
  {
    "$addFields": {
      "products.productOwners": {
        "$cond": [
          { "$and": [
            { "$ne": ["$products.productOwners", ""] },
            { "$isArray": "$products.productOwners" }
          ] },
          "$matchedOwners",
          "$products.productOwners"
        ]
      }
    }
  },
  // 移除临时字段
  { "$unset": "matchedOwners" },
  // 重组products数组
  {
    "$group": {
      "_id": "$_id",
      "id": { "$first": "$id" },
      "uniqueRefId": { "$first": "$uniqueRefId" },
      "products": { "$push": "$products" }
    }
  },
  // 移除默认的_id字段（可选，根据需求调整）
  { "$unset": "_id" }
])

步骤说明 #

1. $unwind：将products数组拆分为单个文档，方便逐个处理每个产品的productOwners。
2. 带管道的$lookup：使用let传入当前产品的productOwners，在子管道中判断是否为有效数组，只匹配存在于该数组中的用户文档，避免无效关联。
3. $addFields：替换productOwners字段，对有效数组使用匹配到的用户数据，非有效情况（空字符串、非数组）保留原内容。
4. $unset：删除临时的matchedOwners字段，清理数据。
5. $group：按原文档的_id分组，将拆分后的产品文档重新组合成products数组，同时保留其他字段。
6. 可选的$unset：移除聚合生成的默认_id字段，和原数据结构保持一致。

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