$lookup: Joining Collections

MongoDB documents are designed to avoid joins wherever possible — you embed related data directly inside a document so a single read gets everything you need. But sometimes data genuinely belongs in separate collections: it’s large, it’s shared across many parent documents, or it grows without bound. When that happens, the $lookup aggregation stage lets you reach across collections and pull in matching documents, much like a SQL LEFT OUTER JOIN. It’s one of the most powerful stages in the aggregation framework, and also one of the easiest to misuse if you don’t understand what it’s doing under the hood.

Overview / How it works

A MongoDB collection has no enforced relationships between documents the way a relational database enforces foreign keys. If you store customers in one collection and orders in another, there is nothing at the database level linking an order to its customer except a field you chose to store — typically the customer’s _id. $lookup is the aggregation stage that performs that link at query time: for every document flowing through the pipeline, it searches another collection in the same database for documents whose value in a given field matches, and attaches the results as a new array field.

Two things about that are worth internalizing immediately. First, $lookup only works within a single database — you cannot join across databases or clusters. Second, the result is always an array in the as field, even when you know there’s exactly one match, because MongoDB has no way to know in advance whether zero, one, or many documents will match.

There are two forms of $lookup. The classic equality form (available since MongoDB 3.2) compares one field in the input documents against one field in the foreign collection using strict equality — this covers the majority of real-world joins. The more flexible pipeline form (added in 3.6) lets you pass variables from the input document into a nested aggregation pipeline run against the foreign collection, which supports multiple join conditions, non-equality matches (ranges, $expr comparisons), and filtering/projecting the foreign documents before they’re even returned.

Performance-wise, think of $lookup as a correlated subquery, conceptually similar to a nested-loop join: for each document (or batch of documents) from the pipeline so far, MongoDB looks for matches in the foreign collection. If foreignField is indexed, MongoDB can use that index to find matches quickly (an IXSCAN), the same way a query planner would for a normal find(). If it isn’t indexed, MongoDB has to scan the entire foreign collection’s candidates for every batch of input documents — on a large collection this gets slow fast. Since MongoDB 5.1, the server can also use an in-memory hash-join strategy for pure equality lookups against small-enough foreign collections, but you should never rely on that as a substitute for proper indexing.

Syntax

The equality form:

const lookupStage = {
  $lookup: {
    from: "<foreign collection>",
    localField: "<field in the input documents>",
    foreignField: "<field in the foreign collection>",
    as: "<name for the new array field>"
  }
};
  • from — the name of the collection to join with, in the same database.
  • localField — the field on the input (current pipeline) documents to match.
  • foreignField — the field on documents in the from collection to match against localField.
  • as — the name of the new array field added to each output document, holding all matching foreign documents (an empty array if there are no matches).

The pipeline form, for multi-field or non-equality joins:

const lookupWithPipeline = {
  $lookup: {
    from: "<foreign collection>",
    let: { varName: "$fieldFromInputDoc" },
    pipeline: [ /* aggregation stages, can reference $$varName */ ],
    as: "<name for the new array field>"
  }
};
  • let — defines variables (prefixed with $$ inside the sub-pipeline) bound to values from the input document.
  • pipeline — any aggregation pipeline run against the from collection; typically starts with a $match using $expr to compare $$varName against a foreign field.
  • as — same as above.

Examples

Example 1: A simple equality join

Given db.orders documents with a customerId field referencing db.customers._id:

db.orders.aggregate([
  { $match: { status: "shipped" } },
  {
    $lookup: {
      from: "customers",
      localField: "customerId",
      foreignField: "_id",
      as: "customerInfo"
    }
  }
]);

Output:

[
  {
    _id: ObjectId("60f7a3b2c1a4f2a1b8e4d123"),
    customerId: ObjectId("60f7a3b2c1a4f2a1b8e4d001"),
    status: "shipped",
    total: 129.99,
    customerInfo: [
      {
        _id: ObjectId("60f7a3b2c1a4f2a1b8e4d001"),
        name: "Priya Nair",
        email: "priya@example.com"
      }
    ]
  }
]

The $match runs first to shrink the working set to shipped orders, then $lookup attaches the matching customer document, wrapped in an array, as customerInfo.

Example 2: Flattening a one-to-one relationship with $unwind

Since we know each order has exactly one customer, we can unwrap the array into a plain object using $unwind, then keep only the fields we need:

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      localField: "customerId",
      foreignField: "_id",
      as: "customerInfo"
    }
  },
  { $unwind: "$customerInfo" },
  {
    $project: {
      total: 1,
      status: 1,
      "customerInfo.name": 1,
      "customerInfo.email": 1
    }
  }
]);

Output:

[
  {
    _id: ObjectId("60f7a3b2c1a4f2a1b8e4d123"),
    status: "shipped",
    total: 129.99,
    customerInfo: {
      name: "Priya Nair",
      email: "priya@example.com"
    }
  }
]

$unwind turns the single-element customerInfo array into a plain embedded object, which is much easier to work with downstream (and in application code) than always having to index into an array.

Example 3: Pipeline-style $lookup with $expr and $match

Suppose each order has an items array of { productId, qty }, and we only want to attach product details for items that are still in stock, keeping just the fields we need. This needs the pipeline form because we want to filter and project the foreign documents, not just equality-match them:

db.orders.aggregate([
  { $match: { _id: ObjectId("60f7a3b2c1a4f2a1b8e4d123") } },
  { $unwind: "$items" },
  {
    $lookup: {
      from: "products",
      let: { prodId: "$items.productId" },
      pipeline: [
        { $match: { $expr: { $eq: ["$_id", "$$prodId"] } } },
        { $match: { inStock: true } },
        { $project: { _id: 0, name: 1, price: 1 } }
      ],
      as: "productInfo"
    }
  }
]);

