MongoDB $lookup, $facet and advanced stages

$lookup, $facet, $bucket, $replaceRoot, $graphLookup, top-N per group. · 11 practice questions · Cheat sheet ↓

$lookup is how you join two collections in MongoDB: it matches localField in the current documents to foreignField in another collection and attaches the matches as an array, always an array, even for a single match. $unwind or $arrayElemAt turns that into a plain embedded document. $facet runs several sub-pipelines over the same input in one pass and returns them as one document, ideal for "results plus counts". $bucket groups numbers into ranges, $replaceRoot promotes a subdocument to the top level, and $graphLookup follows references recursively, such as a manager chain.

These stages combine: $lookup then $unwind then $group is the join-and-aggregate pattern, and $sort, $group with $push and $slice give top-N per group. The questions here are the ones that come up in real reporting work.

MongoDB $lookup and $facet practice questions

Join orders to customers: attach the matching customer document as an array field called customer. medium orders
Produce one row per order with _id, the customer's name as customerName, and total – sorted by _id ascending. hard orders
In one pipeline produce a document with two keys: byBorough (a $sortByCount on borough) and topSeats (the 2 restaurants with the most seats, projected to name and seats only, no _id). hard restaurants
Bucket movies by imdb.rating into the ranges [0,6), [6,7), [7,8), [8,10) with a count in each and the titles pushed into titles. Use other as the default bucket. hard movies
Use a single stage to rank boroughs by restaurant count, highest first. medium restaurants
For each borough, list the top 2 restaurants by seats. Output _id (the borough) and top – an array of the whole documents, sliced to 2. Sort boroughs by name ascending. hard restaurants
Promote each Manhattan restaurant's address subdocument to be the top-level document. Sort by building ascending. medium restaurants
For employee E6, walk the full management chain upward into a field called chain. hard employees
Which single restaurant has the best (lowest) average grade score? Output name and avgScore, one row. hard restaurants
Compute monthly revenue: group delivered orders by the YYYY-MM string of their date as _id, summing total into revenue, sorted by month. hard orders
Join order line items to products and report revenue per product name: unwind items, look up the product by sku, and sum qty * price into revenue. Output _id as the product name, sorted by revenue descending. hard orders

Each question is answered in a live mongo-shell editor on this page; the result is checked against a reference solution.

$lookup, $facet, $bucket and $graphLookup cheat sheet

$lookup – the left outer join

{ $lookup: {
    from: "customers",
    localField: "customerId",
    foreignField: "_id",
    as: "customer"          // always an ARRAY, even for one match
} },
{ $unwind: "$customer" }     // flatten it back to an object

$bucket – histogram

{ $bucket: {
    groupBy: "$imdb.rating",
    boundaries: [0, 6, 7, 8, 10],
    default: "unrated",
    output: { count: { $sum: 1 }, titles: { $push: "$title" } }
} }

$facet – several pipelines, one pass

{ $facet: {
    byBorough: [ { $sortByCount: "$borough" } ],
    priciest:  [ { $sort: { price: -1 } }, { $limit: 3 } ]
} }

Returns one document with an array per key. Great for dashboards.

$replaceRoot – promote a subdocument

{ $replaceRoot: { newRoot: "$address" } }

Top N per group

The pattern worth memorising:

{ $sort:  { borough: 1, seats: -1 } },
{ $group: { _id: "$borough", top: { $push: "$$ROOT" } } },
{ $project: { top: { $slice: ["$top", 3] } } }

Sort first, group into an array, then $slice. $$ROOT is the whole document.

$graphLookup – recursive

{ $graphLookup: {
    from: "employees",
    startWith: "$reportsTo",
    connectFromField: "reportsTo",
    connectToField: "_id",
    as: "chain"
} }

Walks the chain until it runs out. Use for org charts, categories, threads.

Common questions

How do I join two collections in MongoDB?

With $lookup: { $lookup: { from: "customers", localField: "customerId", foreignField: "_id", as: "customer" } }. The matches arrive as the customer array. Follow with { $unwind: "$customer" } when you expect exactly one match per order.

Why is $lookup always an array?

Because a foreign field may match many documents, $lookup returns every match in an array, even when there is one or none. Flatten it with $unwind, or pick the first with { $arrayElemAt: ["$customer", 0] } in a $project or $addFields.

How do I get the top N per group?

Sort, group with $push, then slice: sort by the ranking field, $group by the key pushing documents into an array, and $project with { $slice: ["$items", 3] }. MongoDB 5.2 and later also offer $topN accumulators that do it in one stage.

Continue:← $unwind & Array Pipelines

MongoDB topics:Finding & Projection Comparison Operators Logical Operators Element & Type Pattern Matching Sort, Limit & Skip Array Queries Embedded Documents Aggregation Basics Grouping & Accumulators $unwind & Array Pipelines Full cheat sheet