$lookup Examples | MongoDB

Sam Erickson
Updated 12 hours ago
0

Key Takeaways

$lookup joins posts to matching comments and returns those comments as an array.
Use localField and foreignField for equality matches; use let and pipeline when conditions depend on the current post.
For the title-based join, index comments.postTitle and verify the actual execution plan.
Standard MongoDB $lookup joins collections in the same database. Atlas Data Federation has separate cross-database capabilities.

MongoDB’s $lookup aggregation stage joins documents from two collections in the same database. It performs a left outer join: each input document remains in the result, with an array containing its matching foreign documents—or an empty array when nothing matches.

This MongoDB $lookup example starts with posts and comments, then adds a condition: return only comments with more likes than their post. If you need a refresher on stages, start with our MongoDB aggregation pipeline guide.

Server Compatibility and Database Scope

Use mongosh with a MongoDB 8.0-or-later server for this walkthrough. The examples use longstanding syntax, not features introduced specifically in 8.0. Foreign sharded collections have been supported since MongoDB 5.1; MongoDB 8.0 added support for using $lookup inside a transaction while targeting a sharded collection. See the MongoDB 8.0 $lookup reference for those version-specific details.

Create the Posts and Comments Collections

In a fresh scratch database, paste the following into mongosh. Run the setup once, then use the same shell session for the remaining examples.

db = db.getSiblingDB("stackchief_lookup_demo");

db.posts.insertMany([
  { _id: 1, title: "my first post", author: "Jim", likes: 5 },
  { _id: 2, title: "my second post", author: "Jim", likes: 2 },
  { _id: 3, title: "hello world", author: "Joe", likes: 3 }
]);

db.comments.insertMany([
  { _id: 101, postTitle: "my first post", comment: "great read", likes: 3 },
  { _id: 102, postTitle: "my second post", comment: "good info", likes: 0 },
  { _id: 103, postTitle: "my second post", comment: "i liked this post", likes: 12 },
  { _id: 104, postTitle: "hello world", comment: "not my favorite", likes: 8 },
  { _id: 105, postTitle: "my last post", comment: null, likes: 0 }
]);

The relationship is posts.title = comments.postTitle. The comment for "my last post" deliberately has no corresponding post.

$lookup Example: Basic Equality Match

Start with the equality form. Save the pipeline so you can explain the same query later:

const equalityPipeline = [
  {
    $lookup: {
      from: "comments",
      localField: "title",
      foreignField: "postTitle",
      as: "comments"
    }
  },
  { $sort: { _id: 1 } }
];

db.posts.aggregate(equalityPipeline);

from identifies the foreign collection: comments.
localField identifies the field on the input post: title.
foreignField identifies the field on each foreign comment: postTitle.
as names the result array: comments.

The first post’s result looks like this. This is one document from the result, not the complete output:

{
  "_id": 1,
  "title": "my first post",
  "author": "Jim",
  "likes": 5,
  "comments": [
    {
      "_id": 101,
      "postTitle": "my first post",
      "comment": "great read",
      "likes": 3
    }
  ]
}

Input post                    $lookup output
{ title: "my first post" } --> { title: "my first post",
                                comments: [matching comment] }

The complete query returns three posts: "my first post" has one comment, "my second post" has two, and "hello world" has one. The orphan comment for "my last post" appears nowhere because the aggregation starts from posts; a foreign comment cannot create a new input post.

The array exists only in the aggregation result. This query does not embed or persist comments in the stored post documents; ordinary aggregation reads do not modify source collections unless you explicitly add a write stage such as $merge or $out. See MongoDB’s aggregation pipeline behavior.

$lookup Example: Pipeline Use with Conditions

Now require both a matching title and a comment whose likes exceed the post’s likes. A foreign pipeline lets you express that second condition:

const conditionalPipeline = [
  {
    $lookup: {
      from: "comments",
      let: {
        post_likes: "$likes",
        post_title: "$title"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$postTitle", "$$post_title"] },
                { $gt: ["$likes", "$$post_likes"] }
              ]
            }
          }
        }
      ],
      as: "comments"
    }
  },
  { $sort: { _id: 1 } }
];

db.posts.aggregate(conditionalPipeline);

Understand the Variable Scope

In let, "$likes" and "$title" read fields from the current input post.
Inside the foreign pipeline, "$likes" and "$postTitle" read fields from the current comment.
"$$post_likes" and "$$post_title" reference the variables passed from the post. The double dollar sign distinguishes variables from document fields.

$expr lets $match evaluate aggregation expressions, including these variable-based comparisons. This is a correlated lookup: its condition depends on the current input post. The $expr reference explains variable access and index restrictions.

Read the Complete Conditional Result

Here are all three output documents:

[
  {
    "_id": 1,
    "title": "my first post",
    "author": "Jim",
    "likes": 5,
    "comments": []
  },
  {
    "_id": 2,
    "title": "my second post",
    "author": "Jim",
    "likes": 2,
    "comments": [
      {
        "_id": 103,
        "postTitle": "my second post",
        "comment": "i liked this post",
        "likes": 12
      }
    ]
  },
  {
    "_id": 3,
    "title": "hello world",
    "author": "Joe",
    "likes": 3,
    "comments": [
      {
        "_id": 104,
        "postTitle": "hello world",
        "comment": "not my favorite",
        "likes": 8
      }
    ]
  }
]