Output:

[
  {
    _id: ObjectId("60f7a3b2c1a4f2a1b8e4d123"),
    items: { productId: ObjectId("60f7a3b2c1a4f2a1b8e4d200"), qty: 2 },
    productInfo: [ { name: "Wireless Mouse", price: 19.99 } ]
  }
]

The let binds $items.productId to $$prodId, and the nested pipeline uses $expr to compare it against each product’s _id, then filters by inStock and trims the returned fields with $project. If the matching product were out of stock, productInfo would simply be an empty array.

How it works step by step

When the aggregation engine reaches a $lookup stage, it processes the incoming document stream roughly like this: for each input document (often in batches for efficiency), it takes the value at localField (or evaluates the let variables), and looks for documents in the from collection whose foreignField matches — either via an index seek if one exists on foreignField, via a hash-join strategy the planner may choose for equality-only lookups, or via a full collection scan if neither is possible. Every matching foreign document is collected into an array, which becomes the value of the as field on the output document; if nothing matches, that field is simply an empty array, not a missing field. The now-augmented document continues down the rest of the pipeline to whatever stage comes next — a $unwind, a $project, another $lookup, and so on. Because this happens once per input document, the cost of an unindexed $lookup scales with both the size of the input stream and the size of the foreign collection, which is why filtering early with $match and indexing foreignField matter so much.

Common Mistakes

1. Forgetting the result is always an array

It’s tempting to project a nested field straight out of the lookup result as if it were a single value:

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      localField: "customerId",
      foreignField: "_id",
      as: "customerInfo"
    }
  },
  {
    $project: {
      total: 1,
      customerName: "$customerInfo.name"
    }
  }
]);

This silently produces customerName: ["Priya Nair"] — an array with one element, not the string you expected — because customerInfo is an array even when there’s one match. Fix it with $unwind before projecting:

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      localField: "customerId",
      foreignField: "_id",
      as: "customerInfo"
    }
  },
  { $unwind: "$customerInfo" },
  {
    $project: {
      total: 1,
      customerName: "$customerInfo.name"
    }
  }
]);

2. Missing an index on foreignField

Joining reviews to products by SKU without an index on the foreign field forces a collection scan of products for every review document:

db.reviews.aggregate([
  {
    $lookup: {
      from: "products",
      localField: "productSku",
      foreignField: "sku",
      as: "product"
    }
  }
]);

On a collection with a few hundred thousand products and a similarly large reviews collection, this can turn a simple report into a query that takes minutes. Add an index on the foreign field so the lookup can use an index seek instead:

db.products.createIndex({ sku: 1 });

3. Comparing an ObjectId to a string

If customerId was accidentally stored as a plain string (say, copied from a URL parameter without conversion) while customers._id is a real ObjectId, the equality-form lookup will never match — customerInfo comes back empty for every single order, with no error at all:

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      localField: "customerId",
      foreignField: "_id",
      as: "customerInfo"
    }
  }
]);

The cleanest long-term fix is storing customerId as an ObjectId in the first place. If you’re stuck joining across the mismatch, convert inside a pipeline-style lookup:

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      let: { custId: "$customerId" },
      pipeline: [
        { $match: { $expr: { $eq: ["$_id", { $toObjectId: "$$custId" }] } } }
      ],
      as: "customerInfo"
    }
  }
]);

Best Practices

  • Always create an index on foreignField (or make sure it’s already _id) before running $lookup against a collection of any real size.
  • Put a $match stage before $lookup whenever possible, ideally one backed by an index, to shrink the number of input documents that need a lookup at all.
  • Use the pipeline form with an inner $match/$project to filter and trim the foreign documents you get back, instead of pulling entire documents across and filtering afterward.
  • Reach for $unwind immediately after a $lookup when you know the relationship is one-to-one or one-to-zero, so the rest of the pipeline works with plain objects instead of single-element arrays.
  • Prefer embedding over $lookup when the related data is small, bounded, and always read together with its parent — save $lookup for data that’s large, shared, or grows without bound.
  • Watch out for unbounded $lookup results: if the foreign side can match thousands of documents per input document, add a $limit or additional filters inside the pipeline form.
  • Run .explain("executionStats") on aggregations with $lookup and check the nested query stats for the lookup stage to confirm it’s using an index, not a collection scan.

Practice Exercises

  • You have db.authors and db.books, where each book document has an authorId referencing authors._id. Write an aggregation that joins each book to its author and returns only the book title alongside the author’s name (single flattened object per book, not an array).
  • You have db.enrollments (with a studentId and a courseId) and separate db.students and db.courses collections. Write an aggregation with two $lookup stages that produces one document per enrollment containing the student’s name and the course’s title.
  • Given db.orders with a customerId stored as a string and db.customers with a real ObjectId _id, write a pipeline-style $lookup that correctly joins them despite the type mismatch. Expected result shape: each order gains a customerInfo array with zero or one matching customer document.

Summary

  • $lookup performs a left-outer-join-style match against another collection in the same database, adding results as a new array field.
  • The equality form (localField/foreignField) covers most joins; the pipeline form (let/pipeline) supports multi-field and non-equality conditions plus filtering the foreign side.
  • The output field is always an array, even for one-to-one relationships — use $unwind to flatten it when you expect a single match.
  • Index foreignField so MongoDB can use an index seek instead of scanning the entire foreign collection per input document.
  • Type mismatches, like a string compared against an ObjectId, cause silent empty results rather than errors — convert types explicitly in the pipeline form when needed.
  • Filter early with $match, and project down to only the fields you need inside the lookup pipeline, to keep $lookup fast on large collections.