$lookup in MongoDB

Sam Erickson
Updated yesterday
0

Key Takeaways

MongoDB $lookup performs a left outer join and adds matching foreign documents as an array; unmatched documents receive an empty array.
For the Posts/Comments example, join posts._id to comments.postId and index comments.postId.
Reduce the input set before joining when that preserves your query’s meaning, and filter foreign documents inside the lookup pipeline.
Check execution statistics and match counts. An index does not make an unbounded result inexpensive.

MongoDB $lookup joins documents from a collection in the same database and attaches the matches to each input document as an array. It reshapes query results; it does not permanently merge the collections. Its cost depends on the join condition, eligible indexes, input count, and number of matches.

If you need a refresher on how stages fit together, start with the MongoDB aggregation pipeline.

What is $lookup in MongoDB?

Think of a left outer join as “keep every post, then attach its matching comments.” A post without comments still appears in the result. Unlike a flattened SQL result, one post with several matches initially remains one document containing an array.

Input post     Matching foreign documents     Lookup result
P1             C1.postId = P1                  P1.comments = [C1, C2]
               C2.postId = P1

P2             No matching comments           P2.comments = []

The basic equality syntax has four fields:

from: the foreign collection’s name.
localField: the join field on the input document.
foreignField: the corresponding field on foreign documents.
as: the output array’s field name. An existing field with that name is overwritten.

These fields and their matching rules are defined in the $lookup reference.

MongoDB $lookup example: Posts and Comments

Create the post and comment collections

Run this setup once in an empty demo database using mongosh. Subsequent examples reuse the same shell session and variables.

db = db.getSiblingDB("stackchief_lookup_demo");

const postId = ObjectId("507f1f77bcf86cd799439011");
const quietPostId = ObjectId("507f1f77bcf86cd799439012");

db.posts.insertMany([
  { _id: postId, title: "My first post", status: "published" },
  { _id: quietPostId, title: "No comments yet", status: "draft" }
]);

db.comments.insertMany([
  { postId, comment: "I liked this post", approved: true },
  { postId, comment: "Awaiting review", approved: false }
]);

Use identifiers rather than titles as references. Two posts can share a title, and editing a title should not change which comments belong to a post. Keep both sides of this relationship as ObjectId values rather than mixing ObjectIds and strings.

Run the equality lookup

const lookupComments = {
  $lookup: {
    from: "comments",
    localField: "_id",
    foreignField: "postId",
    as: "comments"
  }
};

db.posts.aggregate([lookupComments]);

Each matching comment becomes an element of comments. To make the output easier to inspect, project only the title and comment text:

db.posts.aggregate([
  lookupComments,
  { $project: { _id: 0, title: 1, "comments.comment": 1 } }
]);

The projection removes the post’s _id from the returned document and selects the nested comment text; it does not change the stored documents. See MongoDB projections for more field-selection examples.

Example output, shown as valid JSON; document and comment order can differ:

[
  {
    "title": "My first post",
    "comments": [
      { "comment": "I liked this post" },
      { "comment": "Awaiting review" }
    ]
  },
  {
    "title": "No comments yet",
    "comments": []
  }
]

For more join shapes, see the MongoDB $lookup examples.

Flattening lookup results with $unwind

Use $unwind when the next stage needs one document per comment rather than an array of comments. It replaces the array field with one element in each output document.

db.posts.aggregate([
  lookupComments,
  {
    $unwind: {
      path: "$comments",
      preserveNullAndEmptyArrays: true
    }
  }
]);

The commented post now produces two documents. The unmatched post remains because preserveNullAndEmptyArrays is true; its empty comments field is omitted. The default is false, so the shorter { $unwind: "$comments" } form drops posts whose lookup array is empty.

Filtering foreign documents with pipeline and let

A foreign pipeline lets you filter and shape the documents placed in the output array. For a correlated join—one whose foreign query depends on the current input document—capture local values with let and reference them with a double-dollar prefix. Inside $match, use $expr to compare a foreign field with a captured value.

const lookupApprovedComments = {
  $lookup: {
    from: "comments",
    let: { postId: "$_id" },
    pipeline: [
      {
        $match: {
          approved: true,
          $expr: { $eq: ["$postId", "$$postId"] }
        }
      },
      { $sort: { _id: 1 } },
      { $limit: 20 },
      { $project: { _id: 0, comment: 1 } }
    ],
    as: "comments"
  }
};

db.posts.aggregate([lookupApprovedComments]);

Here, $postId reads the foreign comment’s field, while $$postId reads the value captured from the current post. The foreign pipeline keeps approved comments, sorts by _id, takes at most 20 per post, and returns only their text. The limit bounds the array’s count, not the number of posts returned or the total work needed to find those matches.

For an equality join with additional foreign filtering, you can also keep localField and foreignField alongside pipeline. That concise form is supported starting in MongoDB 5.0.

MongoDB $lookup performance

Index the foreign join field

For the equality lookup above, the important supporting index is on comments.postId:

db.comments.createIndex({ postId: 1 });

Equality lookups generally benefit from an index on the foreign join field. They do not inherently scan the entire foreign collection for every post. An index on the source collection serves a different purpose: it can help select posts before they enter the join.

See creating MongoDB indexes for the underlying commands. For the approved-comments pipeline, a compound index beginning with postId and including the filter and sort fields is a candidate to test—not a guarantee of a particular plan.

