Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 70 ยท Aggregation pipeline

Joining with $lookup

Learn how $lookup joins collections in MongoDB aggregation, how the pipeline form works, what joins cost, and when embedding data is the better design.

MongoDB encourages you to keep related data together, but not everything fits in one document. Customers place many orders, products appear in many orders, and you sometimes need both sides in a single result. The $lookup stage is MongoDB's join: for each incoming document it finds matching documents in another collection and attaches them as an array.

This lesson covers the basic form, the more flexible pipeline form, the follow-up $unwind, what a join really costs, and how to decide between joining and embedding. If you know SQL joins (Joins, Left Join), $lookup will feel familiar, with one twist: the result is always an array field, not extra columns. The shell blocks are real MongoDB; the TryIts use a JavaScript stand-in.

What $lookup does

Input Each document from your pipeline.
Match Finds documents in another collection.
Attach Stores them in an array field.

No match gives an empty array, like a left outer join.

1 / 2

The basic $lookup

The basic form compares one field of the incoming document with one field of the other collection. The collection named in from must live in the same database.

JavaScript
db.orders.aggregate([
  { $lookup: {
      from: "customers",          // the other collection
      localField: "customerId",   // field in each order
      foreignField: "_id",        // field in customers
      as: "customer"              // name of the new array field
  } }
])

Every order comes back with a customer array. If the customer exists, the array has one element (more if the foreign field is not unique); if nothing matches, the array is empty and the order is still returned. That is the behavior of a left outer join, and there is no inner-join option: to drop unmatched orders you filter afterwards.

Because you usually want a single customer object, not a one-element array, the next stage is typically $unwind:

JavaScript
{ $unwind: "$customer" }

Remember from Working with Arrays in Pipelines that a default $unwind removes documents with an empty array. Here that turns the left join into an inner join, which is exactly what you want when an order without a customer is a data error. Add preserveNullAndEmptyArrays: true to keep such orders.

The stand-in for the basic form

The first stand-in implements $lookup with localField and foreignField, then unwinds the result. It also shows an order whose customer does not exist.

JS Basic $lookup, then $unwind

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The pipeline form

The basic form can only test equality of two fields. The pipeline form lets you run a whole sub-pipeline on the foreign collection. You pass values from the current document with let, read them as "$$name" inside the sub-pipeline, and compare them with $expr:

JavaScript
db.orders.aggregate([
  { $lookup: {
      from: "products",
      let: { ids: "$productIds", maxPrice: 100 },
      pipeline: [
        { $match: { $expr: { $and: [
            { $in: ["$_id", "$$ids"] },
            { $lte: ["$price", "$$maxPrice"] }
        ] } } },
        { $project: { name: 1, price: 1 } }
      ],
      as: "products"
  } }
])

This one joins an order to the products listed in its productIds array, keeps only items up to a price, and returns just two fields of each. Use the pipeline form when you need a range condition, several conditions, a sub-pipeline with $sort and $limit (for example "the three latest reviews of each product"), or to trim the foreign documents before they are attached. If you only need equality, prefer the basic form, which is simpler and easier for the server to optimize.

The second stand-in recreates a pipeline $lookup where the foreign side is filtered by an arbitrary condition, and counts how many foreign documents were examined with and without a lookup index.

JS Pipeline $lookup and what a join costs

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The stand-in makes the point with plain counts: a lookup against a foreign field with no usable index examines the foreign collection again for every incoming document, while an index turns each lookup into a few key probes. Real MongoDB behaves the same way in spirit, which leads to the cost rules below.

What a join costs

$lookup is not free, and the two collections do not magically merge:

  • Index the foreign field. Without an index on foreignField (or on the field you match inside the sub-pipeline), each incoming document triggers a scan of the foreign collection. With an index, each lookup is a quick search. Indexes are explained in How Indexes Work.
  • Shrink the input first. Put $match and $limit before $lookup, so you join 20 documents instead of 2 million.
  • Trim the output. A joined array can make a document very large, and a pipeline result document is still capped at 16 MB. Use $project inside the sub-pipeline to attach only the fields you need.
  • A join is a read-time cost. You pay it on every execution of the query, while embedding pays once at write time.

Join or embed?

Because MongoDB lets you store related data together, the first question is whether you need the join at all. If you always display the customer's name with the order, store the name (and maybe the city) in the order document, as in Embedding vs Referencing. The cost is duplicated data that can go stale, so decide by how the data changes: a customer's name at the time of an order should usually stay as it was, which makes embedding correct, not just fast.

Keep $lookup for relationships that are looked at occasionally, change independently, or are too large to embed: reports, admin screens, analytics (Project: Sales Analytics Pipelines). If a screen that serves every page view needs three lookups, that is a sign the model should change, not the query.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. An order has a customerId that matches no customer. What does $lookup (basic form) return for that order?

    $lookup behaves like a left outer join: the incoming document is kept and the as field is an empty array.
  2. 2. Why do most pipelines put { $unwind: "$customer" } right after a $lookup?

    $lookup always produces an array. Unwinding it gives a plain subdocument, and a default $unwind removes documents whose array is empty.
  3. 3. When do you need the pipeline form of $lookup?

    The basic form only compares one local field with one foreign field. The pipeline form accepts let variables and a full sub-pipeline.
  4. 4. Which change most directly reduces the cost of a $lookup?

    Fewer incoming documents and an index on the foreign field both cut the work, since the lookup runs once per incoming document.

Summary

  • $lookup attaches matching documents from another collection (same database) as an array in the as field.
  • No match yields an empty array, so the stage acts as a left outer join; $unwind afterwards makes it an inner join unless you preserve empty arrays.
  • The pipeline form takes let variables and a sub-pipeline for range conditions, filtering, sorting and trimming.
  • Index the foreign field, $match early, and project away what you do not need.
  • If you always need the data together, embed it instead of joining on every read.

Next, summarize results in several ways at once with $facet, $bucket and Other Stages.