Week 5: Advanced Aggregation & Data Transformation

1. $lookup: Joining Collections

Week 3 spent a whole module on choosing between embedding and referencing. $lookup is the tool that makes referencing usable in a query — it performs a left outer join, pulling matching documents from a second collection into the current pipeline, the same way a SQL JOIN would.

two referenced collections

// customers
{ _id: ObjectId("c1"), name: "Priya Nair", tier: "gold" }

// orders
{ _id: ObjectId("o1"), customerId: ObjectId("c1"), total: 89.50, status: "shipped" }
{ _id: ObjectId("o2"), customerId: ObjectId("c1"), total: 42.00, status: "shipped" }

joining orders to their customer

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",       // the collection to join against
      localField: "customerId",// field on THIS (orders) document
      foreignField: "_id",     // field on the FOREIGN (customers) document
      as: "customer"           // name of the new array field holding matches
    }
  }
])

The result adds a customer field to every order — but it's always an array, even when only one document matches, because $lookup has no way of knowing in advance that _id is unique. Two common next steps handle that: $unwind (covered next) to flatten a one-to-one join down to a plain object, or { $arrayElemAt: ["$customer", 0] } inside a $project stage to pull the first element out directly.

For joins that need more than an equality match — filtering the foreign collection, or matching on a computed value — $lookup also accepts a let plus a full sub-pipeline instead of localField/foreignField. That's the escape hatch for "join, but only the customer's shipped orders from the last 90 days" style logic; reach for it only when the simple form can't express the condition.

2. $unwind: Flattening Arrays

$unwind takes a document with an array field and outputs one document per array element, copying every other field unchanged. It's the stage that makes an embedded array groupable — you can't $group by the contents of an array directly, but you can once each element has its own document.

an order with a line-item array

{
  _id: ObjectId("o3"),
  customerId: ObjectId("c2"),
  items: [
    { product: "Keyboard", qty: 1, price: 45 },
    { product: "Mouse",    qty: 2, price: 20 }
  ]
}

revenue per product, across every order

db.orders.aggregate([
  { $unwind: "$items" },
  {
    $group: {
      _id: "$items.product",
      unitsSold: { $sum: "$items.qty" },
      revenue: { $sum: { $multiply: ["$items.qty", "$items.price"] } }
    }
  },
  { $sort: { revenue: -1 } }
])

After $unwind, the order with two line items becomes two documents — each carrying the full order's other fields plus one items object instead of the array. That's what makes $group on "$items.product" possible: every document now represents exactly one product sale.

An order with an empty items array disappears by default

$unwind drops any document whose target array is missing, null, or empty — that order simply won't appear in the output. If a report needs to account for those orders too (an "orders with $0 in items" edge case), use the object form: { $unwind: { path: "$items", preserveNullAndEmptyArrays: true } }.

3. $facet: Parallel Pipelines

A dashboard endpoint often needs several different summaries of the same underlying data — a category breakdown and a top-5 list and a grand total. Running three separate aggregate() calls means reading the collection three times. $facet runs multiple sub-pipelines against the same input in a single stage, returning one document with each sub-pipeline's results under its own name.

one query, three summaries

db.orders.aggregate([
  { $match: { status: "shipped" } },
  {
    $facet: {
      byStatus: [
        { $group: { _id: "$status", count: { $sum: 1 } } }
      ],
      topCustomers: [
        { $group: { _id: "$customerId", spent: { $sum: "$total" } } },
        { $sort: { spent: -1 } },
        { $limit: 5 }
      ],
      grandTotal: [
        { $group: { _id: null, revenue: { $sum: "$total" } } }
      ]
    }
  }
])

Each key inside $facet (byStatus, topCustomers, grandTotal) is its own independent pipeline, run against the same set of documents produced by the $match stage before it — the sub-pipelines don't see each other's output, only the shared input.

Filter before you facet

$facet buffers all of its input in memory to feed each sub-pipeline, which makes it noticeably heavier than an equivalent single-purpose pipeline. Always put a $match (and, where possible, indexed fields) before $facet so it's summarizing thousands of relevant documents, not the entire collection.

4. Designing Multi-Stage Pipelines

An aggregation pipeline is read top to bottom, each stage passing its output to the next — the same mental model as a Unix pipe. Two habits keep a long pipeline both correct and fast:

Filter as early as possible. A $match at the very start — ideally on an indexed field — shrinks everything downstream. A $match placed after an expensive $lookup or $unwind still pays the cost of processing documents it's about to discard.
Prefer $project/$addFields over recomputing values. Compute a derived field once, early, and reference it in later stages rather than repeating the same expression in every $group.

explain() works on pipelines too

db.orders.aggregate([
  { $match: { status: "shipped" } },
  { $lookup: { from: "customers", localField: "customerId", foreignField: "_id", as: "customer" } }
]).explain("executionStats")

The output shows, stage by stage, how many documents entered and left each part of the pipeline — exactly the tool you need to confirm a $match is actually running first and using an index, rather than trusting that it is.

5. A Reporting Pipeline, End to End

Putting all three stages together: a monthly revenue-by-category report, with a grand total attached, computed in a single call.

monthly category revenue report

db.orders.aggregate([
  // 1. Filter to the window we care about, on an indexed field, first
  { $match: { placedAt: { $gte: ISODate("2026-07-01"), $lt: ISODate("2026-08-01") } } },

  // 2. Flatten line items so we can group by product/category
  { $unwind: "$items" },

  // 3. Join each line item to its product document for category info
  {
    $lookup: {
      from: "products",
      localField: "items.product",
      foreignField: "sku",
      as: "productInfo"
    }
  },
  { $unwind: "$productInfo" },

  // 4. Group revenue by category
  {
    $group: {
      _id: "$productInfo.category",
      revenue: { $sum: { $multiply: ["$items.qty", "$items.price"] } },
      unitsSold: { $sum: "$items.qty" }
    }
  },
  { $sort: { revenue: -1 } },

  // 5. Attach a grand total alongside the breakdown, in the same response
  {
    $facet: {
      byCategory: [{ $match: {} }],
      grandTotal: [
        { $group: { _id: null, revenue: { $sum: "$revenue" } } }
      ]
    }
  }
])

Notice the order: $match first to shrink the working set, $unwind and $lookup next to reshape and join, then $group to summarize, and only at the very end $facet to package the summary alongside a total — one pipeline, one round trip, everything a reporting endpoint needs.

6. Hands-on Exercise

Hands-on

Build a customer-spending dashboard pipeline

One aggregation pipeline against seed customers and orders collections that a dashboard endpoint could call directly.

Requirements:

Seed at least 5 customers and 15 orders (some customers with multiple orders, some orders with an items array of 2–3 line items each).
Write a pipeline that $matches orders with status: "shipped", then $lookups each order's customer and flattens the resulting array with $unwind.
Add a $group stage that computes total spend and order count per customer, sorted highest spend first.
Wrap the result in a $facet stage that returns the per-customer breakdown and a separate grandTotal of all shipped-order revenue, in one response.
Run .explain("executionStats") on your pipeline and confirm the $match stage is the first one to execute, before the more expensive $lookup/$unwind stages.

Hint

If your grand total doesn't match the sum of the per-customer breakdown, check whether your $facet sub-pipelines are both reading from the same post-$match input — a common mistake is filtering inside one sub-pipeline but not the other.

7. Knowledge Check

Four quick questions. Expand each to check your answer.

Why does `$lookup` always add an array field, even when only one document matches?

$lookup performs a left outer join, and MongoDB has no built-in guarantee that the foreign field is unique — so it always returns every match as an array, even if that array only ever holds zero or one element in practice. Flattening it to a plain object (with $unwind or $arrayElemAt) is a separate, deliberate step.

An order with an empty `items: []` array vanishes after `$unwind: "$items"`. Why, and how do you keep it?

By default, $unwind drops any document whose target array is missing, null, or empty, since there's no element to produce a document from. Use the object form — { $unwind: { path: "$items", preserveNullAndEmptyArrays: true } } — to keep that document in the output instead, with items set to null.

Why should a `$match` stage almost always come before a `$facet` stage, rather than after?

$facet buffers all of its input in memory and feeds the same set of documents to every sub-pipeline, which is expensive on a large input. A $match placed before it shrinks that input once; a $match placed only inside individual sub-pipelines still forces $facet to buffer the full, unfiltered collection first.

What does running `.explain("executionStats")` on an aggregation pipeline actually show you?

It breaks down execution stage by stage, showing how many documents entered and left each one — the same tool from Week 2's single-query explain(), applied across a whole pipeline. It's how you confirm a $match is actually running first and using an index, instead of assuming the stage order you wrote is the stage order that executes efficiently.