HowToCodePage

The aggregation pipeline

Stages as a sequence of transformations, the accumulators worth knowing, and how to keep a pipeline eligible for indexes.

Stages as transformations

A pipeline is an array of stages; each takes the documents produced by the previous stage and emits new ones. Read it top to bottom, because that is also the order the server executes it in — and therefore the order that decides whether an index can be used.

db.orders.aggregate([
  { $match: { placedAt: { $gte: ISODate("2026-01-01") }, status: "paid" } },
  { $unwind: "$items" },
  { $group: {
      _id: { sku: "$items.sku" },
      units:  { $sum: "$items.qty" },
      revenue:{ $sum: { $multiply: ["$items.qty", "$items.unitPrice"] } },
      orders: { $addToSet: "$_id" }
  } },
  { $project: {
      _id: 0,
      sku: "$_id.sku",
      units: 1,
      revenue: 1,
      orderCount: { $size: "$orders" }
  } },
  { $sort: { revenue: -1 } },
  { $limit: 10 }
]);
StagePurpose
$matchFilter documents — put it first so later stages see less data
$project / $addFieldsShape documents: keep, rename, drop or compute fields
$groupCollapse by key with accumulators
$unwindExpand one array into one document per element
$lookupLeft outer join to another collection
$facetRun several sub-pipelines over the same input for one result page
$merge / $outWrite results to a collection — materialised summaries
$sort / $limit / $skipOrder and page; $sort before $limit is mandatory for determinism

Accumulators and shaping

db.orders.aggregate([
  { $match: { status: { $ne: "cancelled" } } },
  { $addFields: { units: { $sum: "$items.qty" } } },
  { $group: {
      _id: { $dateTrunc: { date: "$placedAt", unit: "day" } },
      orders:   { $sum: 1 },
      avgUnits: { $avg: "$units" },
      first:   { $first: "$customer.name" },
      last:    { $last: "$customer.name" },
      skus:    { $push: "$items.sku" }
  } },
  { $unwind: { path: "$skus", preserveNullAndEmptyArrays: true } },
  { $sort: { _id: -1 } }
]);
  • $sum, $avg, $min, $max aggregate numbers; $sum: 1 counts documents.
  • $push keeps duplicates and can grow the document; $addToSet removes them but is unordered.
  • $first and $last depend on the incoming order, so sort before grouping if you care.
  • Without preserveNullAndEmptyArrays, $unwind silently drops documents whose array is missing or empty.
  • Each stage has a 100 MB memory budget; add allowDiskUse: true when a large $sort or $group exceeds it.

Keeping a pipeline fast

db.orders.explain("executionStats").aggregate([
  { $match: { status: "paid", placedAt: { $gte: ISODate("2026-01-01") } } },
  { $group: { _id: "$customer.id", total: { $sum: 1 } } }
]);
  • Only a $match or $sort at the start of the pipeline can use an index — after $group or $project the planner is working with computed documents.
  • In executionStats, look for IXSCAN versus COLLSCAN, and compare totalKeysExamined and totalDocsExamined against nReturned.
  • $lookup requires an index on the foreign field; without it, every input document triggers a collection scan on the other side.
  • Use $facet to return results plus counts plus filter options in one pass instead of three round trips.
⚠️
A pipeline that starts with $project or $addFields throws away index eligibility for every stage after it. Filter first, compute later.

FAQ

How do I count distinct values?
Group by the field and then count groups, or use $addToSet inside a group and take $size of the result. db.collection.distinct() is simpler but returns the whole set to the client.
Should I use aggregation or a view?
A read-only view is a stored pipeline — useful when many callers need the same shape. If the input is large and the result is small, write it to a collection with $merge and refresh on a schedule.

Documents, collections and queries Indexes and schema design patterns

Last refreshed 2026-09-18.