Lesson 0005 — The aggregation pipeline

MongoDB's real query language for anything past a simple find. A stream of documents flows through ordered stages — and the last stage you reach for, $lookup, is the join the document model spent lesson 0001 trying to avoid.

Warm-up — apply lesson 0004 first (cold, from memory)

A plan shows stage: COLLSCAN, nReturned: 2, totalDocsExamined: 50000. The verdict:

Examined (50000) ≫ returned (2), and it's a COLLSCAN with no index. Reading the whole collection to emit two docs — add an index so examined collapses toward nReturned.

MongoDB's classic planner picks the winning plan by:

The classic multi-planner runs candidates in a trial and keeps the one with most results for least work — then caches it by query shape. That's an empirical race, not Postgres-style cost-from-stats.

In a plan, an IXSCAN with no FETCH stage means the query was:

No FETCH = answered from index keys, never opening a document: a covered query (0003). A FETCH means it went to the doc; a SORT stage means the index didn't supply the order.

A find() filters and returns documents. The moment you need to compute — group and count, join, reshape, bucket by month — you reach for the aggregation pipeline. Think of the Unix pipe you already use: cat | grep | sort | uniq -c. Each command transforms a stream and hands it on. MongoDB's pipeline is that idea for documents, and it maps almost one-to-one onto the SELECT … WHERE … GROUP BY … ORDER BY you know — just written as an ordered list of stages instead of one declarative statement.

The model: stages over a stream of documents

The core stages — each is a SQL clause you know

Order matters — and the optimizer helps, the way you'd hope

Stage	Does	SQL equivalent
`$match`	Filter documents by a condition	`WHERE`
`$group`	Group by a key, compute aggregates (`$sum`, `$avg`)	`GROUP BY`
`$project`	Choose / rename / compute fields	`SELECT` list
`$sort`	Order the stream	`ORDER BY`
`$limit` / `$skip`	Take / skip N	`LIMIT` / `OFFSET`
`$unwind`	Explode an array → one doc per element	≈ `UNNEST`
`$lookup`	Join in documents from another collection	`LEFT OUTER JOIN`

This is predicate pushdown — you already know it

Filtering before sorting/joining/grouping is exactly the predicate pushdown you saw the SQL planner do in the EXPLAIN lessons. The lesson for you: put $match (and a $sort it can use) first, so the pipeline's opening stage hits an index — the same first-stage index use you read in explain() in 0004. A related win: a $sort immediately followed by $limit is coalesced into a top-N sort that keeps only N items in memory. — MongoDB Manual: "When a $sort precedes a $limit, the optimizer can coalesce the $limit into the $sort… This allows the sort operation to only maintain the top n results."

$lookup: the join the document model tried to avoid

The course comes full circle

MongoDB's own guidance: "Excessive use of $lookup may slow down query performance. To reduce reliance on $lookup, consider an embedded data model." — MongoDB Manual — $lookup This is lesson 0001 returning as a diagnostic: if your pipelines are thick with $lookup, that's a signal your schema is normalized like a relational one and the data you keep joining probably wants to be embedded. A join is cheap in Postgres because the whole engine is built around it; in MongoDB it's the thing good schema design lets you avoid.

$unwind: the array move with no clean SQL twin

Because MongoDB fields can be arrays, one stage has no tidy relational equivalent: $unwind takes a document with an N-element array and emits N documents, one per element — flattening the array so you can $group or $match on individual elements. It's the pipeline counterpart to the multikey indexing you met in 0003: arrays are first-class, so both the index layer and the query layer have a dedicated way to fan them out.

SQL query vs aggregation pipeline

Check yourself

Answer from memory — the effortful recall is what builds retention. Feedback is immediate.

Question	SQL (Postgres / MySQL)	MongoDB aggregation
Shape of a query	One declarative statement	An ordered list of stages
Filter / group / sort	WHERE / GROUP BY / ORDER BY	$match / $group / $sort
Join	Native, cheap, everywhere	$lookup — avoid via embedding
Who orders operations?	Planner, fully	You write order; optimizer still pushes $match early
Filter-early optimization	Predicate pushdown	$match moved before $sort / projection
Top-N	ORDER BY … LIMIT (top-N sort)	$sort + $limit coalesced

Which aggregation stage is the equivalent of SQL GROUP BY with a count?

$group buckets documents by its _id key and computes accumulators like { $sum: 1 } per bucket — exactly GROUP BY … COUNT(*). $match is WHERE; $project is the SELECT list.

Why put $match as early as possible in a pipeline?

An early $match shrinks the stream and lets the first stage use an index — predicate pushdown. The optimizer even moves $match ahead of $sort/projection for you, but writing it first makes the win explicit.

What does $lookup do?

$lookup left-outer-joins another collection in the same database, adding an array field of matched foreign docs. Useful, but the manual says avoid overusing it — embed instead where you can.

Your pipelines lean heavily on $lookup. The best signal that suggests:

Heavy $lookup usually means the schema is normalized like a relational one. Per MongoDB's own guidance, embedding the frequently-joined data removes the join — lesson 0001 returning as a diagnostic.

A $sort immediately followed by $limit: 10 is optimized how?

The optimizer coalesces $limit into the $sort so it maintains only the top N results in memory as it goes — a top-N sort, the cousin of ORDER BY … LIMIT.

Optional — when you have an instance

No Mongo handy yet, so this is a lab for later (Docker: docker run --rm -p 27017:27017 mongo, then mongosh):

db.users.aggregate([
  { $match: { status: "active" } },
  { $group: { _id: "$city", n: { $sum: 1 } } },
  { $sort:  { n: -1 } },
  { $limit: 5 }
])
db.users.aggregate([ /* same pipeline */ ], { explain: true })  // see the rewritten stage order
// With an index on { status: 1 }, the opening $match should IXSCAN, not COLLSCAN.

Read the explain output and find where MongoDB reordered your stages — the $match pulled to the front, hitting the index. Bring it to your teacher and we'll trace the rewrite together.

Primary source — read this next

MongoDB Manual — Aggregation Pipeline for the model, then Pipeline Optimization (the $match-early and $sort+$limit rewrites), and the $lookup reference — read its performance note twice.

The aggregation pipeline