Skip to content
MongoDB — From Zero to Hero

$lookup — joining collections

MongoDB stores related data in separate collections often enough that you will sometimes need to stitch two of them back together. The $lookup stage does that join inside an aggregation pipeline: for each incoming document, it finds the matching documents in another collection and attaches them as an array field. It is a left outer join — every input document comes through, with an empty array when nothing matches.

We add a second collection to the shop. Alongside sales, there is now a books collection describing each title:

{ "_id": "Dune", "author": "Frank Herbert", "pages": 412, "year": 1965 }

A sales order references a book by its title:

{ "_id": "a1", "title": "Dune", "quantity": 3, "price": 12 }

The equality form: localField and foreignField

Section titled “The equality form: localField and foreignField”

The simplest $lookup matches one local field against one foreign field. You name the collection to join (from), the field on the incoming document (localField), the field on the foreign document (foreignField), and the name of the array field to add (as).

flowchart LR
  S["sales: title = Dune"] --> L["$lookup from books on title"]
  B["books: _id = Dune"] --> L
  L --> O["sales doc with book array attached"]
Each sales document is joined to matching books and carries them in a new array field
db.sales.aggregate([
  { $lookup: {
      from: "books",
      localField: "title",
      foreignField: "_id",
      as: "book"
  } }
])

Each sales document now carries a book array holding the matched book document:

[
  {
    "_id": "a1",
    "title": "Dune",
    "quantity": 3,
    "price": 12,
    "book": [
      { "_id": "Dune", "author": "Frank Herbert", "pages": 412, "year": 1965 }
    ]
  }
]

The result is always an array, even when at most one document matches, because a join can in general match many. A common next step is $unwind (the next lesson) to flatten that one-element array into a plain object.

In Compass: the Aggregations tab offers $lookup from a dropdown and lets you pick the foreign collection and fields with form inputs. The preview shows the new array field appended to each document, which makes the “join attaches an array” behaviour concrete.

The pipeline form for richer joins

Section titled “The pipeline form for richer joins”

When you need more than a single-field equality — filtering the foreign documents, projecting them, or matching on several conditions — use the pipeline form. You declare let variables from the local document and run a sub-pipeline against the foreign collection, comparing with $expr. Here we attach only books published after 1950, exposing just author and year:

db.sales.aggregate([
  { $lookup: {
      from: "books",
      let: { t: "$title" },
      pipeline: [
        { $match: { $expr: { $eq: ["$_id", "$$t"] } } },
        { $match: { year: { $gt: 1950 } } },
        { $project: { _id: 0, author: 1, year: 1 } }
      ],
      as: "book"
  } }
])

The attached array now holds only the trimmed, filtered foreign document:

[
  {
    "_id": "a1",
    "title": "Dune",
    "quantity": 3,
    "price": 12,
    "book": [ { "author": "Frank Herbert", "year": 1965 } ]
  }
]

Note the $$t syntax: a single $ references a field of the foreign document, while $$ references a let variable carried in from the local document.

Tips and gotchas

Section titled “Tips and gotchas”
  • A $lookup runs a query against the foreign collection for every input document. Index the foreignField (or whatever the sub-pipeline matches on), or the join can become slow on real-world sizes.
  • It is a left outer join: input documents with no match still come through, with an empty as array. Filter those out afterward with $match if you only want joined rows.
  • Joins are powerful but not free. If you find yourself joining the same two collections on every read, that can be a sign the data wants to be embedded rather than referenced — a data-modeling question covered later.
  • The result field named in as is always an array. Pair $lookup with $unwind when you expect a single match and want a flat object.
In the equality form of $lookup, what does the "as" option name?
A sales document matches exactly one book. What is the type of the field $lookup adds?
Which index most improves $lookup performance?
In the pipeline form, what does $$t refer to?