Building a Multi-Stage Aggregation Pipeline

A single aggregation stage can filter or reshape documents, but real reporting questions — monthly revenue, top-selling products, customer lifetime spend — almost always need several transformations chained together. A multi-stage pipeline lets you combine stages like $match, $unwind, $group, $lookup, $sort, and $project into one request, where each stage consumes the exact document stream produced by the stage before it. Getting good at this means thinking about two things at once: correctness (does each stage receive the shape it expects?) and performance (are you filtering and indexing as early as possible?).

Overview: How a Pipeline Works

An aggregation pipeline is an array of stage objects passed to db.collection.aggregate([...]). MongoDB runs the stages in the exact order you list them. The first stage receives the raw BSON documents in the collection; every stage after that receives whatever documents the previous stage emitted — not the original documents. This matters constantly: once a $group stage runs, the original document fields are gone unless you explicitly carried them forward, and once a $project drops a field, no later stage can see it.

Internally, each stage is a transformation over a stream of documents. $match and $sort placed early in the pipeline can use collection indexes exactly like a normal find() query, because at that point the documents still look like the ones on disk. Once you pass through $unwind, $group, or $project, the shape changes and any indexes on the original fields no longer apply to later stages — there is no such thing as an index on a computed field. This is the single biggest performance lever in pipeline design: push filtering as early as possible, before the shape-changing stages.

Pipelines also have a memory budget. Stages like $group and $sort that need to see many documents at once are limited to 100MB of RAM by default; if a stage exceeds that, MongoDB throws an error unless you pass { allowDiskUse: true }, which lets the server spill intermediate results to temporary files on disk (slower, but unbounded). Well-designed pipelines shrink the document set with $match and $project before they reach the memory-hungry stages, so allowDiskUse is rarely needed in practice.

Syntax

There is no special syntax beyond ordinary JavaScript arrays and objects — a pipeline is just an ordered list of stage documents:

db.collection.aggregate([
  { $match: { field: value } },
  { $unwind: "$arrayField" },
  { $group: { _id: "$field", total: { $sum: "$amount" } } },
  { $sort: { total: -1 } },
  { $limit: 10 },
  { $project: { _id: 0, field: 1, total: 1 } }
], { allowDiskUse: true });

Stage	Purpose
`$match`	Filters documents using normal query syntax; can use indexes if it’s the first stage.
`$unwind`	Deconstructs an array field, emitting one output document per array element.
`$group`	Groups documents by an `_id` expression and computes accumulators (`$sum`, `$avg`, `$max`, `$push`, etc.) per group.
`$sort`	Orders documents; cheap and index-backed only when it’s the very first stage.
`$limit` / `$skip`	Restricts or offsets the number of documents passed downstream.
`$project`	Includes, excludes, or computes fields, reshaping each document.
`$lookup`	Performs a left outer join against another collection in the same database.

The optional second argument, { allowDiskUse: true }, permits $group and $sort to use temporary disk files when a stage’s working set exceeds the in-memory limit.

Examples

Example 1: Revenue and order count per status

Suppose db.orders holds documents like { customer: "Alice Johnson", customerId: ObjectId(...), status: "shipped", orderDate: ISODate(...), items: [{ product: "Wireless Mouse", qty: 2, price: 25 }, ...] }. We want total revenue and order count per status — but revenue has to be computed per line item, then rolled up per order, then rolled up per status. That’s naturally a multi-stage pipeline:

db.orders.aggregate([
  { $match: { status: { $in: ["shipped", "delivered"] } } },
  { $unwind: "$items" },
  { $group: {
      _id: { orderId: "$_id", status: "$status" },
      orderTotal: { $sum: { $multiply: ["$items.qty", "$items.price"] } }
  } },
  { $group: {
      _id: "$_id.status",
      totalRevenue: { $sum: "$orderTotal" },
      orderCount: { $sum: 1 }
  } },
  { $sort: { totalRevenue: -1 } }
]);

Output:

[
  { _id: 'delivered', totalRevenue: 3120, orderCount: 18 },
  { _id: 'shipped', totalRevenue: 2450, orderCount: 12 }
]

Notice the two $group stages. If you skipped the first one and grouped straight by status after $unwind, orderCount would count line items, not orders — a classic multi-stage bug. Grouping by { orderId, status } first collapses each order back to one document with its true total, and only then do we roll those up by status.

Example 2: Top 5 products by revenue this year

db.orders.aggregate([
  { $match: { orderDate: { $gte: ISODate("2026-01-01") } } },
  { $unwind: "$items" },
  { $group: {
      _id: "$items.product",
      unitsSold: { $sum: "$items.qty" },
      revenue: { $sum: { $multiply: ["$items.qty", "$items.price"] } }
  } },
  { $sort: { revenue: -1 } },
  { $limit: 5 },
  { $project: {
      _id: 0,
      product: "$_id",
      unitsSold: 1,
      revenue: 1
  } }
]);

