$lookup, $unwind and $graphLookup

conceptThe concept

{ $lookup: { from: "customers", localField: "customerId", foreignField: "_id", as: "customer" } }

adds an array field customer with the matching documents. Follow with $unwind: "$customer" (or $first) for a one-to-one join. Since 5.0 you can combine localField/foreignField with a sub-pipeline to filter or project the joined documents; the let + pipeline form allows arbitrary correlated conditions.

$graphLookup recursively follows a reference (connectFromField → connectToField) to walk org charts, category trees or social graphs, with maxDepth and depthField.

whyWhy it matters

Joins are fine for reports, admin screens and occasional lookups. A join on every request of a high-traffic endpoint usually signals that the data should be embedded or that a few fields should be duplicated (the extended reference pattern).

Use caseReal-world scenario

An admin "order details" screen joins the order with its customer and the products' current stock. The storefront order history page does not join at all: each order already carries the item names and prices at the time of purchase.

Try itExamples

Run and edit these in the interactive lesson.

db.orders.aggregate([
  { $match: { status: "returned" } },
  { $limit: 3 },
  { $lookup: { from: "customers", localField: "customerId", foreignField: "_id", as: "customer" } },
  { $unwind: "$customer" },
  { $project: { _id: 0, orderNo: 1, total: 1, "customer.name": 1, "customer.tier": 1 } }
])

db.products.aggregate([
  { $match: { category: "electronics" } },
  { $limit: 3 },
  { $lookup: {
      from: "reviews", localField: "_id", foreignField: "productId", as: "topReviews",
      pipeline: [{ $match: { rating: { $gte: 4 } } }, { $sort: { helpfulVotes: -1 } }, { $limit: 2 }, { $project: { _id: 0, rating: 1, title: 1 } }] } },
  { $project: { name: 1, topReviews: 1 } }
])

db.customers.aggregate([
  { $lookup: { from: "orders", localField: "_id", foreignField: "customerId", as: "o", pipeline: [{ $limit: 1 }, { $project: { _id: 1 } }] } },
  { $match: { o: { $size: 0 } } },
  { $count: "customersWithoutOrders" }
])

db.employees.aggregate([
  { $match: { _id: 30 } },
  { $graphLookup: { from: "employees", startWith: "$managerId", connectFromField: "managerId", connectToField: "_id", as: "chain", depthField: "depth" } },
  { $project: { name: 1, title: 1, chain: { $map: { input: { $sortArray: { input: "$chain", sortBy: { depth: 1 } } }, as: "m", in: { $concat: ["$$m.name", " (", "$$m.title", ")"] } } } } }
])

internalsUnder the hood

For every input document, $lookup runs a query against from. Without an index on foreignField each lookup is a collection scan: 5,000 orders joining a 500-document collection is 2.5 million document examinations. With an index it is 5,000 index seeks. Since 6.0 SBE can execute some lookups as hash joins. In sharded clusters, from may be sharded since 5.1.

Avoid / preferDo this, not that

db.products.aggregate([
  { $lookup: { from: "reviews", localField: "_id", foreignField: "productId", as: "reviews" } },
  { $project: { name: 1, n: { $size: "$reviews" } } }
])

db.products.aggregate([
  { $lookup: { from: "reviews", localField: "_id", foreignField: "productId",
      pipeline: [{ $count: "n" }], as: "stats" } },
  { $project: { name: 1, n: { $ifNull: [{ $first: "$stats.n" }, 0] } } }
])

db.customers.find().forEach(c => {
  c.orders = db.orders.find({ customerId: c._id }).toArray()
})

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

PitfallsCommon mistakes

• No index on foreignField.
• Using $lookup on the hot path of a high-traffic API instead of embedding or duplicating the few fields needed.
• Forgetting that $unwind drops documents with no match unless preserveNullAndEmptyArrays: true.
• Joining a huge array into each document and blowing the 16 MB limit; filter and limit inside the sub-pipeline.

LimitsWhen not to use it

If nearly every read needs the joined data, a join on every request is the wrong design: embed it, or copy the few fields you need (extended reference). $graphLookup is limited to 100 MB of memory and is not for deep traversals of huge graphs.

SecuritySecurity notes

A $lookup needs the find privilege on the joined collection. Check that reporting roles cannot join into collections that hold credentials or personal data they should not see.

ChallengeTry it yourself

db.orders.aggregate([
  { $match: { status: "delivered" } },
  { $group: { _id: "$customerId", revenue: { $sum: "$total" } } },
  { $sort: { revenue: -1 } },
  { $limit: 5 },
  { $lookup: { from: "customers", localField: "_id", foreignField: "_id", as: "c" } },
  { $unwind: "$c" },
  { $project: { name: "$c.name", tier: "$c.tier", revenue: { $round: ["$revenue", 2] } } }
])

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.