The first post remains, but its array is empty because its comment has 3 likes and the post has 5. The other posts retain their comments with 12 and 8 likes, respectively. Filtering inside the foreign pipeline filters comments, not the outer posts.

Prefer Stable Post Identifiers to Titles

Titles make the relationship readable, but they are fragile join keys. Renaming a post can break its matches, and two posts with the same title can receive each other’s comments. In application data, store the post’s stable identifier in a field such as comments.postId.

The fixture already has numeric post identifiers. This separate collection demonstrates the reference pattern without changing the previous examples:

db.commentsById.insertMany([
  { _id: 201, postId: 1, comment: "great read", likes: 3 },
  { _id: 202, postId: 2, comment: "i liked this post", likes: 12 }
]);

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

db.posts.aggregate([
  {
    $lookup: {
      from: "commentsById",
      localField: "_id",
      foreignField: "postId",
      as: "comments"
    }
  },
  { $sort: { _id: 1 } }
]);

Here, the first two posts each have one matching comment, while "hello world" has comments: []. For an ObjectId-based schema, use the post’s ObjectId as the comment’s reference value. Choose the relationship around your access patterns; our MongoDB schema design tutorial covers that broader decision.

Use $unwind for One Document per Comment

If downstream stages need individual comments instead of an array, append $unwind. Reuse the conditional pipeline to make unmatched-post behavior visible:

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

$unwind produces one output document per array element, replacing the array with that element. With preserveNullAndEmptyArrays: true, the first post survives even though its array is empty; its empty comments field is omitted. The default is false, which drops documents whose target field is missing, null, or an empty array.

Improve MongoDB $lookup Performance

Index the Foreign Join Field

For the equality example, the useful foreign-side index is on comments.postTitle:

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

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

Equality lookups generally benefit from an index on foreignField. Indexing posts.title is not a substitute: that index may help a filter selecting input posts, but it does not index the foreign comments being searched. Our MongoDB create-index tutorial explains index creation.

explain("executionStats") executes the winning plan and reports execution statistics. Inspect the foreign-side access strategy and, where exposed, indexesUsed, collectionScans, totalDocsExamined, and totalKeysExamined. Output structure varies by server version and execution engine; consult the explain-results reference rather than expecting one fixed plan layout.

Repeat the explain call with conditionalPipeline. This tiny fixture demonstrates correctness, not production speed; measure with representative collection sizes and comments-per-post counts.

Do Not Assume Every Pipeline Predicate Uses an Index

Inside a lookup’s $expr, comparisons such as $eq and $gt can use foreign indexes when comparing a field with a constant; a let operand must resolve to a constant for that lookup invocation. Comparing two foreign fields does not qualify. Indexes are not used for these comparisons when the let operand is empty or missing, and multikey, partial, and sparse indexes are excluded.

For the conditional example, a compound index on { postTitle: 1, likes: 1 } is a candidate to measure, not a guarantee of a particular plan. Start with the actual predicate and check explain output on your server version.

Control Work and Result Size

Filter input posts before $lookup when the request needs only a subset.
Return only needed comment fields from the foreign pipeline. See our MongoDB projections tutorial for field-selection fundamentals.
For posts with many comments, consider a bounded, explicitly sorted comment preview or a separate paginated query instead of returning every comment.

Each returned BSON document has a 16 MiB size limit, including its joined array; an oversized result document causes an error. This is a per-document limit, not a limit on the total cursor results. MongoDB also limits a single aggregation pipeline to 1,000 stages. See the aggregation pipeline limits.

A foreign $lookup.pipeline cannot contain $out or $merge. Neither an index nor disk spilling removes the returned-document size limit.

Common Mistakes

Expecting the result array to be stored: these lookup queries reshape results, not the underlying posts.
Using titles as permanent references: prefer stable identifiers to avoid collisions and broken relationships after edits.
Ignoring missing join keys: the equality form treats missing join fields as null for matching, so absent keys can produce unexpected matches. Validate required reference fields.

FAQ

How do you join two collections in MongoDB with $lookup?

Run aggregate() on the input collection and add a $lookup stage specifying from, localField, foreignField, and as. Matching foreign documents appear in the array named by as on each input document.

What is the difference between localField/foreignField and pipeline in $lookup?

localField and foreignField express an equality match between collections. A foreign pipeline adds filtering and other processing; let variables carry values from the current input document into that pipeline. You can also combine an equality match with a foreign pipeline.

Which field should be indexed for a MongoDB $lookup?

For an equality lookup, start with an index on the foreign collection’s foreignField—for this example, comments.postTitle. Pipeline comparisons have additional index restrictions, so verify their access strategy with explain("executionStats").

Does $lookup return an array when there are no matches?

Yes. The field named by as is an empty array, and the input document remains in the result. A later $unwind drops empty arrays by default unless preserveNullAndEmptyArrays is true.

Can $lookup join collections in different databases?

Standard MongoDB server $lookup joins collections in the same database; MongoDB 8.0 did not add general cross-database joins. Atlas Data Federation supports cross-database $lookup through a federated database instance with its own syntax and restrictions.