Output:

[
  { product: 'Wireless Mouse', unitsSold: 214, revenue: 5350 },
  { product: 'Mechanical Keyboard', unitsSold: 96, revenue: 8640 },
  { product: 'USB-C Cable', unitsSold: 340, revenue: 3400 },
  { product: '27-inch Monitor', unitsSold: 41, revenue: 7380 },
  { product: 'Webcam', unitsSold: 88, revenue: 2640 }
]

The $match runs first and can use an index on orderDate. Only after the working set is small does the pipeline pay the cost of $unwind and $group. The final $project renames _id to product and drops the raw _id field so the output reads cleanly.

Example 3: Customers who spent over $100, joined with customer details

This example chains seven stages, including a $lookup join and a second $match that behaves like a SQL HAVING clause:

db.orders.aggregate([
  { $match: { status: "delivered" } },
  { $unwind: "$items" },
  { $group: {
      _id: "$customerId",
      totalSpent: { $sum: { $multiply: ["$items.qty", "$items.price"] } }
  } },
  { $match: { totalSpent: { $gt: 100 } } },
  { $lookup: {
      from: "customers",
      localField: "_id",
      foreignField: "_id",
      as: "customerInfo"
  } },
  { $unwind: "$customerInfo" },
  { $project: {
      _id: 0,
      name: "$customerInfo.name",
      email: "$customerInfo.email",
      totalSpent: 1
  } },
  { $sort: { totalSpent: -1 } }
]);

Output:

[
  { name: 'Alice Johnson', email: 'alice@example.com', totalSpent: 340 },
  { name: 'Marcus Lee', email: 'marcus@example.com', totalSpent: 275 },
  { name: 'Priya Nair', email: 'priya@example.com', totalSpent: 118 }
]

The first $match is index-backed and shrinks the collection before anything expensive happens. The second $match, on totalSpent, can’t use an index — that field doesn’t exist until $group computes it — but it still matters, because it shrinks the document set before the costly $lookup join runs against customers. The final $unwind assumes each order’s customerId matches exactly one customer document, which is safe here since _id is unique.

How It Works Step by Step

Walking through Example 3: (1) $match scans the status index (if one exists) and passes only "delivered" orders downstream — check this with explain(). (2) $unwind multiplies each order into one document per item in its items array; a five-item order becomes five documents. (3) The first $group collapses those back down by customerId, maintaining a running sum in memory for each distinct group key as documents stream through. (4) The second $match filters groups, discarding any customer whose total didn’t clear $100 — conceptually identical to a SQL HAVING. (5) $lookup runs, for each remaining document, an equality lookup against customers._id (which is indexed by default, since it’s the primary key), attaching matches as an array field. (6) $unwind flattens that one-element array into a plain subdocument. (7) $project reshapes the final output, and $sort orders the now-small result set. You can inspect any of this with explain("executionStats"):

db.orders.aggregate([
  { $match: { status: "delivered" } },
  { $unwind: "$items" },
  { $group: { _id: "$customerId", totalSpent: { $sum: "$items.price" } } }
]).explain("executionStats");

Output (trimmed):

{
  stages: [
    {
      '$cursor': {
        queryPlanner: {
          winningPlan: {
            stage: 'FETCH',
            inputStage: { stage: 'IXSCAN', indexName: 'status_1' }
          }
        }
      }
    },
    { '$unwind': { path: '$items' } },
    { '$group': { _id: '$customerId', totalSpent: { '$sum': '$items.price' } } }
  ]
}

Seeing IXSCAN here confirms the leading $match used the status_1 index instead of a full COLLSCAN. If you instead saw COLLSCAN, that’s your signal to either add an index or reconsider whether the query needs to touch every document.

Common Mistakes

1. Filtering too late

Putting $match after $unwind and $group forces the server to explode and process every document — including ones you’ll throw away — before it ever gets a chance to narrow anything down.

// Scans and unwinds every order, including cancelled ones,
// before filtering — expensive on a large collection
db.orders.aggregate([
  { $unwind: "$items" },
  { $group: {
      _id: "$items.product",
      revenue: { $sum: { $multiply: ["$items.qty", "$items.price"] } }
  } },
  { $match: { revenue: { $gt: 500 } } }
]);

Fixed — filter first, ideally on an indexed field, so far fewer documents ever reach $unwind:

