The aggregation pipeline

find retrieves documents. The aggregation pipeline transforms them — grouping, joining, reshaping, computing. It is MongoDB's GROUP BY, its JOIN, and a good deal more, and it is where MongoDB stops looking like a toy key-value store and becomes a real query engine.

The idea is a pipeline: documents flow through an array of stages, each transforming the stream and passing it on. If you have used a Unix pipe (grep | sort | uniq -c) or JavaScript .filter().map().reduce(), you already have the mental model.

db.books.aggregate([
  { $match: { copies: { $gt: 1 } } },   // stage 1: keep some documents   (WHERE)
  { $group: { _id: "$author.city",      // stage 2: group and compute     (GROUP BY)
              count: { $sum: 1 } } },
  { $sort: { count: -1 } }              // stage 3: order the result       (ORDER BY)
])

Three stages, read top to bottom: filter, then group, then sort. Order matters, and it matters for performance — see the end.

The stages you will use constantly

`$match` — the `WHERE`

{ $match: { "author.city": "Pune", copies: { $gt: 1 } } }

Identical syntax to a find filter. Put it first whenever you can — it shrinks the stream before the expensive stages, and only a $match at the very start can use an index.

`$group` — the `GROUP BY`

The heart of aggregation:

{ $group: {
    _id: "$author.city",              // the grouping key — GROUP BY author.city
    count: { $sum: 1 },               // COUNT(*)
    avgPrice: { $avg: "$price" },     // AVG(price)
    maxPrice: { $max: "$price" },     // MAX(price)
    titles: { $push: "$title" }       // array_agg(title) — collect into an array
} }

Run on the library data, grouping books by author city:

{ _id: 'Pune',    count: 3, avgPrice: 486000 }
{ _id: 'Chennai', count: 1, avgPrice: 29900 }

_id is the grouping key — the one required field, and _id: null groups everything into one bucket for a grand total. The accumulators are $sum, $avg, $min, $max, $push (collect into an array — SQL has no clean equivalent), $addToSet (distinct values), $first, $last, $count. The $ before a field name ("$price") means "the value of this field", as opposed to a literal string.

`$sort`, `$limit`, `$skip` — ordering and paging

{ $sort: { count: -1 } }, { $limit: 10 }

$sort then $limit is a top-N, and MongoDB optimises the pair — it keeps only the top 10 as it sorts rather than sorting everything.

`$project` — the `SELECT` list, plus computed fields

{ $project: {
    title: 1,
    priceRupees: { $divide: ["$price", 100] },     // a computed field
    _id: 0
} }

$project reshapes each document: pick fields, rename them, compute new ones. $addFields (or its alias $set) is the same but keeps the existing fields and adds to them.

`$unwind` — the one with no SQL equivalent

This is the array-native stage, and it is what the document model was building toward. It explodes an array into one document per element:

db.books.aggregate([
  { $unwind: "$categories" },                          // one row per (book, category)
  { $group: { _id: "$categories", n: { $sum: 1 } } },
  { $sort: { n: -1 } }
])

{ _id: 'fiction', n: 2 }
{ _id: 'literary', n: 1 }
{ _id: 'science',  n: 1 }
...

A book with three categories becomes three documents, one per category, which you can then group. Relationally this needed a join table; here the array is the relationship, and $unwind turns it back into rows on demand. When you need to aggregate over the elements of an array, $unwind first.

`$lookup` — the `JOIN`

Yes, MongoDB can join:

db.loans.aggregate([
  { $lookup: {
      from: "books",           // the other collection
      localField: "book_id",   // field on this side
      foreignField: "_id",     // field on the other side
      as: "book"               // results land here, as an ARRAY
  } },
  { $unwind: "$book" },        // flatten the single-element array to an object
  { $group: { _id: "$book.title", loans: { $sum: 1 } } }
])

{ _id: 'The Long Monsoon', loans: 2 }
{ _id: 'Deccan Kitchens',  loans: 1 }

Two things to know about $lookup, honestly:

It returns an array — every match, even for a one-to-one, so you almost always $unwind after it.
It is a left outer join and it is not fast. It runs per input document and benefits greatly from an index on foreignField, but a relational database's join planner is more sophisticated. If your workload is join-heavy, that is a signal the data was relational and MongoDB is the wrong home for it — the recurring theme of this module.

A realistic multi-stage pipeline

"Average loan duration for returned loans, per book, longest first" — putting several stages together:

db.loans.aggregate([
  { $match: { returned: { $ne: null } } },                         // returned loans only
  { $project: {                                                    // compute the duration
      book_id: 1,
      days: { $dateDiff: { startDate: "$borrowed", endDate: "$returned", unit: "day" } }
  } },
  { $group: { _id: "$book_id", avgDays: { $avg: "$days" }, n: { $sum: 1 } } },
  { $lookup: { from: "books", localField: "_id", foreignField: "_id", as: "book" } },
  { $unwind: "$book" },
  { $project: { _id: 0, title: "$book.title", avgDays: 1, n: 1 } },
  { $sort: { avgDays: -1 } }
])

