MongoDB $lookup Stage

Example 1 — Sample Data

Imagine three orders pointing at two customers (plus one orphan customerId). Customers include an active flag for the pipeline join in Example 4.

db.customers.drop()
db.orders.drop()

db.customers.insertMany([
  { _id: 1, name: "Acme", city: "Austin", active: true },
  { _id: 2, name: "Beta", city: "Boston", active: false }
])

db.orders.insertMany([
  { _id: "A100", customerId: 1, total: 40 },
  { _id: "B200", customerId: 2, total: 15 },
  { _id: "C300", customerId: 9, total: 99 }
])

How the Data Is Stored in MongoDB

After the inserts, MongoDB keeps two collections. Run find() on each to see what you stored:

db.customers.find()
db.orders.find()

// customers
[
  { _id: 1, name: "Acme", city: "Austin", active: true },
  { _id: 2, name: "Beta", city: "Boston", active: false }
]

// orders
[
  { _id: "A100", customerId: 1, total: 40 },
  { _id: "B200", customerId: 2, total: 15 },
  { _id: "C300", customerId: 9, total: 99 }
]

Read the join keys like this:

Join key (equality $lookup):

  orders.customerId  ──matches──►  customers._id

  A100  customerId 1  →  Acme   (_id 1)
  B200  customerId 2  →  Beta   (_id 2)
  C300  customerId 9  →  (no customer _id 9)

How It Works

1. Reset collections. drop() clears old tutorial data from both sides.
2. Insert customers. Each row has _id, name, city, and active.
3. Insert orders. Each order stores customerId pointing at a customer _id.
4. find() confirms shape. Same documents you inserted—ready for $lookup in Examples 2–4.

Example 2 — Equality Join

Goal: join each order from Example 1 to its customer using matching keys.

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

{
  _id: "A100",
  customerId: 1,
  total: 40,
  customerInfo: [ { _id: 1, name: "Acme", city: "Austin", active: true } ]
}
// C300 has customerInfo: [] — order is still returned

How It Works

1. Same data as Example 1. Orders hold customerId; customers use _id.
2. Equality join. MongoDB matches customerId to _id.
3. Array result. Matches land in customerInfo—even if there is only one.
4. Left join. Order C300 stays with an empty array (no customer 9).

Example 3 — Flatten with $unwind

Goal: turn the joined array into a single nested object for API-friendly output. Keep unmatched orders with preserveNullAndEmptyArrays. Uses the orders and customers from Example 1.

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      localField: "customerId",
      foreignField: "_id",
      as: "customerInfo"
    }
  },
  {
    $unwind: {
      path: "$customerInfo",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $project: {
      _id: 1,
      total: 1,
      customer: "$customerInfo.name",
      city: "$customerInfo.city"
    }
  }
])

{ _id: "A100", total: 40, customer: "Acme", city: "Austin" }
{ _id: "B200", total: 15, customer: "Beta", city: "Boston" }
{ _id: "C300", total: 99, customer: null, city: null }

How It Works

1. Join first. Same equality $lookup as Example 2.
2. Unwind. Array → single object (or missing field when empty).
3. Preserve empties. Without preserve, C300 would disappear.
4. Project. Lift name / city into flat fields for responses.

Example 4 — Pipeline + let

Goal: join only active customers and return a slim projection inside the join. Customer active flags come from Example 1 (Acme active, Beta inactive).

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      let: { cid: "$customerId" },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$_id", "$$cid"] },
                { $eq: ["$active", true] }
              ]
            }
          }
        },
        { $project: { _id: 0, name: 1, city: 1 } }
      ],
      as: "customerInfo"
    }
  }
])

A100 → customerInfo: [{ name: "Acme", city: "Austin" }]
B200 → customerInfo: []   // Beta inactive
C300 → customerInfo: []   // no matching _id

$$cid is the let variable from each order

How It Works

1. let. cid captures each order’s customerId.
2. $expr. Compare foreign _id to $$cid (double $ = let var).
3. Extra filter. Only active: true customers join.
4. Project early. Sub-pipeline returns slim docs—less data over the wire.