// Filters out unwanted orders first, ideally using an index
// on status, so far fewer documents reach $unwind and $group
db.orders.aggregate([
  { $match: { status: { $in: ["shipped", "delivered"] } } },
  { $unwind: "$items" },
  { $group: {
      _id: "$items.product",
      revenue: { $sum: { $multiply: ["$items.qty", "$items.price"] } }
  } },
  { $match: { revenue: { $gt: 500 } } }
]);

2. Comparing ObjectId to a string

customerId is stored as a BSON ObjectId. A value pulled from a URL parameter or a form field arrives as a plain string, and ObjectId never loosely equals a string in a query — the match silently returns nothing.

// customerId is stored as an ObjectId, but this value came
// straight from a URL param as a string — matches nothing
const customerId = "64f1a2b3c4d5e6f7a8b9c0d1";
db.orders.aggregate([
  { $match: { customerId: customerId } },
  { $group: { _id: "$status", count: { $sum: 1 } } }
]);

Fixed by wrapping the string in new ObjectId(...) before it reaches the query:

const customerId = new ObjectId("64f1a2b3c4d5e6f7a8b9c0d1");
db.orders.aggregate([
  { $match: { customerId: customerId } },
  { $group: { _id: "$status", count: { $sum: 1 } } }
]);

3. Unbounded $lookup before narrowing the input

A $lookup runs once per document flowing into it. Put it before any filtering and MongoDB joins the entire orders collection against the entire customers collection, even though you only cared about a handful of delivered orders.

// Joins every order in the collection against customers
// before narrowing anything down
db.orders.aggregate([
  { $lookup: {
      from: "customers",
      localField: "customerId",
      foreignField: "_id",
      as: "customerInfo"
  } },
  { $match: { status: "delivered" } }
]);

Fixed by moving the filter ahead of the join:

// Shrinks the working set with an index-backed $match
// before the join has to run at all
db.orders.aggregate([
  { $match: { status: "delivered" } },
  { $lookup: {
      from: "customers",
      localField: "customerId",
      foreignField: "_id",
      as: "customerInfo"
  } }
]);

Best Practices

Put $match (and $sort, if it can use an index) as the very first stage whenever possible, so the query planner can use IXSCAN instead of a full collection scan.
Run .explain("executionStats") on any pipeline touching a large collection and check for COLLSCAN before shipping it.
Use $project early to drop fields you don’t need, especially large embedded arrays or blobs, to reduce the memory each later stage has to move around.
Never $unwind a large array without a preceding $match to cut down the document count first — unwinding multiplies documents, it doesn’t shrink them.
Place $limit as early as correctness allows; MongoDB can push some limits ahead of certain stages automatically, but you shouldn’t rely on that for correctness.
Reach for { allowDiskUse: true } only after you’ve tried to shrink the pipeline’s working set — it hides memory problems rather than solving them.
When joining with $lookup, make sure foreignField is indexed in the target collection; an unindexed foreign field turns the join into a scan per input document.
Prefer plain query operators ($match: { status: "shipped" }) over $expr-based matches when a normal field comparison will do — $expr can prevent index usage in some cases.

Practice Exercises

Using db.orders, write a pipeline that computes the average order value (total items price, summed per order) grouped by month of orderDate. Expect one document per month shaped like { _id: "2026-01", avgOrderValue: N }.
Find the 3 customers with the most orders in "cancelled" status, joined with their customers record to include name and email. Hint: you’ll need $match, $group, $sort, $limit, and $lookup, in that order.
Take the flawed pipeline from Common Mistake #1 above and rewrite it so explain("executionStats") shows an IXSCAN on the leading stage, assuming an index exists on status.

Summary

A pipeline is an ordered array of stages; each stage consumes exactly what the previous stage emitted, not the original documents.
Only stages before the first shape-changing stage ($group, $unwind, $project) can use collection indexes — put $match and index-backed $sort first.
$unwind multiplies documents (one per array element); a follow-up $group is often needed to collapse them back to the right level before further aggregation.
$group and $sort are capped at 100MB of RAM per stage unless you pass { allowDiskUse: true }.
$lookup performs a per-document join against another collection — filter before it runs, and index its foreignField.
Use .explain("executionStats") to confirm the planner is using an index (IXSCAN) rather than scanning the whole collection (COLLSCAN).

Building a Multi-Stage Aggregation Pipeline

Overview: How a Pipeline Works

Syntax

Examples

Example 1: Revenue and order count per status

Example 2: Top 5 products by revenue this year

Example 3: Customers who spent over $100, joined with customer details

How It Works Step by Step

Common Mistakes

1. Filtering too late

2. Comparing ObjectId to a string

3. Unbounded $lookup before narrowing the input

Best Practices

Practice Exercises

Summary

Table of Contents