Index eligibility is more restrictive for comparisons inside a correlated $expr:

$eq, $lt, $lte, $gt, and $gte can use a foreign index for eligible field-to-constant comparisons.
The captured let value must resolve to a constant for that input document. Comparing two foreign fields does not qualify for this optimization.
An empty or missing captured value prevents index use for that comparison.
Multikey, partial, and sparse indexes are not used for these $expr comparisons.

Those restrictions describe the documented $expr index-use rules, not a blanket rule for every lookup shape.

For string join keys, also check collation—the rules used to compare strings. To support string comparisons, the operation and index must use the same collation. ObjectId references avoid that particular string-comparison concern.

Reduce input documents without changing the question

If you only want published posts, filter them before joining:

const publishedPostsPipeline = [
  { $match: { status: "published" } },
  lookupComments
];

db.posts.aggregate(publishedPostsPipeline);

This removes draft posts from the join’s input. It is not interchangeable with filtering approved comments inside the foreign pipeline: one selects posts, while the other selects comments. Only move filters earlier when their required values already exist and the results remain equivalent.

MongoDB can reorder or combine eligible stages during optimization. For example, adjacent lookup and unwind stages can be combined internally, and some independent filters can move earlier. The exact transformations depend on pipeline structure and server release; inspect the optimized plan rather than assuming every stage runs literally as written.

Verify the plan with executionStats

Use the supported shell explain form to inspect the actual work:

db.posts.explain("executionStats").aggregate(publishedPostsPipeline);

executionStats executes the winning plan to completion. Start with representative staging data if the aggregation may be expensive.

Inspect the source plan and foreign lookup work separately. Depending on the execution engine and plan shape, relevant statistics may appear under different nodes:

totalKeysExamined and totalDocsExamined: index entries and documents examined.
indexesUsed and collectionScans, when a separate lookup-stage summary is present.
nReturned: documents returned at the reported stage, not the number of elements inside every joined array.
executionTimeMillis and stage-level executionTimeMillisEstimate: execution timing information.

The explain-results reference documents these fields. Inspect comment-array lengths too: returning one post can still mean returning many comments.

Compare plans before and after an index or pipeline change using realistic data distributions, including heavily commented posts. Measure application latency as well: explain timing excludes network transmission and is not necessarily representative of normal cached-plan execution. Server-side execution alone is not evidence that a lookup is faster than an application-side alternative.

Considerations with $lookup

Sharded collections and deployment support

The old rule that the foreign collection must be unsharded is obsolete. MongoDB supports a sharded from collection starting in 5.1. Using $lookup inside a transaction while targeting a sharded collection is supported starting in 8.0.

$lookup is available in MongoDB Community, Enterprise, and Atlas; it is not an Enterprise-only feature. If a sharded lookup fails, check the documentation for your server version and whether the operation runs inside a transaction.

Match cardinality and document size

Cardinality means how many matches a join produces. A post with a large number of comments can create a large array even with an efficient index. Bound the matches your endpoint actually needs, project only necessary fields, and consider querying paginated comments separately.

Each returned aggregation document must fit within MongoDB’s 16 MiB BSON document-size limit. Intermediate pipeline documents can exceed that size, but other resource limits still apply. allowDiskUse does not remove the returned-document size limit.

Choose embedding or references by access pattern

Joins are a design trade-off, not evidence that you are using MongoDB incorrectly. Embedding stores related records inside the parent; referencing stores them separately and connects them through identifiers.

Prefer embedding when	Prefer references and selective lookups when
Related data is usually read and updated together.	Related records have independent lifecycles or update patterns.
The child collection is small and bounded per parent.	The number of children can grow substantially or without bounds.
Keeping data together simplifies the application.	Duplicating shared data would create costly consistency work.

Evaluate read patterns, update frequency, duplication, and growth together. For posts with an expanding comment history, references plus bounded reads are often a better starting point than embedding every comment. See MongoDB schema-design questions for that broader decision.

Common Mistakes

Joining on titles: use stable references and consistent BSON types.
Assuming an index solves fan-out: test the posts with the most comments, not just the average post.
Moving every filter before the join: distinguish source filters from conditions that need joined data.
Unwinding automatically: keep the array when that is the response shape you need; otherwise decide explicitly whether unmatched posts should survive.

FAQ

What does MongoDB $lookup return?

MongoDB $lookup adds an array of matching foreign documents to each input document. With no matches, the array is empty; the stage does not permanently merge collections.

Which index improves a MongoDB $lookup?

For a basic equality lookup, start with an index on the foreignField in the foreign collection. In the Posts/Comments example, that is comments.postId; verify eligibility and actual use with executionStats.

Can $lookup join a sharded collection?

Yes. Sharded foreign collections are supported starting in MongoDB 5.1, and lookups inside transactions targeting sharded collections are supported starting in 8.0. Check the documentation for your server version if an operation fails.

How do I filter documents inside a $lookup?

Add a foreign pipeline containing a $match stage. When its conditions depend on the current input document, capture local values with let and reference them inside $expr using the double-dollar variable syntax.

When should I use $unwind after $lookup?

Use $unwind when downstream processing needs one document per joined array element. Set preserveNullAndEmptyArrays to true to retain unmatched input documents; the default false drops empty lookup arrays.