Joining Collections in MongoDB with $lookup

MongoDB stores related data in separate collections more often than newcomers expect, and when you need to bring those collections back together in a query, $lookup is the tool. It is an aggregation pipeline stage that performs the equivalent of a SQL LEFT OUTER JOIN: every input document is kept, and matching documents from the other collection are attached to it as an array.

This guide covers the two forms of $lookup, the common patterns around it, and the behaviors that affect correctness and performance.

The equality form

The simplest $lookup matches one field in the current collection against one field in another collection.

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

This reads: for each order, find every document in customers whose _id equals this order's customerId, and store the matches in a new array field called customer.

Three things are worth fixing in your mental model immediately:

1. The result is always an array, even when you expect exactly one match. An order joined to its single customer still gets customer: [ { ... } ], not customer: { ... }. You almost always follow $lookup with $unwind to flatten it (covered below).

2. It is a left outer join. Input documents with no match are kept, and as is set to an empty array []. There is no inner-join switch; you filter out non-matches yourself afterward.

3. from must be in the same database, and historically could not be sharded. As of MongoDB 5.1, the from collection can be sharded.

If localField points at an array, $lookup matches if any array element equals a foreignField value, which is how you resolve many-to-many style references stored as an array of IDs.

The let / pipeline form

The equality form only supports a single equality condition. When you need more (multiple join keys, a range condition, or any filtering on the joined collection), use the let and pipeline form, available since MongoDB 3.6.

db.orders.aggregate([
  {
    $lookup: {
      from: "items",
      let: { orderId: "$_id", region: "$region" },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$orderId", "$$orderId"] },
                { $eq: ["$region", "$$region"] },
                { $gt: ["$price", 100] }
              ]
            }
          }
        },
        { $project: { _id: 0, sku: 1, price: 1 } }
      ],
      as: "expensiveItems"
    }
  }
])

The mechanics that trip people up:

• let defines variables from the current (outer) document. You reference them inside pipeline with the double-dollar prefix: $$orderId. A single dollar like $orderId inside that pipeline refers to a field of the foreign document, not the outer one. Mixing these up is the most common $lookup bug.

• You must use $expr inside $match to compare a foreign field against a let variable, because the comparison is computed per document rather than being a plain query predicate. Plain query operators in the pipeline (like { price: { $gt: 100 } }) still work for conditions that only involve the foreign collection.

• You can run any stages in pipeline, including $project, $sort, $limit, and even a nested $lookup. This lets you shape and trim the joined data before it is attached, which keeps documents small.

There is also a concise correlated form: if you supply localField/foreignField and a pipeline, MongoDB applies the equality match and the pipeline together. This was made more ergonomic in MongoDB 5.0.

The $unwind pattern

Because $lookup produces an array, the standard follow-up is $unwind to produce one output document per match.

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

By default, $unwind drops documents whose array is empty, which turns the left join into an inner join. If you want to keep unmatched orders, set preserveNullAndEmptyArrays:

{ $unwind: { path: "$customer", preserveNullAndEmptyArrays: true } }

The query optimizer can coalesce an $unwind placed immediately after $lookup into the lookup itself, so this pattern does not necessarily materialize a large intermediate array. Keep the $unwind adjacent to the $lookup to allow that optimization.

Performance considerations

$lookup executes the join by running a query against from for each input document. The single biggest lever is an index:

• Index the foreignField. In the equality form, a missing index on the joined collection's foreignField turns every lookup into a full collection scan, and the cost multiplies by the number of input documents. With an index, each lookup is an index seek.
• In the pipeline form, the index must support the $match inside the pipeline. $expr equality comparisons against let variables can use indexes, but verify with explain.

Reduce the input set before the join. A $match early in the outer pipeline means fewer lookups run. Joining 50 filtered documents is cheap; joining a million unfiltered ones is not.

Trim the joined data with a $project inside the pipeline form so you do not attach entire foreign documents you will discard a stage later. This matters because the pipeline still respects the 100MB per-stage memory limit; if a stage exceeds it you need allowDiskUse: true, which is slower.

Finally, ask whether you should be joining at all. MongoDB's data model often favors storing related data together in one document. If you find yourself joining the same two collections on nearly every read, that is a signal to revisit the schema. See our guide on embedding versus referencing for how to make that call.

Debugging joins

When a $lookup returns nothing or the wrong matches:

• Check the field types. customerId stored as a string will not match an _id stored as ObjectId, and there is no implicit coercion. This is the most frequent cause of an unexpectedly empty as array.
• In the pipeline form, confirm you used $$ for outer variables and $ for foreign fields.
• Run the join's logic as a standalone find() on the from collection with a hardcoded value to confirm the match works before wiring it into let/pipeline.

Run the whole pipeline with explain to confirm the foreign-collection query used an index rather than a collection scan:

db.orders.aggregate([ /* stages */ ], { explain: true })

For background on how stages flow and where $lookup fits, see our aggregation pipeline guide, and for indexing the join field, the MongoDB indexes guide.

Trying joins interactively

Building a correlated $lookup is iterative, and seeing intermediate output speeds it up. Mako connects to MongoDB and offers AI-assisted query writing, which helps when you are recalling the let/$$ syntax or translating a multi-condition join into the pipeline form. Mako is a read and query tool, so it suits exploring and analyzing joined data rather than administering the deployment.

To load sample collections to join, see our guides on importing CSV, JSON, and Excel into MongoDB.

Mako connects to MongoDB, PostgreSQL, MySQL, and more with AI-powered autocomplete. Try it free at mako.ai.