Back to all posts

MongoDB $lookup: Join Collections with Examples, Pipelines, and $unwind

For developers who write SQL joins and now need the same result from two MongoDB collections.

On this page

$lookup is an aggregation stage that finds the matching documents in a second collection and adds them to each document as an array. For example, you can return every movie with its reviews attached. MongoDB has no JOIN keyword, so $lookup is how one query reads two collections.

The stage takes four fields:

db.collection.aggregate([
  {
    $lookup: {
      from: "<otherCollection>",
      localField: "<field_in_current>",
      foreignField: "<field_in_other>",
      as: "<output_array_field>"
    }
  }
])
  • from names the other collection, in the same database.
  • localField is a field of the input documents, the documents that enter the stage.
  • foreignField is the field of the from documents that has to equal it.
  • as names the new array field that receives the matches.

This page is lesson 9 of the MongoDB course. The lessons before it:

  1. Introduction to MongoDB
  2. Installation & Database Creation
  3. CRUD operations in MongoDB
  4. Embedded Documents and Arrays
  5. Validation rules that enforce structure in MongoDB
  6. Visualize MongoDB Relationships (Embedded vs Referenced)
  7. What Is an Index in MongoDB?
  8. Aggregation Pipeline Explained

Create three sample collections in mongosh, the MongoDB shell:

db.movies.insertMany([
  { _id: 1, title: "Inception" },
  { _id: 2, title: "The Matrix" },
  { _id: 3, title: "Interstellar" }
])

db.reviews.insertMany([
  { _id: 100, movie_id: 1, user_id: 10, rating: 5 },
  { _id: 101, movie_id: 1, user_id: 11, rating: 4 },
  { _id: 102, movie_id: 2, user_id: 10, rating: 3 }
])

db.users.insertMany([
  { _id: 10, name: "Ada" },
  { _id: 11, name: "Grace" }
])

Every example on this page was run on MongoDB 8.2.

$lookup

With localField and foreignField, $lookup attaches the documents of the other collection whose foreignField value equals the input document's localField value. For example, you can attach to each movie the reviews whose movie_id equals the movie's _id.

db.movies.aggregate([
  {
    $lookup: {
      from: "reviews",
      localField: "_id",
      foreignField: "movie_id",
      as: "movie_reviews"
    }
  }
])
_idtitlemovie_reviews
1Inception[ { _id: 100, movie_id: 1, user_id: 10, rating: 5 }, { _id: 101, movie_id: 1, user_id: 11, rating: 4 } ]
2The Matrix[ { _id: 102, movie_id: 2, user_id: 10, rating: 3 } ]
3Interstellar[]

Inception got its two reviews, and The Matrix got its one. Interstellar has no review and is still in the result, with an empty array. Each review arrives whole, because the query removes no field.

The MongoDB manual calls $lookup a left outer join. A LEFT JOIN in SQL also keeps every movie, but it returns one row per review and puts NULL in Interstellar's rating:

The same data joined two ways. A SQL LEFT JOIN returns four rows: Inception twice, with ratings 5 and 4, The Matrix with 3, and Interstellar with NULL. $lookup returns three documents: Inception with two reviews in movie_reviews, The Matrix with one, and Interstellar with an empty array.

$unwind

$unwind outputs one copy of a document for each element of an array field. For example, you can turn each movie with its reviews into one document per review.

db.movies.aggregate([
  { $lookup: { from: "reviews", localField: "_id",
               foreignField: "movie_id", as: "movie_reviews" } },
  { $unwind: "$movie_reviews" }
])
_idtitlemovie_reviews
1Inception{ _id: 100, movie_id: 1, user_id: 10, rating: 5 }
1Inception{ _id: 101, movie_id: 1, user_id: 11, rating: 4 }
2The Matrix{ _id: 102, movie_id: 2, user_id: 10, rating: 3 }

Inception appears twice, and each Inception document holds one review instead of an array. Interstellar is missing: by default, $unwind outputs nothing for an empty array. These three documents match the rows of a SQL inner join.

