MongoDB嵌套数组元素$lookup：关联orders与products扩展items字段

Hey there! To achieve your goal of adding product names to each item in the orders collection's items array by joining with the products collection, you can use MongoDB's aggregation framework. Below are two straightforward methods to get the job done:

Approach 1: Using $unwind + $lookup + $group #

This method is easy to follow and great for beginners:

1. Split the items array: Use $unwind to break each order into separate documents for every item in its items array.
2. Fetch product details: Use $lookup to pull in the matching product from the products collection using product_id.
3. Merge product data into items: Extract the product name from the lookup result and attach it directly to the item object.
4. Reassemble the items array: Use $group to combine the individual items back into an array for each original order.

Aggregation Query

db.orders.aggregate([
  // Step 1: Split each order into individual item documents
  { $unwind: "$items" },
  
  // Step 2: Join with products to get the product name
  {
    $lookup: {
      from: "products",
      localField: "items.product_id",
      foreignField: "_id",
      as: "productInfo"
    }
  },
  
  // Step 3: Add the product name to the item and clean up temporary data
  {
    $addFields: {
      "items.name": { $arrayElemAt: ["$productInfo.name", 0] },
      productInfo: "$$REMOVE"
    }
  },
  
  // Step 4: Put the items array back together for each order
  {
    $group: {
      _id: "$_id",
      items: { $push: "$items" }
    }
  }
])

Approach 2: Using $lookup with a Pipeline (No Unwind) #

If you prefer to avoid splitting the array, you can use a pipeline inside $lookup to match and merge product data directly:

db.orders.aggregate([
  {
    $lookup: {
      from: "products",
      let: { orderItems: "$items" },
      pipeline: [
        {
          $match: {
            $expr: { $in: ["$_id", "$$orderItems.product_id"] }
          }
        },
        { $project: { _id: 1, name: 1 } }
      ],
      as: "productDetails"
    }
  },
  {
    $addFields: {
      items: {
        $map: {
          input: "$items",
          as: "item",
          in: {
            $mergeObjects: [
              "$$item",
              {
                $arrayElemAt: [
                  "$productDetails",
                  { $indexOfArray: ["$productDetails._id", "$$item.product_id"] }
                ]
              }
            ]
          }
        }
      },
      productDetails: "$$REMOVE"
    }
  }
])

Expected Output #

Both queries will produce your desired result:

[
  {
    "_id": 1,
    "items": [
      { "product_id": 1, "price": 1.99, "qty": 2, "name": "Product 1" },
      { "product_id": 2, "price": 3.99, "qty": 5, "name": "Product 2" }
    ]
  }
]

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