如何在MongoDB中实现带AND条件的SQL LEFT JOIN查询？

Hey there! Let's walk through converting your SQL query to MongoDB and break down how conditional joins work in MongoDB, since it doesn't use traditional SQL-style JOINs.

Converted MongoDB Query #

First, here's the equivalent MongoDB aggregation pipeline for your SQL statement. I'll assume your SQL tables map directly to MongoDB collections named apple and peach:

db.apple.aggregate([
  // Filter red apples first (matches SQL WHERE clause)
  {
    $match: {
      color: "red"
    }
  },
  // Left join with peach collection, applying AND conditions during the join
  {
    $lookup: {
      from: "peach",
      let: { appleId: "$id" }, // Pass current apple's id as a local variable
      pipeline: [
        // Match peach docs where id matches apple's id AND name is "sweet"
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$id", "$$appleId"] },
                { $eq: ["$name", "sweet"] }
              ]
            }
          }
        },
        // Keep only the name field (matches SQL's peach.name selection)
        {
          $project: {
            _id: 0,
            name: 1
          }
        }
      ],
      as: "peach_matches" // Store join results in this array field
    }
  },
  // Format output to match SQL's SELECT: apple.id + peach.name (or null if no match)
  {
    $project: {
      _id: 0,
      id: 1,
      peach_name: { $arrayElemAt: ["$peach_matches.name", 0] }
    }
  }
])

How AND-Conditioned Joins Work in MongoDB #

MongoDB doesn't have native SQL JOINs—instead, we use the $lookup aggregation operator to perform left outer joins between collections. When you need to add AND conditions to the join (like matching both id and name in your case), here are the key approaches:

1. Pipeline-Based $lookup (Recommended) #

This is the method used in the example above, and it's the most efficient and flexible option:

• Use the let parameter to define local variables that reference fields from the source collection (apple in this case). Variables are accessed with the $$ prefix.
• Inside the pipeline array, use $match with $expr to write your conditional logic. The $and operator lets you combine multiple match criteria directly in the join stage—this way, we only pull in peach documents that meet both conditions, instead of fetching all matching id docs first and filtering later.
• You can extend this with other operators (like $or, $gte) for even more complex join conditions.

2. Basic $lookup + Post-Filtering (Less Efficient) #

If you're working with simpler cases, you could first do a basic $lookup on just the id field, then filter the results afterward. However, this pulls in all peach docs that match the id before filtering out non-"sweet" ones, which wastes resources on unnecessary data:

db.apple.aggregate([
  { $match: { color: "red" } },
  {
    $lookup: {
      from: "peach",
      localField: "id",
      foreignField: "id",
      as: "peach_docs"
    }
  },
  {
    $project: {
      id: 1,
      peach_name: {
        $arrayElemAt: [
          {
            $filter: {
              input: "$peach_docs",
              cond: { $eq: ["$$this.name", "sweet"] }
            }
          }.name,
          0
        ]
      },
      _id: 0
    }
  }
])

Mapping SQL Logic to MongoDB #

Let's tie this back to your original SQL to make the correspondence clear:

• WHERE apple.color = "red" → The $match stage at the start filters the apple collection first, reducing the number of documents we need to process in subsequent stages.
• LEFT JOIN peach ON apple.id = peach.id AND peach.name = "sweet" → The pipeline-based $lookup handles this by applying both conditions during the join, just like your SQL. Since it's a left join, apple docs with no matching peach docs will have an empty peach_matches array, which we convert to null in the final stage (matching SQL's NULL for no matches).
• SELECT apple.id, peach.name → The final $project stage selects the fields we want, using $arrayElemAt to pull the first (and only) matching peach name from the array, or return null if there are no matches.

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