MongoDB Lookup and Joins

Data often lives in more than one collection. Customers sit in one collection, and their orders sit in another. A join combines the two so that each order shows the customer details beside it. MongoDB performs joins with the $lookup stage of the aggregation pipeline.

The Idea Behind a Join

Imagine a school with two registers. One register lists students with ID numbers. The other register lists library books with the ID of the student who borrowed each one. A teacher who wants to see the student name next to each borrowed book matches the ID in both registers. That matching step is a join.

Sample Data

db.customers.insertMany([
  { _id: 1, name: "Asha",  city: "Pune" },
  { _id: 2, name: "Rohit", city: "Delhi" },
  { _id: 3, name: "Meera", city: "Jaipur" }
])

db.orders.insertMany([
  { _id: 101, customerId: 1, item: "Pen",      qty: 10 },
  { _id: 102, customerId: 1, item: "Notebook", qty: 3 },
  { _id: 103, customerId: 2, item: "Ruler",    qty: 5 },
  { _id: 104, customerId: 9, item: "Marker",   qty: 2 }
])

Join Diagram

orders                          customers
+-----------------------+       +------------------+
| _id: 101              |       | _id: 1           |
| customerId: 1  -------------->| name: "Asha"     |
| item: "Pen"           |       +------------------+
+-----------------------+
| _id: 103              |       +------------------+
| customerId: 2  -------------->| _id: 2           |
| item: "Ruler"         |       | name: "Rohit"    |
+-----------------------+       +------------------+
| _id: 104              |
| customerId: 9  ---- no match found

When Joins Make Sense

MongoDB encourages storing related data together in one document, so many applications need few joins. Joins still earn their place in reports, admin screens, and data that changes often. A customer address that lives in one collection stays correct everywhere, because each order reads the latest address at query time. Copies of the same address inside every order would need an update in many places.

Choose a join when the related data changes often or grows large. Choose embedding when applications always read the two pieces together and the embedded part stays small.

Basic $lookup

The $lookup stage takes four settings:

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

Result

{
  _id: 101,
  customerId: 1,
  item: "Pen",
  qty: 10,
  customerInfo: [ { _id: 1, name: "Asha", city: "Pune" } ]
}

MongoDB adds customerInfo as an array, even when only one customer matches. Order 104 receives an empty array because no customer has the ID 9. This behavior matches a left outer join in relational databases.

Flatten the Result with $unwind

Most people want a single object rather than a one-item array. The $unwind stage turns each array element into its own document:

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

Order 104 disappears from the output because its array is empty. Keep such orders by adding the option preserveNullAndEmptyArrays:

{ $unwind: { path: "$customer", preserveNullAndEmptyArrays: true } }

Shape the Output with $project

db.orders.aggregate([
  { $lookup: { from: "customers", localField: "customerId", foreignField: "_id", as: "customer" } },
  { $unwind: "$customer" },
  { $project: { _id: 0, item: 1, qty: 1, buyer: "$customer.name", city: "$customer.city" } }
])

The final output holds clean documents such as { item: "Pen", qty: 10, buyer: "Asha", city: "Pune" }.

Pipeline Flow

orders --> [ $lookup ] --> [ $unwind ] --> [ $project ] --> clean result
           attach        flatten         pick and
           customer      the array       rename fields

Advanced $lookup with a Pipeline

The basic form matches one field against one field. The pipeline form adds extra conditions to the join. The let setting passes values from the current document into the inner pipeline:

db.customers.aggregate([
  {
    $lookup: {
      from: "orders",
      let: { custId: "$_id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$customerId", "$$custId"] }, qty: { $gte: 5 } } }
      ],
      as: "bigOrders"
    }
  }
])

Each customer receives only the orders with a quantity of five or more. Variables created with let use a double dollar sign inside the inner pipeline.

Performance Tips

• Create an index on the foreignField. An index on customerId in the orders collection speeds up lookups from customers.
• Place $match before $lookup so that fewer documents enter the join.
• Embed data that applications always read together. Joins cost more than a single document read.
• Avoid joining very large collections on every request. Store a copy of frequently used fields instead.

Summary

The $lookup stage joins two collections by matching a field in each. The result arrives as an array that $unwind can flatten and $project can shape. The pipeline form supports extra conditions through let and $expr. Indexes on the foreign field and early filtering keep joins fast. Good schema design reduces the number of joins an application needs in the first place.