Filter, compute, group, join for the name, reshape, sort. Read a pipeline top to bottom and it narrates what it does — which is the strength of the model, and why a long pipeline is more readable than a deeply nested SQL query even though the SQL is often shorter.

`$facet` — several aggregations in one pass

{ $facet: {
    byCity:     [ { $group: { _id: "$author.city", n: { $sum: 1 } } } ],
    priceStats: [ { $group: { _id: null, min: { $min: "$price" }, max: { $max: "$price" } } } ]
} }

Each sub-pipeline runs against the same input and returns its own result array. This is how a dashboard gets all its numbers — counts, a histogram, top-N — in one round trip rather than five. Genuinely useful, and it has no tidy SQL equivalent short of several separate queries.

Performance, which is the same lesson as before

Three rules, all of which you have met on the SQL side:

$match and $sort early. A $match at the very start of the pipeline can use an index; one in the middle cannot. Filter the stream down before the heavy stages, for the same reason you push WHERE down in SQL.

$sort before $group cannot use an index; after $group it cannot either — grouping destroys any index order. So sort at the end, and if you need a sorted input for something, do it before the group with an index behind it.

.explain() works on a pipeline. db.coll.aggregate([...]).explain() shows whether the opening $match hit an index (IXSCAN) or scanned the collection (COLLSCAN) — read exactly like the SQL EXPLAIN.

And a real limit worth knowing: a pipeline stage holds its working set in memory with a 100 MB cap per stage; exceed it and the stage errors unless you pass allowDiskUse: true, which lets it spill to disk (slower). A $group or $sort over a large collection is where you meet this — the same "the sort spilled to disk" story as work_mem in PostgreSQL.

Check your work

What the aggregation pipeline is. An array of stages; documents flow through, each stage transforming the stream.

The mental model. A Unix pipe, or .filter().map().reduce().

$match. The WHERE; put it first so it can use an index and shrink the stream.

$group and its _id. The GROUP BY; _id is the grouping key, _id: null for a grand total.

Five accumulators. $sum, $avg, $min/$max, $push (into an array), $addToSet (distinct).

What $ before a field name means. The value of that field, not a literal string.

$unwind, and why it has no SQL equivalent. It explodes an array into one document per element, so you can aggregate over array contents.

$lookup, and its two catches. A left outer join; it returns an array (so you $unwind after), and it is slower than a relational join.

What $lookup needing an index on foreignField tells you. Join-heavy work suggests the data was relational.

$facet. Several sub-pipelines over the same input in one pass — a dashboard in one round trip.

Two performance rules. $match early to use an index; grouping destroys index order so sort around it deliberately.

The per-stage memory cap. 100 MB; exceed it and you need allowDiskUse: true.

Practice

Group your books by a field and compute a count and an average. Compare with the SQL GROUP BY.
Add a $match before the $group and confirm it changes the result. Move it after and note it still works but reads more.
Use $push to collect titles per group, then $addToSet and note the difference.
$unwind an array field and group by its elements. Write the equivalent SQL with a join table and compare.
$lookup two collections and observe the array result. Add $unwind to flatten it.
Build the five-stage loan-duration pipeline and read it top to bottom as a sentence.
Add $facet to compute two different summaries in one pass.
Run .explain() on a pipeline whose first stage is $match on an indexed field. Find IXSCAN. Then $match on an unindexed field and find COLLSCAN.
Move the $match to the middle of the pipeline and check .explain() again — note it no longer uses the index.
Force a large $group to exceed 100 MB (or lower the limit) and read the error, then add allowDiskUse: true.
Take one of your SQL reporting queries and rebuild it as a pipeline. Note which is more readable.

Official documentation

MongoDB — Aggregation pipeline — The model and the stage list.
MongoDB — Aggregation stages — Every $match, $group, $unwind, $lookup, $facet.
MongoDB — Aggregation accumulators — $sum, $avg, $push, $addToSet and the rest.
MongoDB — $lookup — The join, including its variants and its cost.
MongoDB — Aggregation pipeline optimization — Stage ordering and index use.
MongoDB — SQL to aggregation mapping — A side-by-side table for every SQL construct you know.

Next: indexes in MongoDB, where the SQL rules almost all apply.

The stages you will use constantly

$match — the WHERE

$group — the GROUP BY

$sort, $limit, $skip — ordering and paging

$project — the SELECT list, plus computed fields

$unwind — the one with no SQL equivalent

$lookup — the JOIN