$lookup & Joins

$lookup is MongoDB's aggregation stage for performing left outer joins between collections. It lets you enrich documents with related data from another collection — MongoDB's answer to SQL JOIN.

Basic $lookup

Join orders to users

db.orders.aggregate([
  {
    $lookup: {
      from:         'users',       // the other collection
      localField:   'userId',      // field in orders
      foreignField: '_id',         // field in users
      as:           'user',        // output array field
    },
  },
])
// Each order document now has a 'user' array field

$unwind After $lookup

$lookup always produces an array — even for 1:1 joins. Use $unwind to flatten it into a single embedded document.

Flatten the joined array

db.orders.aggregate([
  {
    $lookup: {
      from: 'users', localField: 'userId',
      foreignField: '_id', as: 'user',
    },
  },
  // Flatten array to single object; preserve orders with no user
  { $unwind: { path: '$user', preserveNullAndEmptyArrays: true } },
  {
    $project: {
      orderDate: 1,
      total: 1,
      'user.name': 1,
      'user.email': 1,
    },
  },
])

Pipeline $lookup (Advanced)

The pipeline form lets you filter and transform joined documents before returning them — more efficient than joining everything then filtering.

$lookup with pipeline

db.orders.aggregate([
  {
    $lookup: {
      from: 'products',
      let:  { productIds: '$items.productId' },   // pass local vars
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $in: ['$_id', '$$productIds'] },
                { $eq: ['$active', true] },         // filter in sub-pipeline
              ],
            },
          },
        },
        { $project: { name: 1, price: 1 } },        // shape joined docs
      ],
      as: 'products',
    },
  },
])

Multiple $lookup Stages

Chain multiple joins

db.orders.aggregate([
  // 1. Join to users
  {
    $lookup: {
      from: 'users', localField: 'userId',
      foreignField: '_id', as: 'user',
    },
  },
  { $unwind: '$user' },

  // 2. Join to addresses (on the user)
  {
    $lookup: {
      from: 'addresses', localField: 'user.addressId',
      foreignField: '_id', as: 'address',
    },
  },
  { $unwind: { path: '$address', preserveNullAndEmptyArrays: true } },

  {
    $project: {
      orderId: '$_id',
      total: 1,
      customerName: '$user.name',
      city: '$address.city',
    },
  },
])

$graphLookup — Recursive Joins

$graphLookup performs recursive lookups for hierarchical data — org charts, comment threads, category trees.

Find all subordinates of a manager

db.employees.aggregate([
  { $match: { name: 'Alice' } },
  {
    $graphLookup: {
      from:             'employees',
      startWith:        '$_id',
      connectFromField: '_id',
      connectToField:   'managerId',
      as:               'team',
      maxDepth:         5,           // stop at 5 levels deep
    },
  },
])

$lookup vs SQL JOIN

Concept	SQL JOIN	$lookup
Join type	INNER / LEFT / RIGHT	Left outer join only
Index use	Both sides	Local collection (pipeline form for remote)
Cross-DB join	No	No — same database only
Multiple joins	Multiple JOINs	Multiple $lookup stages
Result shape	Flat row	Nested array (flatten with $unwind)

Performance Tips

$lookup performance is critical to get right:

Tip	Why
Place $match before $lookup	Reduce docs entering the join
Use pipeline form with $match first	Allows index use on joined collection
Project only needed fields after $unwind	Reduce memory and network
Consider embedding instead	One read vs a join — 10x+ faster

MongoDB design preference

In MongoDB, the preferred alternative to $lookup is embedding related data directly in the document. Use $lookup for relationships where the "many" side is large or changes independently of the parent.

Warning

$lookup on a large unindexed collection is very slow. In the pipeline form, place $match as the first sub-pipeline stage to enable index use on the foreign collection.

Tip

Heavy use of $lookup (more than 3 stages in a pipeline) is usually a sign that the data model needs redesign — consider embedding the most frequently accessed fields.