preserveNullAndEmptyArrays

With preserveNullAndEmptyArrays: true, $unwind also outputs a document whose array is empty, null or missing. For example, you can keep Interstellar in the list of reviews.

db.movies.aggregate([
  { $lookup: { from: "reviews", localField: "_id",
               foreignField: "movie_id", as: "movie_reviews" } },
  { $unwind: { path: "$movie_reviews", preserveNullAndEmptyArrays: true } }
])

The query returns four documents: the three above, and this one:

_idtitle
3Interstellar

Interstellar is back in the result, without a movie_reviews field. The four documents match the rows of a SQL LEFT JOIN.

After $lookup, Inception holds reviews 100 and 101 in an array and Interstellar holds an empty array. After $unwind, Inception becomes two documents, one per review. Interstellar produces no document by default, and with preserveNullAndEmptyArrays set to true it comes out without a movie_reviews field.

A second $lookup

A second $lookup can match on a field that the first $lookup added. For example, you can put each reviewer's name next to their rating.

db.movies.aggregate([
  { $lookup: { from: "reviews", localField: "_id",
               foreignField: "movie_id", as: "movie_reviews" } },
  { $unwind: "$movie_reviews" },
  { $lookup: { from: "users", localField: "movie_reviews.user_id",
               foreignField: "_id", as: "reviewer" } },
  { $unwind: "$reviewer" }
])

The query returns three documents, shown here by their title, rating and reviewer name:

titlemovie_reviews.ratingreviewer.name
Inception5Ada
Inception4Grace
The Matrix3Ada

Ada gave Inception a 5 and The Matrix a 3, and Grace gave Inception a 4. After the first $unwind, each document holds one review. The second $lookup then finds the one user who wrote it.

pipeline

With pipeline, $lookup runs more stages on the matched documents before it adds them to the array. For example, you can attach only the reviews rated 4 or more.

db.movies.aggregate([
  {
    $lookup: {
      from: "reviews",
      localField: "_id",
      foreignField: "movie_id",
      pipeline: [ { $match: { rating: { $gte: 4 } } } ],
      as: "good_reviews"
    }
  }
])
_idtitlegood_reviews
1Inception[ { _id: 100, movie_id: 1, user_id: 10, rating: 5 }, { _id: 101, movie_id: 1, user_id: 11, rating: 4 } ]
2The Matrix[]
3Interstellar[]

Inception kept both reviews. The Matrix's only review is rated 3, so The Matrix is in the result with an empty array. A pipeline in the same $lookup as localField and foreignField needs MongoDB 5.0 or later.

let

let turns fields of the input document into variables that the pipeline can read. For example, you can write the same join with the movie's _id in a variable called movieId.

db.movies.aggregate([
  {
    $lookup: {
      from: "reviews",
      let: { movieId: "$_id" },
      pipeline: [
        { $match: { $expr: { $and: [
          { $eq: ["$movie_id", "$$movieId"] },
          { $gte: ["$rating", 4] }
        ] } } }
      ],
      as: "good_reviews"
    }
  }
])
_idtitlegood_reviews
1Inception[ { _id: 100, movie_id: 1, user_id: 10, rating: 5 }, { _id: 101, movie_id: 1, user_id: 11, rating: 4 } ]
2The Matrix[]
3Interstellar[]

This query returns the same three documents as the query with localField, foreignField and pipeline. Two dollar signs read a variable, such as $$movieId, and one dollar sign reads a field of the review, such as $movie_id. A $match can read a variable only inside $expr.

The Inception document has _id 1. The let block sets movieId from _id, so $$movieId is 1. The pipeline on reviews keeps reviews 100 and 101, whose movie_id is 1 and whose rating is 4 or more, and skips review 102, whose movie_id is 2.

localField and foreignField match only when two values are equal. Inside $expr, a let variable can also be compared with $lt, $gt and the other comparison operators.

What goes wrong

A $lookup without an index

