Convert SQL LEFT JOIN to MongoDB $lookup with Preserved Nulls

LEFT JOIN to $lookup with preserveNullAndEmptyArrays

SQL's LEFT JOIN returns all rows from the left table, plus matching rows from the right table (or NULL if no match). MongoDB achieves this by combining $lookup with $unwind and setting preserveNullAndEmptyArrays: true.

Example SQL

SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id

Generated MongoDB Query

db.users.aggregate([
  {
    $lookup: {
      from: "orders",
      localField: "id",
      foreignField: "user_id",
      as: "orders"
    }
  },
  {
    $unwind: {
      path: "$orders",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $project: {
      name: 1,
      "orders.total": 1
    }
  }
])

preserveNullAndEmptyArrays

This is the critical difference from INNER JOIN. Setting this option to true tells $unwind to:

• Keep documents where the as array is empty (no matches found)
• Keep documents where the as field is null or missing

Without this option, documents with no matches would be dropped, making the query behave like an INNER JOIN instead.

INNER JOIN vs LEFT JOIN Comparison

Join Type	$unwind Configuration	Unmatched Documents
INNER JOIN	{ $unwind: "$field" }	Dropped
LEFT JOIN	{ $unwind: { path: "$field", preserveNullAndEmptyArrays: true } }	Preserved

Accessing Joined Fields

After a LEFT JOIN conversion, unmatched documents will have the joined fields as null. In application code, check for null values before accessing nested properties:

// In application code
const total = doc.orders ? doc.orders.total : null;

When to Denormalize Instead

If you find yourself frequently performing LEFT JOINs on the same collections, it may be more efficient to embed the related data directly in the parent document. MongoDB's document model is designed for this kind of denormalization, trading storage space for query performance.