$lookup Deep Dive — MongoDB

$lookup

$lookup performs a left outer join to another collection in the same database, pulling in related documents as an array field. It's how MongoDB handles the "join" queries you'd write as a SQL JOIN — with a different syntax and different performance characteristics.

Basic Equality Join

Sample collections

// orders
{ _id: 1, customerId: 101, total: 42.50 }

// customers
{ _id: 101, name: "Alice Chen", email: "alice@example.com" }

Joining orders to customers

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",       // collection to join
      localField: "customerId",  // field on THIS (orders) document
      foreignField: "_id",        // field on the FOREIGN (customers) document
      as: "customer"               // name of the new array field holding matches
    }
  }
])

[
  {
    _id: 1,
    customerId: 101,
    total: 42.5,
    customer: [
      { _id: 101, name: 'Alice Chen', email: 'alice@example.com' }
    ]
  }
]

Syntax Breakdown

Field	Meaning
from	The other collection to join against — must be in the same database
localField	The field on the input (current pipeline) documents
foreignField	The field on the `from` collection's documents to match against
as	The name of the new array field that will hold all matched documents

Note

The result of a basic $lookup is always an array in the as field, even when exactly one document matches (as above) or none do (an empty array) — $lookup never returns a bare object.

$unwind After $lookup

Since $lookup always produces an array, it's extremely common to $unwind immediately afterward when you expect (or require) exactly one match per document — this flattens the single-element array into a plain embedded object.

Lookup + unwind for a 1:1 relationship

db.orders.aggregate([
  { $lookup: { from: "customers", localField: "customerId", foreignField: "_id", as: "customer" } },
  { $unwind: "$customer" }   // customer becomes a plain object, not a 1-element array
])

[
  {
    _id: 1,
    customerId: 101,
    total: 42.5,
    customer: { _id: 101, name: 'Alice Chen', email: 'alice@example.com' }
  }
]

Warning

Plain $unwind after $lookup silently drops any document whose join found no match (empty array). Add preserveNullAndEmptyArrays: true to the $unwind stage if orphaned orders (with no matching customer) should still appear in the results.

Pipeline Lookup — let / $$vars for Complex Joins

The basic localField/foreignField form only supports a simple equality join. For anything more complex — multiple join conditions, filtering the joined collection, or joining on a computed expression — use the pipeline form, which defines a sub-pipeline to run against the foreign collection, with variables passed in via let.

Pipeline lookup — only the customer's SHIPPED orders

db.customers.aggregate([
  {
    $lookup: {
      from: "orders",
      let: { custId: "$_id" },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$customerId", "$$custId"] },
                { $eq: ["$status", "shipped"] }
              ]
            }
          }
        },
        { $project: { _id: 1, total: 1, createdAt: 1 } }
      ],
      as: "shippedOrders"
    }
  }
])

Note

Inside the sub-pipeline, variables from let are referenced with a double-dollar prefix ($$custId), to distinguish them from ordinary field references ($customerId). This is what makes the pipeline form a correlated subquery, conceptually equivalent to a SQL correlated subquery inside a LEFT JOIN LATERAL.

Embedding vs $lookup — the Decision

Choose	When
Embed	The related data is read together with the parent almost every time, doesn't grow unbounded, and doesn't need to be updated independently across many parents.
$lookup (reference)	The related data is large, shared/reused across many parent documents, updated independently, or grows unbounded (e.g. a customer's full order history).

Tip

A common real-world hybrid: embed a small, frequently-needed summary of the related data (e.g. customer name denormalized onto the order) for fast reads, while still storing the reference (customerId) so a $lookup can pull the full record when needed.

Index Requirements

Warning

$lookup performance depends entirely on an index existing on the foreign collection's foreignField (or the equivalent field used in a pipeline-form sub-pipeline's $match). Without one, every single input document triggers a full collection scan of from — catastrophically slow at any real scale.

Index the foreign collection's join field

db.customers.createIndex({ _id: 1 })   // usually free — the default _id index
db.orders.createIndex({ customerId: 1 })   // needed on the "many" side for the reverse lookup

Correlated Subquery Pattern — Latest Related Document

Each customer's single most recent order

db.customers.aggregate([
  {
    $lookup: {
      from: "orders",
      let: { custId: "$_id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$customerId", "$$custId"] } } },
        { $sort: { createdAt: -1 } },
        { $limit: 1 }
      ],
      as: "latestOrder"
    }
  },
  { $unwind: { path: "$latestOrder", preserveNullAndEmptyArrays: true } }
])

Basic $lookup performs a left outer join keyed by simple field equality; the result is always an array in the as field.
$unwind right after $lookup is standard for 1:1 relationships — remember preserveNullAndEmptyArrays to keep unmatched documents.
Pipeline-form $lookup (with let/$$vars) supports multi-condition joins, filtering, sorting, and limiting the joined side — a correlated subquery.
Index the foreign collection's join field — an un-indexed $lookup scans the entire foreign collection per input document.