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.
db.collection.aggregate([
{
$lookup: {
from: "<otherCollection>",
localField: "<field_in_current>",
foreignField: "<field_in_other>",
as: "<output_array_field>"
}
}
])
fromnames the other collection, in the same database.localFieldis a field of the input documents, the documents that enter the stage.foreignFieldis the field of thefromdocuments that has to equal it.asnames the new array field that receives the matches.
This page is lesson 9 of the MongoDB course. The lessons before it:
- Introduction to MongoDB
- Installation & Database Creation
- CRUD operations in MongoDB
- Embedded Documents and Arrays
- Validation rules that enforce structure in MongoDB
- Visualize MongoDB Relationships (Embedded vs Referenced)
- What Is an Index in MongoDB?
- 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"
}
}
])
| _id | title | movie_reviews |
|---|---|---|
| 1 | Inception | [ { _id: 100, movie_id: 1, user_id: 10, rating: 5 }, { _id: 101, movie_id: 1, user_id: 11, rating: 4 } ] |
| 2 | The Matrix | [ { _id: 102, movie_id: 2, user_id: 10, rating: 3 } ] |
| 3 | Interstellar | [] |
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:
$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" }
])
| _id | title | movie_reviews |
|---|---|---|
| 1 | Inception | { _id: 100, movie_id: 1, user_id: 10, rating: 5 } |
| 1 | Inception | { _id: 101, movie_id: 1, user_id: 11, rating: 4 } |
| 2 | The 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:
| _id | title |
|---|---|
| 3 | Interstellar |
Interstellar is back in the result, without a movie_reviews field. The four documents match the rows of a SQL LEFT JOIN.
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:
| title | movie_reviews.rating | reviewer.name |
|---|---|---|
| Inception | 5 | Ada |
| Inception | 4 | Grace |
| The Matrix | 3 | Ada |
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"
}
}
])
| _id | title | good_reviews |
|---|---|---|
| 1 | Inception | [ { _id: 100, movie_id: 1, user_id: 10, rating: 5 }, { _id: 101, movie_id: 1, user_id: 11, rating: 4 } ] |
| 2 | The Matrix | [] |
| 3 | Interstellar | [] |
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"
}
}
])
| _id | title | good_reviews |
|---|---|---|
| 1 | Inception | [ { _id: 100, movie_id: 1, user_id: 10, rating: 5 }, { _id: 101, movie_id: 1, user_id: 11, rating: 4 } ] |
| 2 | The Matrix | [] |
| 3 | Interstellar | [] |
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.
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:
| strategy | indexName | |
|---|---|---|
before createIndex | HashJoin | none |
after createIndex | IndexedLoopJoin | movie_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:
| title | reviewer |
|---|---|
| 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:
- 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
localFieldandforeignField. - Drag
reviews.movie_idontomovies._id, andreviews.user_idontousers._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. - Choose Query → Query Editor, type the pipeline with your database name in place of
db, and press Run. - Right-click the
moviesheader and choose Data Explorer. Click the arrow next to the collection name and choose the Cascade entry forreviews, then do the same fromreviewsforusers. Move to Inception: its reviews show in the next pane, and the user of the selected review in the pane after that.
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.