Index the foreignField of the other collection, here reviews.movie_id. Without that index, the manual warns, a $lookup on localField and foreignField "will likely have poor performance".

db.reviews.createIndex({ movie_id: 1 })

explain() shows whether the join uses the index:

db.movies.explain().aggregate([
  { $lookup: { from: "reviews", localField: "_id",
               foreignField: "movie_id", as: "movie_reviews" } }
])

On MongoDB 8.2, the plan holds an EQ_LOOKUP stage under queryPlanner.winningPlan.queryPlan. The strategy field of EQ_LOOKUP changed with the index:

strategyindexName
before createIndexHashJoinnone
after createIndexIndexedLoopJoinmovie_id_1

Look for indexName in the plan. It names the index that the join uses. The explain results page doesn't list the strategy names.

A second $lookup before $unwind

Without the first $unwind of the query under "A second $lookup", movie_reviews is still an array. movie_reviews.user_id is then an array of user ids, and the second $lookup attaches every user whose _id is in it:

db.movies.aggregate([
  { $lookup: { from: "reviews", localField: "_id",
               foreignField: "movie_id", as: "movie_reviews" } },
  { $lookup: { from: "users", localField: "movie_reviews.user_id",
               foreignField: "_id", as: "reviewer" } }
])

The query returns three documents. Inception's document has this reviewer field:

titlereviewer
Inception[ { _id: 10, name: 'Ada' }, { _id: 11, name: 'Grace' } ]

Inception has both users in one array, and its ratings are in the movie_reviews array. No field pairs a user with a rating, and a second run of the same query returned Grace before Ada.

Data that belongs in one document

Every read of a movie with its reviews pays for the $lookup. If the reviews are few, bounded and always read with the movie, embed them in the movie document instead. Keep them in their own collection when they grow without limit.

MongoDB limits a document to 16 mebibytes, 16,777,216 bytes. Once a movie with embedded reviews reaches that limit, the update that appends one more review fails. MongoDB schema design explains the choice in more depth.

Build $lookup pipelines in DbSchema

DbSchema connects to MongoDB and reads a sample of the documents in each collection. By default, DbSchema reads 100 documents from the start and 100 from the end, and the Schema Scan Depth setting reads more.

From them, DbSchema lists each field with its BSON type, the type that MongoDB stores, nested documents and arrays included. This list approximates what the documents hold; MongoDB enforces no such schema.

Where a collection has a $jsonSchema validator, DbSchema reads the fields from the validator, not from the documents. When you create or edit a collection in DbSchema, DbSchema writes its validator to the database and to the model file.

With the three sample collections, the work in DbSchema goes in this order:

  1. Choose File → New Model, connect to the database, and reverse-engineer it. The diagram shows each collection with its fields, so check there the names that you pass as localField and foreignField.
  2. Drag reviews.movie_id onto movies._id, and reviews.user_id onto users._id. DbSchema draws each one as a virtual relation, a line on the diagram that DbSchema saves in the model file. MongoDB never sees the relation.
  3. Choose Query → Query Editor, type the pipeline with your database name in place of db, and press Run.
  4. Right-click the movies header and choose Data Explorer. Click the arrow next to the collection name and choose the Cascade entry for reviews, then do the same from reviews for users. Move to Inception: its reviews show in the next pane, and the user of the selected review in the pane after that.
The DbSchema Data Explorer with a customer document on the left and, joined by a line, the first of her two orders on the right

File → Export As... → HTML5/PDF Documentation writes the diagram as a vector image, with collection and field comments as mouse-over tooltips. Whoever inherits the pipeline can then see which field points where. The Community Edition exports this documentation for a diagram of up to 12 tables, with "DbSchema Trial" written across the diagram image.

Download DbSchema at https://dbschema.com/download.html, connect it to your MongoDB database, and draw the virtual relations before you write the $lookup. The free Community Edition connects, reverse-engineers, draws the diagram and runs the Query Editor. Pro saves the model with its virtual relations, follows them in the Data Explorer, and documents any diagram in HTML5.