PyMongo中基于MD5对两个集合做全外连接并合并属性字段

Got it, let's tackle this problem: you want to do a full outer join between your type1 and type2 collections based on the md5 field, then merge the type1 and type2 properties into a new combined field. Since MongoDB doesn't have a native full outer join like SQL, we'll use the aggregation framework to simulate it. Here are two reliable approaches:

Approach 1: Using $unionWith (Clean & Efficient) #

This method combines processed data from both collections first, then groups by md5 to merge the properties. Perfect if you want a single pipeline without extra steps.

Full Aggregation Pipeline #

db.type1.aggregate([
  // Unwind nested arrays to access properties directly
  { $unwind: "$result" },
  { $unwind: "$result.raw.rects" },
  // Reshape type1 data: extract type1 values, add empty type2 array
  {
    $project: {
      md5: 1,
      type1Vals: "$result.raw.rects.properties.type1",
      type2Vals: []
    }
  },
  // Combine with processed type2 data
  {
    $unionWith: {
      coll: "type2",
      pipeline: [
        { $unwind: "$result" },
        { $unwind: "$result.raw.rects" },
        {
          $project: {
            md5: 1,
            type1Vals: [],
            type2Vals: "$result.raw.rects.properties.type2"
          }
        }
      ]
    }
  },
  // Group by md5 to merge all matching values from both collections
  {
    $group: {
      _id: "$md5",
      allType1: { $addToSet: "$type1Vals" },
      allType2: { $addToSet: "$type2Vals" }
    }
  },
  // Flatten nested arrays and build the final properties field
  {
    $project: {
      md5: "$_id",
      _id: 0,
      properties: {
        type1: { 
          $reduce: { 
            input: "$allType1", 
            initialValue: [], 
            in: { $concatArrays: ["$$value", "$$this"] } 
          } 
        },
        type2: { 
          $reduce: { 
            input: "$allType2", 
            initialValue: [], 
            in: { $concatArrays: ["$$value", "$$this"] } 
          } 
        }
      }
    }
  }
])

What's Happening Here? #

• $unwind: Breaks down the nested result and raw.rects arrays so we can get to the properties fields easily.
• $project: Shapes each collection's data to have consistent fields—empty arrays for the type that's missing in each collection.
• $unionWith: Merges the processed data from both collections, which gives us all records from type1 and type2 (the full outer join base).
• $group: Groups all documents by md5, collecting every type1 and type2 value associated with that hash.
• $reduce + $concatArrays: Flattens the nested arrays we get from grouping into a single clean array for each type in the final properties field.

Approach 2: Using $lookup (For More Control) #

If you prefer working with left joins and want explicit control over handling records that only exist in one collection, this method uses two $lookup pipelines and combines the results.

Step-by-Step Implementation #

1. Left Join type1 with type2: Get all records from type1, plus matching type2 data (if any).
2. Reverse Left Join: Get records from type2 that don't have a matching md5 in type1.
3. Combine & Group: Merge the two result sets and group by md5 to clean up duplicates.

// 1. Left join type1 with type2
const leftJoinResults = db.type1.aggregate([
  { $unwind: "$result" },
  { $unwind: "$result.raw.rects" },
  {
    $lookup: {
      from: "type2",
      localField: "md5",
      foreignField: "md5",
      as: "type2Matches"
    }
  },
  // Preserve records where there's no match in type2
  { $unwind: { path: "$type2Matches", preserveNullAndEmptyArrays: true } },
  { $unwind: { path: "$type2Matches.result", preserveNullAndEmptyArrays: true } },
  { $unwind: { path: "$type2Matches.result.raw.rects", preserveNullAndEmptyArrays: true } },
  {
    $project: {
      md5: 1,
      type1: "$result.raw.rects.properties.type1",
      type2: { $ifNull: ["$type2Matches.result.raw.rects.properties.type2", []] }
    }
  }
]).toArray();

// 2. Get type2 records with no matching md5 in type1
const type2OnlyResults = db.type2.aggregate([
  { $unwind: "$result" },
  { $unwind: "$result.raw.rects" },
  {
    $lookup: {
      from: "type1",
      localField: "md5",
      foreignField: "md5",
      as: "type1Matches"
    }
  },
  // Filter out records that have a match in type1
  { $match: { type1Matches: { $size: 0 } } },
  {
    $project: {
      md5: 1,
      type1: [],
      type2: "$result.raw.rects.properties.type2"
    }
  }
]).toArray();

// 3. Combine results and group to merge duplicates
const combined = leftJoinResults.concat(type2OnlyResults);

// Use a temp collection to run the final group aggregation
db.tempMerge.insertMany(combined);
const finalResult = db.tempMerge.aggregate([
  {
    $group: {
      _id: "$md5",
      mergedType1: { $addToSet: "$type1" },
      mergedType2: { $addToSet: "$type2" }
    }
  },
  {
    $project: {
      md5: "$_id",
      _id: 0,
      properties: {
        type1: { 
          $reduce: { 
            input: "$mergedType1", 
            initialValue: [], 
            in: { $concatArrays: ["$$value", "$$this"] } 
          } 
        },
        type2: { 
          $reduce: { 
            input: "$mergedType2", 
            initialValue: [], 
            in: { $concatArrays: ["$$value", "$$this"] } 
          } 
        }
      }
    }
  }
]).toArray();

// Clean up the temp collection
db.tempMerge.drop();

Both approaches will give you the full outer join result with merged type1 and type2 properties. Pick the one that fits your workflow best!

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