$lookup: joins in MongoDB for MongoDB & NoSQL developers

Introduction

MongoDB's $lookup stage is your answer when you need to combine data across collections—think of it as SQL's JOIN statement, but with the flexibility that document databases demand. In this article, we'll explore how to use $lookup effectively, when to reach for it, and how to spot when your data model might need restructuring instead.

Left outer join between collections

At its core, $lookup performs a left outer join. Every document in your source collection is preserved, and matching documents from the foreign collection are attached as an array. If there's no match, you get an empty array rather than a null or lost record.

Let's say we're building an e-commerce platform where orders reference customers. Our orders collection holds order data, and we want to enrich each order with customer details from the customers collection.

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      localField: "customer_id",
      foreignField: "_id",
      as: "customer"
    }
  },
  { $limit: 10 }
])

Here, localField is the field in the current document (orders), and foreignField is the field in the target collection (customers). The as parameter names the new array that holds results. If an order has no matching customer (perhaps the customer was deleted), the customer array will be empty.

The result looks like this:

{
  _id: ObjectId("..."),
  customer_id: ObjectId("..."),
  order_date: ISODate("2024-01-15T10:30:00Z"),
  total: 249.99,
  customer: [
    {
      _id: ObjectId("..."),
      name: "Alice Chen",
      email: "alice@example.com",
      tier: "gold"
    }
  ]
}

Because the matched customer is wrapped in an array, you might want to unwrap it in your application or use $unwind in the pipeline. If you're confident every order has a customer, $unwind is safe; if orders can exist without customers, you'll need to handle the empty array case.

Pipeline lookups for filtered joins

Basic $lookup grabs all documents from the foreign collection that match. For large collections or when you need filtering, let and pipeline give you finer control. This syntax lets you run an aggregation pipeline within the lookup itself.

Imagine we're joining orders with shipments, but we only want recent shipments and we want them sorted by date. Here's the approach:

db.orders.aggregate([
  {
    $lookup: {
      from: "shipments",
      let: { order_id: "$_id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$order_id", "$$order_id"] } } },
        { $match: { created_at: { $gte: new Date("2024-01-01") } } },
        { $sort: { created_at: -1 } },
        { $limit: 5 }
      ],
      as: "recent_shipments"
    }
  }
])

The let clause creates variables (prefixed with $$ inside the pipeline) from the current document. Inside the pipeline, we can match, filter, sort, and limit just as we would in a standalone aggregation. This is powerful because you're not bringing back the entire foreign collection into memory—you filter it before inclusion.

This approach shines in reporting queries. A trading platform might want to join orders with only the most recent position updates; a ride-hailing service might join trips with the last three location pings. The pipeline approach keeps your result sets lean and your queries performant.

Index the foreign field

Here's a detail many teams overlook: $lookup performance depends heavily on whether the foreignField is indexed. Without an index, MongoDB must scan every document in the foreign collection for each lookup operation—an expensive O(n) operation repeated across your batch.

In our customers and orders example, ensure you have an index on customers._id. Since _id is indexed by default, you're safe there. But if your foreign key is a different field, create the index explicitly:

db.customers.createIndex({ customer_code: 1 })

And then use that field in your lookup:

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      localField: "customer_code",
      foreignField: "customer_code",
      as: "customer_details"
    }
  }
])

In our experience, a missing index on a heavily used foreign key can turn a sub-second query into something that times out. Always check your execution stats and verify indexes exist before moving a lookup-heavy query to production.

Frequent lookups may signal a modeling issue

If you find yourself writing $lookup in every query, or if your aggregation pipeline chains three or four lookups together, it's worth stepping back and asking: should this data be denormalized?

NoSQL databases trade storage for query speed. Denormalization is intentional and often correct. A ride-hailing app might embed the driver's name and rating directly in the trip document rather than looking them up each time. A SaaS product might store the account owner's email and role in every project document to avoid constant customer table joins.

Consider a CRM system where every contact query joins with account, user assignments, and recent activity. If this is your common case, you might be better served denormalizing at write time:

• Store the account name and account_id on every contact, not just account_id
• Embed assigned user names on contact records (update them when users change names)
• Periodically update a "last_activity_summary" field on contacts

The tradeoff is write complexity and storage. When you update a user's name, you now need to touch all contact documents with that assignment. But if reads vastly outnumber writes, this is a win. Use $lookup for occasional enrichment or complex reporting; use denormalization for your hot path queries.

Common mistakes

• Forgetting to index the foreignField: Your lookup works but slows down under load. Always profile with real data volume.
• Not unwrapping single results: The as field is always an array. If you expect one customer per order, you may need to unwind and handle nulls, or unwrap in your application layer.
• Joining too early in the pipeline: Run expensive filters and matches before the lookup when possible. If you're joining 1 million documents then filtering to 100, you're wasting work.
• Overusing lookups for denormalized data: If you're joining the same collections in 80% of your queries, denormalization is probably the better choice.
• Ignoring pipeline execution limits: Lookups with complex pipelines and no limits can balloon memory usage. Always set $limit or $match stages to constrain results.

Key takeaways

• $lookup performs left outer joins, preserving all documents from the source collection and attaching matches from the foreign collection as arrays.
• Use the let and pipeline syntax for filtered, sorted, and limited joins that reduce memory overhead.
• Always index the foreignField to avoid expensive collection scans during each lookup.
• Frequent, multi-stage lookups often signal that denormalization would be a better fit for your access patterns.
• Order your pipeline stages carefully: filter and match early, then join, to minimize the number of documents being enriched.