MongoDB聚合查询：将集合A数组字段关联集合B替换owner_id为邮箱

Hey there! Let's tackle this problem together. The issue you're facing is because the owner_id you want to map is nested inside an array (versions), so a basic $lookup won't automatically replace each element's owner_id with the corresponding email from collection B. Here are two solid approaches to get your desired output:

Approach 1: Unwind → Lookup → Group (Beginner-Friendly) #

This method breaks down the problem into simple, easy-to-follow steps by first flattening the array, doing the lookup, then reconstructing the original structure:

db.collectionA.aggregate([
  // Step 1: Flatten the versions array into individual documents
  { $unwind: "$versions" },
  
  // Step 2: Join with collectionB to fetch the email matching each versions.owner_id
  {
    $lookup: {
      from: "collectionB",
      localField: "versions.owner_id",
      foreignField: "_id",
      as: "ownerDetails"
    }
  },
  
  // Step 3: Replace the ObjectId owner_id with the matched email
  {
    $addFields: {
      "versions.owner_id": { $arrayElemAt: ["$ownerDetails.email", 0] }
    }
  },
  
  // Step 4: Clean up the temporary ownerDetails field
  { $project: { ownerDetails: 0 } },
  
  // Step 5: Re-group documents back into the original array structure
  {
    $group: {
      _id: "$_id",
      versions: { $push: "$versions" }
    }
  }
])

How this works:

• $unwind splits the versions array into separate documents, so we can run the lookup on each individual owner_id.
• $lookup matches each unwound owner_id with the _id in collectionB, storing the matching email in a temporary ownerDetails array.
• $addFields replaces the original ObjectId owner_id with the email (we use $arrayElemAt because $lookup always returns an array, even for single matches).
• $project removes the temporary field we don't need anymore.
• $group reassembles the documents by their original _id, pushing the updated versions elements back into an array.

Approach 2: Lookup with Sub-Pipeline (More Efficient) #

If you're working with large datasets, this method avoids unwinding the array (which can be resource-heavy) by using a sub-pipeline directly in $lookup:

db.collectionA.aggregate([
  // Step 1: Fetch all matching emails from collectionB for the owner_ids in versions
  {
    $lookup: {
      from: "collectionB",
      let: { versionList: "$versions" },
      pipeline: [
        {
          $match: {
            $expr: { $in: ["$_id", "$$versionList.owner_id"] }
          }
        },
        {
          $project: {
            _id: 0,
            originalOwnerId: "$_id",
            email: "$email"
          }
        }
      ],
      as: "ownerEmailMap"
    }
  },
  
  // Step 2: Update each versions element to replace owner_id with the matching email
  {
    $addFields: {
      versions: {
        $map: {
          input: "$versions",
          as: "v",
          in: {
            $mergeObjects: [
              "$$v",
              {
                owner_id: {
                  $arrayElemAt: [
                    "$ownerEmailMap.email",
                    {
                      $indexOfArray: ["$ownerEmailMap.originalOwnerId", "$$v.owner_id"]
                    }
                  ]
                }
              }
            ]
          }
        }
      }
    }
  },
  
  // Step 3: Clean up the temporary ownerEmailMap field
  { $project: { ownerEmailMap: 0 } }
])

How this works:

• $lookup uses a sub-pipeline to fetch all relevant emails from collectionB, storing a map of original ObjectIds to emails in ownerEmailMap.
• $map iterates over each element in the original versions array. For each element, $mergeObjects keeps all existing fields while replacing owner_id with the matching email (found using $indexOfArray to locate the original ObjectId in the map, then $arrayElemAt to pull the corresponding email).
• Finally, we remove the temporary map field to clean up the output.

Notes: #

• Ensure you're using MongoDB 3.6 or later for the sub-pipeline approach (since let and pipeline support in $lookup was introduced in this version).
• Both methods will produce exactly the output you're looking for—pick the one that aligns better with your familiarity level and dataset size.

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