Gowtham Potureddi

Posted on Sep 9

MongoDB for Data Engineers: Aggregation Pipeline & $lookup

#python #sql #interview #dataengineering

The mongodb aggregation pipeline is the piece of MongoDB that turns a raw stream of JSON-shaped documents into the grouped, joined, reshaped result a report or a downstream warehouse actually needs — and for a data engineer arriving from the relational world, it is the single skill that decides whether MongoDB feels like a black box or like a query engine you can reason about. Instead of a declarative SELECT ... GROUP BY ... JOIN, MongoDB hands you an ordered array of stages: each stage receives the documents the previous stage emitted, transforms them, and passes them on. Before you write a single stage, though, you have to understand the thing being transformed — the document model, where related data lives inside one record rather than spread across normalized tables — because the shape of your documents decides which stages you need and how expensive they are.

This guide is the walkthrough you wished existed the first time someone handed you a MongoDB URI and said "get last month's revenue per customer into the warehouse." It opens the pipeline in layers: the document data model and the embed-versus-reference decision that precedes every query, the core $match / $group / $project stages that filter, fold, and reshape the stream, the $lookup join and its $unwind companion that flatten one collection into another, the indexes that separate a three-millisecond aggregation from a three-minute collection scan, and finally $merge / $out and sharding — the primitives that let you materialize results and move them off the cluster at scale. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works. All examples use the MongoDB aggregation syntax you type into mongosh or a driver, but the mental model carries straight over to Atlas, DocumentDB, and any other document store.

When you want hands-on reps immediately after reading, drill the database practice library →, sharpen your pipeline intuition on the aggregation practice library →, and rehearse document-shape queries on the JSON practice library →.

On this page

The document data model
Aggregation pipeline stages ($match/$group/$project)
$lookup joins & $unwind
Indexing & performance
$merge/$out to a warehouse + sharding
Cheat sheet — MongoDB aggregation recipes
Frequently asked questions
Practice on PipeCode

1. The document data model

One document per entity, nested not normalized — model for how you read, not for how you store

The one-sentence invariant: MongoDB stores data as BSON documents grouped into collections, and the central modelling decision — long before you write any aggregation — is what to embed inside a document versus what to reference in another collection, a choice driven by how the data is read together, how large it can grow, and whether it is shared across many parents. A relational modeller normalizes first and joins at query time; a document modeller embeds the data that is read together and pays the join tax only where it is unavoidable. Get this decision right and most of your queries never need a $lookup at all; get it wrong and every read fans out across collections or every write rewrites a megabyte-sized document.

The axes that decide embed vs reference.

Read-together. If a parent and its children are almost always fetched in the same request — an order and its line items, a blog post and its tags — embed the children as a sub-document or array. One read returns the whole aggregate; no join, no second round trip.
Cardinality and growth. Embedding is safe for bounded one-to-few relationships (an order has a handful of line items). It is dangerous for unbounded one-to-many relationships (a user with millions of events) because a document has a hard 16 MB cap and MongoDB must rewrite the whole document on every update.
Sharing. If the same child is referenced by many parents — a customer referenced by thousands of orders — reference it by _id rather than duplicating it into every parent, or a single edit means updating thousands of copies.
Update independence. Fields updated on very different cadences (a product's price versus its lifetime view count) often belong in separate documents so a hot-write counter does not contend with cold catalogue data.

The BSON building blocks every data engineer must know.

The _id field. Every document has a unique _id; if you do not supply one, MongoDB generates a 12-byte ObjectId that embeds a timestamp, so _id is roughly insertion-ordered and can double as a coarse "created at."
BSON types. BSON extends JSON with real types: Int32, Int64, Double, Decimal128, Date, ObjectId, Boolean, arrays, and nested objects. When you extract to a warehouse, Decimal128 and Date map cleanly to NUMERIC and TIMESTAMP; a field that is sometimes a string and sometimes a number does not.
Arrays are first-class. An array field can be indexed (a multikey index) and unwound in a pipeline — this is what makes embedding line items both natural and queryable.
Schema-on-read. Collections do not enforce a schema by default. Two documents in orders can have different fields. That flexibility is a feature for application teams and a hazard for pipelines, which is why aggregation defensively coerces types.

The 2026 reality for data engineers.

The document store owns the OLTP write path. Application teams pick MongoDB for developer velocity; the data engineer inherits it as a source, not a choice. Your job is to read it efficiently and land it in a warehouse, not to relitigate the schema.
Schema drift is the recurring tax. Because there is no enforced schema, fields appear, disappear, and change type across releases. Robust pipelines pin the fields they need with $project and coerce types with $convert / $toDecimal rather than trusting the raw shape.
Optional schema validation exists but is opt-in. MongoDB supports JSON-Schema validators on a collection; mature teams enable them, but you cannot assume they are on.

What interviewers listen for.

Do you say "model for the read pattern" rather than "normalize to third normal form"? — the core document-modelling signal.
Do you name the 16 MB document cap as the hard limit on embedding unbounded arrays? — required answer.
Do you distinguish embed (read-together, bounded) from reference (shared, unbounded) with a concrete rule? — senior signal.
Do you mention ObjectId carries a timestamp and can order documents roughly by creation? — nice-to-have that flags real experience.

Worked example — embedding line items vs referencing them

Detailed explanation. The canonical modelling decision: an orders collection whose orders each have a small number of line items. The question is whether to embed the line items as an array inside the order document or to store them in a separate line_items collection referenced by order_id. For a read pattern of "show the order and all its lines," embedding wins outright.

Read pattern. The order-detail screen always shows the order plus its lines together.
Cardinality. An order has 1–50 line items — bounded and small.
Growth. Line items are written once at checkout and rarely change — no hot-write contention.

Question. Model the orders collection with embedded line items, and contrast it with the normalized three-collection alternative.

Input.

Design	Collections	Read cost for order detail	Risk
Embedded	`orders` (lines inside)	1 document read	16 MB cap if lines unbounded
Referenced	`orders`, `line_items`, `customers`	1 read + `$lookup` per child	joins on every read

Code.

// Embedded model — one document holds the whole order aggregate
db.orders.insertOne({
  _id: ObjectId(),
  customer_id: ObjectId("6650a1f2c3d4e5f601020304"),
  status: "paid",
  currency: "USD",
  total_cents: 4200,
  created_at: ISODate("2026-08-14T10:22:00Z"),
  line_items: [                       // embedded array — bounded, read-together
    { sku: "TSHIRT-BLK-M", qty: 2, unit_cents: 1500 },
    { sku: "STICKER-PACK",  qty: 1, unit_cents: 1200 }
  ]
});

// Reading the whole order aggregate is a single indexed lookup — no join
db.orders.findOne({ _id: ObjectId("6650b0000000000000000001") });

Step-by-step explanation.

The order document embeds line_items as an array of sub-documents. Everything the order-detail screen needs is in one BSON document, so the read is a single index seek on _id — no join, no second collection, no round trip.
total_cents is stored denormalized (pre-summed) rather than computed at read time. In a document store you often store the aggregate you read most, because there is no cheap SUM(line_items.qty * unit) across a separate table.
Each line item is a full sub-document with its own fields. Because arrays are first-class, you can later index line_items.sku (a multikey index) and $unwind the array in a pipeline to compute per-SKU revenue.
The referenced alternative would put lines in their own collection keyed by order_id; every order-detail read then costs a $lookup. That is the right trade only when lines are shared or unbounded — neither is true here.
The 16 MB cap is the guardrail: embedding is correct precisely because an order has at most a few dozen lines. If "line items" were "user click events," embedding would eventually blow the cap and referencing would be mandatory.

Output.

Metric	Embedded	Referenced
Reads to render order detail	1	1 + N (or 1 `$lookup`)
Writes to add a line item	rewrite 1 doc	insert 1 doc
Max safe children	~thousands (16 MB)	unbounded
Best when	read-together, bounded	shared, unbounded

Rule of thumb. Embed the data you read together and that grows only to bounded size; reference the data that is shared across parents or grows without bound. When in doubt, ask "will this array ever exceed a few thousand elements?" — if yes, reference it.

Worked example — `_id`, `ObjectId`, and BSON types you will meet in the warehouse

Detailed explanation. Before you extract MongoDB to a relational warehouse, you must understand what the values actually are. An ObjectId is not a string; a MongoDB Date is milliseconds since epoch; money stored as a floating-point Double will not reconcile to the penny. Walk through the types and how the pipeline coerces them.

_id as ObjectId. 12 bytes: a 4-byte timestamp, a 5-byte random value, and a 3-byte counter. The leading timestamp means ObjectIds sort roughly by creation time.
Money. Store as Decimal128 (or integer cents), never Double — floating point loses pennies at scale.
Dates. BSON Date is a signed 64-bit millisecond offset; it maps to TIMESTAMP cleanly.

Question. Given a raw order document, project a warehouse-friendly row: a string id, a real timestamp derived from the ObjectId, and money as a decimal.

Input.

Field	Raw BSON type	Warehouse target
`_id`	ObjectId	STRING (hex)
created (from `_id`)	ObjectId timestamp	TIMESTAMP
`total_cents`	Int64	NUMERIC
`total`	Decimal128	NUMERIC(12,2)

Code.

db.orders.aggregate([
  { $project: {
      _id: 0,
      order_id:   { $toString: "$_id" },              // ObjectId -> hex string
      created_at: { $toDate:   "$_id" },              // extract the embedded timestamp
      total:      { $toDecimal: { $divide: ["$total_cents", 100] } },
      status:     1,
      customer_id:{ $toString: "$customer_id" }
  }}
]);

Step-by-step explanation.

$toString: "$_id" converts the 12-byte ObjectId into its 24-character hex form — the stable string key a relational warehouse expects as a primary key.
$toDate: "$_id" is a MongoDB idiom: converting an ObjectId to a date extracts the 4-byte creation timestamp baked into it, giving you a free "created at" even when the document never stored one explicitly.
$divide: ["$total_cents", 100] turns integer cents into a major-unit amount, and $toDecimal wraps it so the value lands as exact NUMERIC, not a lossy binary float.
Setting _id: 0 and listing only the fields you want makes the projection explicit — the pipeline no longer depends on whatever incidental fields the application happens to write, which is the defense against schema drift.
Every extraction pipeline should end with a $project like this: it pins the contract between MongoDB's flexible documents and the warehouse's fixed columns.

Output.

order_id	created_at	total	status
6650b0…0001	2026-08-14T10:22:00Z	42.00	paid
6650b0…0002	2026-08-14T10:24:11Z	18.50	paid

Rule of thumb. Never trust raw BSON types in a warehouse feed. End every extraction pipeline with an explicit $project that $toStrings ObjectIds, $toDates where you need timestamps, and $toDecimals money — coerce at the source, not in the warehouse.

Worked example — polymorphic and versioned documents

Detailed explanation. Because collections are schema-flexible, one events collection often holds several shapes distinguished by a type field, and documents accumulate a schema_version as the application evolves. A pipeline must branch on shape rather than assume one. Walk through handling a polymorphic collection defensively.

Polymorphism. events holds click, purchase, and refund documents with overlapping but not identical fields.
Versioning. A schema_version field lets the pipeline apply the right field mapping per generation.
Defense. $ifNull and $switch normalize missing or renamed fields into one clean shape.

Question. Normalize a polymorphic events collection into a uniform (event_id, type, amount, occurred_at) shape regardless of version.

Input.

type	v1 amount field	v2 amount field	has occurred_at?
purchase	`value_cents`	`amount_cents`	v2 only
refund	`value_cents`	`amount_cents`	v2 only
click	(none)	(none)	v2 only

Code.

db.events.aggregate([
  { $addFields: {
      // Coalesce the renamed field across schema versions
      amount_cents: { $ifNull: ["$amount_cents", "$value_cents"] },
      // Backfill occurred_at from the _id timestamp for v1 docs
      occurred_at:  { $ifNull: ["$occurred_at", { $toDate: "$_id" }] }
  }},
  { $project: {
      _id: 0,
      event_id: { $toString: "$_id" },
      type: 1,
      amount: {
        $switch: {
          branches: [
            { case: { $in: ["$type", ["purchase"]] }, then: { $divide: ["$amount_cents", 100] } },
            { case: { $in: ["$type", ["refund"]]   }, then: { $multiply: [{ $divide: ["$amount_cents", 100] }, -1] } }
          ],
          default: 0          // clicks carry no amount
        }
      },
      occurred_at: 1
  }}
]);

Step-by-step explanation.

$ifNull: ["$amount_cents", "$value_cents"] coalesces the v2 field name over the v1 name, so both generations produce one amount_cents regardless of when the document was written.
$ifNull: ["$occurred_at", { $toDate: "$_id" }] backfills a timestamp for old documents that predate the explicit field — again leaning on the ObjectId's embedded creation time.
$switch branches on type: purchases stay positive, refunds are negated so a later $group can sum net revenue directly, and clicks default to 0.
The final $project collapses three document shapes into one uniform row shape. Downstream stages (and the warehouse) now see a single stable schema, decoupled from the source's polymorphism.
This defensive pattern — coalesce, backfill, branch, project — is the everyday reality of aggregating a schema-flexible collection; skipping it is how "the numbers don't match" bugs enter a pipeline.

Output.

event_id	type	amount	occurred_at
66…aa01	purchase	42.00	2026-08-14T10:22:00Z
66…aa02	refund	-18.50	2026-08-14T10:31:00Z
66…aa03	click	0	2026-08-14T10:33:00Z

Rule of thumb. Treat a schema-flexible collection as polymorphic until proven otherwise. Normalize with $ifNull (renamed fields), $toDate: "$_id" (missing timestamps), and $switch (per-type logic) before any $group, so every downstream stage sees one clean shape.

Data engineering interview question on the document model

A senior interviewer might open with: "You're modelling a blogging platform in MongoDB — posts, the comments on each post, and the authors who write both. Comments can number in the thousands on a popular post; authors write many posts. Walk me through what you'd embed, what you'd reference, why, and how you'd read a post with its recent comments and the author's display name."

Solution Using an embed-plus-reference hybrid keyed by ObjectId

// authors — referenced by both posts and comments (shared entity)
db.authors.insertOne({
  _id: ObjectId("6651000000000000000000a1"),
  display_name: "Priya N.",
  joined_at: ISODate("2025-02-01T00:00:00Z")
});

// posts — embed a *bounded* preview of recent comments; reference the author
db.posts.insertOne({
  _id: ObjectId("6651000000000000000000b1"),
  author_id: ObjectId("6651000000000000000000a1"),   // reference (shared)
  title: "Modelling in MongoDB",
  body_md: "…",
  created_at: ISODate("2026-08-20T09:00:00Z"),
  comment_count: 3120,                                 // denormalized counter
  recent_comments: [                                   // embedded, capped at ~20
    { author_id: ObjectId("…c1"), text: "Great post", at: ISODate("2026-08-20T09:05:00Z") }
  ]
});

// comments — the full, unbounded set lives in its own collection
db.comments.insertOne({
  _id: ObjectId(),
  post_id: ObjectId("6651000000000000000000b1"),      // reference to parent
  author_id: ObjectId("6651000000000000000000c1"),
  text: "Great post",
  created_at: ISODate("2026-08-20T09:05:00Z")
});

// Read a post with its author display name and most-recent comments
db.posts.aggregate([
  { $match: { _id: ObjectId("6651000000000000000000b1") } },
  { $lookup: {
      from: "authors", localField: "author_id",
      foreignField: "_id", as: "author"
  }},
  { $unwind: "$author" },
  { $project: {
      title: 1, created_at: 1, comment_count: 1,
      author_name: "$author.display_name",
      recent_comments: { $slice: ["$recent_comments", 5] }
  }}
]);

Step-by-step trace.

Step	Input	Action	Output
Model authors	shared entity	one `authors` doc per person	referenced by `_id`
Model posts	bounded preview	embed `recent_comments` (~20) + `comment_count`	fast post render
Model comments	unbounded set	own collection keyed by `post_id`	no 16 MB risk
`$match`	post `_id`	index seek to one post	1 document
`$lookup` + `$unwind`	`author_id`	join the single author, flatten	post + author_name
`$project` + `$slice`	full doc	keep title, count, 5 recent comments	render payload

After modelling, rendering a post is one indexed $match, a single-document $lookup on the author, and a $slice of the embedded preview — no scan over the thousands of full comments. The unbounded comment set lives safely in its own collection; the post document stays small and read-cheap.

Output:

title	author_name	comment_count	recent_comments (shown)
Modelling in MongoDB	Priya N.	3120	5 most recent

Why this works — concept by concept:

Embed the bounded, reference the unbounded — recent_comments is capped at ~20 and embedded so the post renders in one read; the full comment set is unbounded and lives in its own collection, keeping every post document far below the 16 MB cap.
Reference shared entities by ObjectId — an author writes many posts and many comments, so the author is stored once and referenced by _id; a display-name change updates exactly one document instead of thousands of copies.
Denormalized counters — comment_count is stored on the post so the common "3,120 comments" render never scans the comments collection; a change stream or write-time increment keeps it fresh.
$lookup only where it's cheap — the author join touches exactly one document because the post already resolved to one; the expensive many-row join over comments is avoided entirely by the embedded preview.
Cost — read of a post is O(1) index seek + O(1) author lookup + O(k) slice of a k≈5 preview, versus O(comments) for a naive "join all comments" design. Writes to add a comment are O(1) insert plus an O(1) counter/preview update.

Database
Topic — database
Document-model and data-modelling problems

Practice →

JSON Topic — json JSON and nested-document query problems

Practice →

2. Aggregation pipeline stages ($match/$group/$project)

The pipeline is an ordered conveyor — `$match` filters, `$group` folds, `$project` reshapes, and order is the program

The mental model in one line: the mongodb aggregation pipeline is an ordered array of stages passed to db.collection.aggregate([...]), where each stage consumes the document stream from the stage before it and emits a transformed stream — $match filters documents (and can use an index when it runs first), $group folds many documents into one per key using accumulators, and $project / $addFields reshape each document — and because the stages run in the order you write them, stage ordering is not cosmetic: it decides both correctness and cost. If you know SQL, $match is WHERE, $group is GROUP BY with aggregate functions, $project is the SELECT list, $sort is ORDER BY, and $limit is LIMIT — but written as a sequence you control explicitly.

The core stages every pipeline uses.

$match. Filters the stream with the same query syntax as find(). Placed first, it can use an index and shrink the stream before any expensive stage runs. Placed after a $group, it filters the grouped output (the equivalent of SQL HAVING).
$group. Folds documents sharing the same _id expression into one output document, computing accumulators — $sum, $avg, $min, $max, $push, $addToSet, $first, $last. The _id of a $group is the grouping key; _id: null groups everything into one total.
$project and $addFields. $project replaces the document with exactly the fields you list (inclusion/exclusion + computed fields); $addFields (alias $set) adds computed fields while keeping everything else. Use $project to shape the final contract, $addFields to enrich mid-pipeline.
$sort, $limit, $skip, $count. Ordering, top-N, pagination, and cardinality. $sort + $limit together are special-cased by the engine into an efficient top-N that avoids sorting the whole stream.

The optimization rules the engine applies.

$match pushdown. The optimizer moves a $match as early as possible — ideally before a $project or $lookup — so filtering happens on the smallest possible stream and an index can serve it.
Index eligibility ends at the first transforming stage. Only a $match (or $sort) at the front of the pipeline can use a collection index. Once a $group or $project has transformed the documents, later $match stages filter in memory.
$sort + $limit coalescing. When a $limit follows a $sort, the engine keeps only the top-N in a bounded heap instead of sorting everything — turning an O(n log n) full sort into O(n log k).
The 100 MB memory limit. A blocking stage ($group, $sort) that exceeds 100 MB errors unless you set allowDiskUse: true, which spills to disk. Index-served sorts avoid the limit entirely.

What interviewers listen for.

Do you put $match first and explain that it enables an index and shrinks the stream? — required answer.
Do you name $group accumulators ($sum, $avg, $push) rather than hand-waving "it groups"? — senior signal.
Do you distinguish $project (replace) from $addFields (augment)? — a real-experience tell.
Do you know $match after $group is HAVING and that only a leading $match uses an index? — senior signal.

Worked example — revenue per status with `$match` + `$group`

Detailed explanation. The everyday pipeline: filter orders to a date window with $match, then fold them into one row per status with $group, summing revenue and counting orders. This is SELECT status, SUM(total), COUNT(*) FROM orders WHERE created_at >= … GROUP BY status written as stages.

Filter. $match on created_at and status != cancelled.
Fold. $group by status, $sum revenue, $sum: 1 for count.
Shape. $project to rename _id to status and round money.

Question. Compute total revenue and order count per status for orders created on or after 2026-08-01.

Input.

_id	status	total_cents	created_at
o1	paid	4200	2026-08-14
o2	paid	1850	2026-08-15
o3	refunded	900	2026-08-16
o4	paid	3000	2026-07-30

Code.

db.orders.aggregate([
  { $match: {
      created_at: { $gte: ISODate("2026-08-01T00:00:00Z") },
      status: { $ne: "cancelled" }
  }},
  { $group: {
      _id: "$status",                       // grouping key
      revenue_cents: { $sum: "$total_cents" },
      order_count:   { $sum: 1 }
  }},
  { $project: {
      _id: 0,
      status: "$_id",
      revenue: { $round: [{ $divide: ["$revenue_cents", 100] }, 2] },
      order_count: 1
  }},
  { $sort: { revenue: -1 } }
]);

Step-by-step explanation.

$match runs first so it can use an index on created_at and drop o4 (July) and any cancelled orders before anything expensive happens. Filtering early is the single biggest performance lever in a pipeline.
$group with _id: "$status" creates one output document per distinct status. $sum: "$total_cents" accumulates revenue; $sum: 1 counts documents — the idiomatic MongoDB COUNT(*).
After $group, the stream is tiny (one doc per status), so the following stages are cheap regardless of the source size.
$project renames the group key _id to a friendly status, divides cents to dollars, and $rounds to two places — coercing the money into a warehouse-ready shape.
$sort: { revenue: -1 } orders the small grouped result; because it follows a $group it sorts in memory, but the input is only a handful of rows so the 100 MB limit is irrelevant.

Output.

status	revenue	order_count
paid	60.50	2
refunded	9.00	1

Rule of thumb. Always lead with $match so an index shrinks the stream, and count with $sum: 1. Put the $project that shapes money and renames _id after the $group, when the stream is already small.

Worked example — `$project` vs `$addFields` and computed fields

Detailed explanation. The two reshaping stages look similar but differ in one way that trips up newcomers: $project outputs only the fields you name (plus _id unless you exclude it), while $addFields keeps the whole document and layers new fields on top. Use $addFields to compute intermediate values you still need downstream; use $project to finalize the output contract.

$addFields. Adds net_cents mid-pipeline while retaining total_cents, discount_cents, etc.
$project. Emits the final (order_id, net) shape and drops everything else.
Computed expressions. $subtract, $multiply, $cond, $dateToString build derived fields.

Question. Add a net_cents field (total minus discount) mid-pipeline, then project a clean final row with a formatted date.

Input.

_id	total_cents	discount_cents	created_at
o1	4200	200	2026-08-14T10:22Z
o2	1850	0	2026-08-15T14:05Z

Code.

db.orders.aggregate([
  { $addFields: {                                  // augment, keep everything
      net_cents: { $subtract: ["$total_cents", { $ifNull: ["$discount_cents", 0] }] },
      day: { $dateToString: { format: "%Y-%m-%d", date: "$created_at" } }
  }},
  { $project: {                                    // finalize the contract
      _id: 0,
      order_id: { $toString: "$_id" },
      day: 1,
      net: { $round: [{ $divide: ["$net_cents", 100] }, 2] },
      is_free: { $cond: [{ $eq: ["$net_cents", 0] }, true, false] }
  }}
]);

Step-by-step explanation.

$addFields computes net_cents with $subtract, guarding a possibly-missing discount_cents via $ifNull. Because $addFields keeps the original fields, later stages could still reference total_cents if needed.
$dateToString derives a day bucket string — the document-store equivalent of DATE_TRUNC('day', ...), useful for daily grouping.
$project then discards everything except the fields the consumer wants, converting _id to a string and rounding money. This is where the flexible document becomes a fixed row.
$cond: [{ $eq: [...] }, true, false] shows an inline conditional — the pipeline's ternary — producing a boolean is_free flag without a separate stage.
The division of labour is the lesson: $addFields for intermediate enrichment you may reuse, $project for the final, minimal, typed output shape.

Output.

order_id	day	net	is_free
o1	2026-08-14	40.00	false
o2	2026-08-15	18.50	false

Rule of thumb. Reach for $addFields when you need a computed value and still want the rest of the document; reach for $project when you want to lock the output down to a specific, minimal shape. Never use $project mid-pipeline to add one field if it silently drops fields a later stage needs.

Worked example — multi-stage top-N per customer

Detailed explanation. Combining stages produces real reports: filter to a window, group per customer, sort by spend, and keep the top N. The $sort + $limit pair is special-cased into an efficient top-N, so this scales even over large collections when $match is index-served.

Window. $match last 30 days.
Fold. $group per customer_id, sum revenue, count orders.
Rank. $sort by revenue descending, $limit 3.

Question. Find the top 3 customers by revenue over the last 30 days, with their order counts.

Input.

_id	customer_id	total_cents	created_at
o1	c1	4200	2026-08-30
o2	c2	9900	2026-08-29
o3	c1	1800	2026-08-28
o4	c3	500	2026-08-27

Code.

db.orders.aggregate([
  { $match: { created_at: { $gte: ISODate("2026-08-06T00:00:00Z") } } },
  { $group: {
      _id: "$customer_id",
      revenue_cents: { $sum: "$total_cents" },
      order_count:   { $sum: 1 }
  }},
  { $sort:  { revenue_cents: -1 } },       // coalesced with the $limit below
  { $limit: 3 },
  { $project: {
      _id: 0,
      customer_id: { $toString: "$_id" },
      revenue: { $divide: ["$revenue_cents", 100] },
      order_count: 1
  }}
]);

Step-by-step explanation.

$match on created_at is index-served and drops everything outside the 30-day window before grouping.
$group per customer_id produces one row per customer with summed revenue and an order count.
The engine coalesces the adjacent $sort + $limit into a bounded top-N: it maintains a heap of the 3 highest-revenue customers rather than sorting the entire grouped set, which matters when there are millions of customers.
$limit: 3 keeps only the leaders; because it is fused with the sort, memory stays O(3), not O(customers).
The trailing $project formats the survivors — money to dollars, _id to a string — leaving a clean, ranked, warehouse-ready result.

Output.

customer_id	revenue	order_count
c2	99.00	1
c1	60.00	2
c3	5.00	1

Rule of thumb. Put $sort immediately before $limit so the engine coalesces them into a memory-bounded top-N. Keep the shaping $project last, after the stream is already reduced to the rows you will actually return.

Data engineering interview question on the aggregation pipeline

A senior interviewer might ask: "Off a 40-million-document orders collection, produce a daily report for the last 30 days: for each (day, customer) give total revenue and order count, then return only the top 10 customer-days by revenue. Write the pipeline, explain the stage order, and tell me which stage should use an index."

Solution Using $match → $group → $sort → $limit with a date-bucket key

db.orders.aggregate([
  // 1. Index-served filter — shrink 40M docs to the 30-day window first
  { $match: {
      created_at: { $gte: ISODate("2026-08-06T00:00:00Z"),
                    $lt:  ISODate("2026-09-05T00:00:00Z") },
      status: { $in: ["paid", "refunded"] }
  }},
  // 2. Bucket each doc into a (day, customer) key
  { $addFields: {
      day: { $dateToString: { format: "%Y-%m-%d", date: "$created_at" } }
  }},
  // 3. Fold to one row per (day, customer)
  { $group: {
      _id: { day: "$day", customer_id: "$customer_id" },
      revenue_cents: { $sum: "$total_cents" },
      order_count:   { $sum: 1 }
  }},
  // 4. Rank and keep the top 10 (coalesced top-N)
  { $sort:  { revenue_cents: -1 } },
  { $limit: 10 },
  // 5. Finalize the output contract
  { $project: {
      _id: 0,
      day: "$_id.day",
      customer_id: { $toString: "$_id.customer_id" },
      revenue: { $round: [{ $divide: ["$revenue_cents", 100] }, 2] },
      order_count: 1
  }}
], { allowDiskUse: true });

Step-by-step trace.

Step	Stage	Effect on the stream	Cost note
1	`$match`	40M → ~1.2M (30-day window)	uses index on `{status, created_at}`
2	`$addFields`	add `day` bucket	streaming, cheap
3	`$group`	~1.2M → N (day×customer)	blocking; `allowDiskUse` if >100 MB
4	`$sort`+`$limit`	N → 10	coalesced top-N heap
5	`$project`	shape 10 rows	trivial

The $match is the load-bearing stage: served by a compound index on {status: 1, created_at: 1}, it turns a 40-million-document scan into a bounded index range, so the $group folds ~1.2M documents rather than 40M. The $sort + $limit never materializes the full grouped set — it keeps a heap of 10.

Output:

day	customer_id	revenue	order_count
2026-08-29	c2	990.00	6
2026-08-14	c1	812.50	9
2026-08-22	c7	640.00	4

Why this works — concept by concept:

$match first (index-served) — leading the pipeline with $match lets the compound index on {status, created_at} restrict the scan to the 30-day window, so every later stage processes ~1.2M documents instead of 40M. This is the single decision that makes the report tractable.
$group by a composite key — _id: { day, customer_id } folds the stream to one row per customer-day; accumulators $sum revenue and count in a single pass over the filtered stream.
$sort + $limit coalescing — placing $limit right after $sort triggers the top-N optimization: the engine keeps a bounded heap of 10 rather than sorting the whole grouped set, holding memory flat.
allowDiskUse safety valve — a $group over 1.2M documents can exceed the 100 MB in-memory limit; allowDiskUse: true lets the blocking stage spill to disk instead of erroring, trading a little speed for reliability.
Cost — O(range) index seek for the filter, one streaming pass for the $group (O(m) over the m filtered docs), and O(N log 10) for the top-N, versus O(40M) for a naive full scan. The index turns the dominant term from collection size into result-window size.

Aggregation
Topic — aggregation
Aggregation pipeline and group-by problems

Practice →

Database Topic — database Database query and reporting problems

Practice →

3. $lookup joins & $unwind

`$lookup` is a left outer join into an array; `$unwind` flattens that array into one document per element

The mental model in one line: $lookup performs a left outer join from the current collection into another, attaching every matched foreign document as an array on a new field, and $unwind then expands that array so the stream carries one document per matched element — together they let a document store answer the relational questions embedding cannot, at the cost of a nested-loop join that you must back with an index on the joined-to collection's foreignField. Where the relational world joins by default and denormalizes reluctantly, MongoDB embeds by default and joins reluctantly — but when a query spans two collections (orders and the customers who placed them), $lookup + $unwind is the tool, and understanding its cost is what separates a fast pipeline from a cluster-melting one.

The two forms of $lookup.

Equality form. { from, localField, foreignField, as } joins where localField == foreignField. Simple, and the engine can use an index on foreignField. This is the everyday form for a foreign-key join.
Sub-pipeline (correlated) form. { from, let: { … }, pipeline: [ … ], as } runs a whole aggregation against the foreign collection, with let binding local values into $expr conditions. Use it to filter, project, or aggregate the joined side — e.g. "only the customer's active address," or "the sum of this order's refunds."
The as field is always an array. Even a one-to-one join lands the match in a single-element array; $unwind (or $arrayElemAt) turns it into a scalar/object.

The mechanics of $unwind.

Array to rows. $unwind: "$items" emits one output document per element of items, copying the parent fields onto each — exactly SQL's CROSS JOIN LATERAL / "explode."
Empty and missing arrays. By default $unwind drops documents whose array is empty or missing. { path: "$items", preserveNullAndEmptyArrays: true } keeps them (the array field becomes null) — the difference between an inner and a left join semantics after a $lookup.
includeArrayIndex. Optionally emits the element's position, useful when order matters (line 1, line 2, …).

Performance caveats you must state.

$lookup is a nested loop. For each input document it probes the foreign collection. Without an index on foreignField, each probe is a collection scan — an O(n·m) disaster. Index the foreignField.
$unwind multiplies the stream. Unwinding a 50-element array turns 1 document into 50; do it after you have filtered down, and group back afterward if you only need aggregates.
Filter before you join. A $match before $lookup shrinks the number of probes; a $match inside a sub-pipeline shrinks each probe's result.

What interviewers listen for.

Do you call $lookup a left outer join and note the result is an array? — required answer.
Do you say "index the foreignField" because $lookup is a nested loop? — senior signal.
Do you know $unwind drops empty arrays unless preserveNullAndEmptyArrays is set? — a real-experience tell.
Do you reach for the sub-pipeline form to filter/aggregate the joined side instead of joining everything then filtering? — senior signal.

Worked example — basic `$lookup` from orders to customers

Detailed explanation. The foreign-key join: each order carries a customer_id; join to customers._id to attach the customer, then flatten the single-element array to an object. This is orders LEFT JOIN customers ON orders.customer_id = customers._id.

Join. Equality form on customer_id → _id.
Flatten. $unwind the single-element customer array.
Shape. $project the customer's name onto the order.

Question. Attach each order's customer name and country using a $lookup.

Input.

orders:

_id	customer_id	total_cents
o1	c1	4200
o2	c9	1850

customers:

_id	name	country
c1	Ana	US
c9	Bo	CA

Code.

db.orders.aggregate([
  { $lookup: {
      from: "customers",
      localField: "customer_id",
      foreignField: "_id",          // index this field on customers
      as: "customer"
  }},
  { $unwind: "$customer" },          // single-element array -> object
  { $project: {
      _id: 0,
      order_id: { $toString: "$_id" },
      total: { $divide: ["$total_cents", 100] },
      customer_name: "$customer.name",
      customer_country: "$customer.country"
  }}
]);

Step-by-step explanation.

$lookup matches each order's customer_id against customers._id, attaching the matched customer as a one-element array on customer. With an index on customers._id (the default _id index), each probe is an O(log n) seek, not a scan.
$unwind: "$customer" flattens that single-element array into an object. Because every order here has a matching customer, no documents are dropped; if an order referenced a deleted customer, the default $unwind would drop it (use preserveNullAndEmptyArrays to keep it as a left join).
$project lifts customer.name and customer.country up to top-level fields and formats money — the flattened, joined row is now a clean record.
The order of stages matters: joining before projecting keeps the pipeline readable, and because orders is the driving side, the number of nested-loop probes equals the number of orders.
This is the bread-and-butter enrichment pattern: a fact collection (orders) enriched with a dimension collection (customers) via an indexed $lookup.

Output.

order_id	total	customer_name	customer_country
o1	42.00	Ana	US
o2	18.50	Bo	CA

Rule of thumb. For a foreign-key join, use the equality $lookup and make sure the foreignField is indexed (joining to _id gets this for free). Follow with $unwind to flatten one-to-one matches into objects; add preserveNullAndEmptyArrays: true when you need left-join semantics.

Worked example — `$unwind` an embedded array then group back

Detailed explanation. When line items are embedded (section 1), you often need per-SKU numbers across all orders. $unwind explodes the embedded line_items array into one document per line, and a following $group folds those lines by SKU. No $lookup needed — the join is already "done" by embedding.

Explode. $unwind: "$line_items".
Fold. $group by line_items.sku, sum quantity and revenue.
Rank. $sort by revenue.

Question. Compute total quantity and revenue per SKU across all embedded line items.

Input.

_id	line_items
o1	[{sku:A, qty:2, unit_cents:1500}, {sku:B, qty:1, unit_cents:1200}]
o2	[{sku:A, qty:1, unit_cents:1500}]

Code.

db.orders.aggregate([
  { $unwind: "$line_items" },                 // one doc per line item
  { $group: {
      _id: "$line_items.sku",
      qty:          { $sum: "$line_items.qty" },
      revenue_cents:{ $sum: { $multiply: ["$line_items.qty", "$line_items.unit_cents"] } }
  }},
  { $project: {
      _id: 0,
      sku: "$_id",
      qty: 1,
      revenue: { $divide: ["$revenue_cents", 100] }
  }},
  { $sort: { revenue: -1 } }
]);

Step-by-step explanation.

$unwind: "$line_items" turns each order into as many documents as it has line items, copying the order's other fields onto each. Order o1 (2 lines) becomes 2 documents; o2 becomes 1.
$group by line_items.sku folds those exploded lines by product. $sum: "$line_items.qty" totals units; the $multiply inside $sum computes per-line revenue (qty × unit price) before summing.
Because the join was pre-materialized by embedding, there is no $lookup and no nested loop — $unwind + $group over an indexed/scanned collection is the whole cost.
$project reshapes to (sku, qty, revenue) and converts cents; $sort ranks SKUs by revenue.
The pattern generalizes: explode the array, group by the element key, aggregate. When you only need aggregates, always group back down after unwinding so the multiplied stream does not flow downstream.

Output.

sku	qty	revenue
A	3	45.00
B	1	12.00

Rule of thumb. $unwind the array, $group by the element key, then aggregate — and group back down immediately, because an unwound stream is N× larger than the source. If you later need the parent shape, $group with $push rebuilds arrays.

Worked example — correlated sub-pipeline `$lookup`

Detailed explanation. The sub-pipeline form joins with a filter or aggregation applied to the foreign side, using let to bind local fields and $expr to compare them. Here, for each customer, join only their paid orders and compute the customer's lifetime paid revenue — filtering the joined side instead of joining everything and filtering after.

Bind. let: { cid: "$_id" } exposes the customer id to the sub-pipeline.
Filter foreign. Sub-pipeline $match on status: "paid" and $expr customer_id == cid.
Aggregate foreign. Sub-pipeline $group sums paid revenue.

Question. For each customer, attach their lifetime paid revenue using a correlated sub-pipeline $lookup.

Input.

customers: c1, c9. orders: o1(c1, paid, 4200), o2(c1, cancelled, 999), o3(c9, paid, 1850).

Code.

db.customers.aggregate([
  { $lookup: {
      from: "orders",
      let: { cid: "$_id" },
      pipeline: [
        { $match: { $expr: { $and: [
            { $eq: ["$customer_id", "$$cid"] },   // correlate to the local customer
            { $eq: ["$status", "paid"] }          // filter the joined side
        ]}}},
        { $group: { _id: null, paid_cents: { $sum: "$total_cents" } } }
      ],
      as: "paid"
  }},
  { $addFields: {
      lifetime_paid: {
        $round: [{ $divide: [{ $ifNull: [{ $arrayElemAt: ["$paid.paid_cents", 0] }, 0] }, 100] }, 2]
      }
  }},
  { $project: { _id: 0, customer_id: { $toString: "$_id" }, lifetime_paid: 1 } }
]);

Step-by-step explanation.

let: { cid: "$_id" } binds each customer's _id into a variable $$cid that the sub-pipeline can reference — this is what makes the join correlated (per-customer), not a cartesian product.
The sub-pipeline's $match with $expr filters orders to that customer's paid rows only. Filtering inside the join means only relevant orders are ever materialized, far cheaper than joining all orders then filtering.
The sub-pipeline $group with _id: null collapses those paid orders into a single total per customer, so the as: "paid" array holds at most one document.
$arrayElemAt: ["$paid.paid_cents", 0] pulls that single total out of the array, $ifNull defaults customers with no paid orders to 0, and $round/$divide format the money.
The result: one clean row per customer with lifetime paid revenue, computed by pushing the filter and aggregation into the join — the correlated sub-pipeline is the tool for "join, but only the matching, filtered, aggregated foreign rows."

Output.

customer_id	lifetime_paid
c1	42.00
c9	18.50

Rule of thumb. Use the sub-pipeline $lookup (let + $expr) whenever you need to filter, project, or aggregate the joined collection — it shrinks each probe's result at the source. For a plain foreign-key attach with no foreign-side filtering, the simpler equality form is faster and index-friendlier.

Data engineering interview question on $lookup and $unwind

A senior interviewer might ask: "You have orders with embedded line_items and a separate customers collection. Produce one row per line item that includes the order id, the SKU, the line revenue, and the customer's name and country. Explain where the join happens, where the explosion happens, and how you keep it from being O(n·m)."

Solution Using $lookup on customers + $unwind on line_items with an index

db.orders.aggregate([
  // 1. Narrow first so we probe customers fewer times
  { $match: { status: "paid",
              created_at: { $gte: ISODate("2026-08-01T00:00:00Z") } } },

  // 2. Join the customer dimension (indexed foreignField = _id)
  { $lookup: {
      from: "customers",
      localField: "customer_id",
      foreignField: "_id",
      as: "customer"
  }},
  { $unwind: { path: "$customer", preserveNullAndEmptyArrays: true } }, // left join

  // 3. Explode the embedded line items into one doc per line
  { $unwind: "$line_items" },

  // 4. Shape one row per line item
  { $project: {
      _id: 0,
      order_id: { $toString: "$_id" },
      sku: "$line_items.sku",
      line_revenue: { $round: [
        { $divide: [{ $multiply: ["$line_items.qty", "$line_items.unit_cents"] }, 100] }, 2 ] },
      customer_name:    "$customer.name",
      customer_country: "$customer.country"
  }}
]);

Step-by-step trace.

Step	Stage	Stream effect	Cost control
1	`$match`	drop unpaid / old orders	index on `{status, created_at}`
2	`$lookup`	attach `customer` array	index on `customers._id` → O(log n) probe
2b	`$unwind` customer	flatten to object (left join)	`preserveNullAndEmptyArrays`
3	`$unwind` line_items	1 order → k lines	done after narrowing
4	`$project`	one row per line item	streaming

The join to customers is a nested loop, but each probe is an indexed _id seek, so it is O(orders · log customers), not O(orders · customers). The line-item explosion happens last, after $match has already reduced the driving set, so the N× multiplication applies to a small stream. Filtering first, joining on an index, exploding last is the recipe.

Output:

order_id	sku	line_revenue	customer_name	customer_country
o1	TSHIRT-BLK-M	30.00	Ana	US
o1	STICKER-PACK	12.00	Ana	US
o2	TSHIRT-BLK-M	15.00	Bo	CA

Why this works — concept by concept:

$lookup is a left outer join — it attaches matched customers as an array; with preserveNullAndEmptyArrays on the $unwind, orders whose customer was deleted still survive with null customer fields, exactly matching SQL left-join semantics.
Index the foreignField — joining on customers._id uses the built-in _id index, so each of the nested-loop probes is an O(log n) seek instead of a full collection scan; this is the difference between a fast join and an O(n·m) meltdown.
$unwind after narrowing — exploding line_items multiplies the stream by k; doing it after $match (and after the single-row customer join) means the multiplication hits a small, already-filtered set.
Filter → join → explode → shape — the stage order is the optimization: shrink the driving set, join on an index, explode late, project last. Reordering these degrades cost, not correctness.
Cost — O(range) for the indexed filter, O(m · log c) for the customer join over m filtered orders and c customers, and O(m · k) for the line explosion — versus O(orders · customers) for an unindexed join or an early explosion. Indexing and ordering keep every term bounded.

Aggregation
Topic — aggregation
$lookup, $unwind, and join problems

Practice →

JSON Topic — json JSON array and nested-join problems

Practice →

4. Indexing & performance

Indexes turn a COLLSCAN into an IXSCAN — order compound keys Equality → Sort → Range

The mental model in one line: MongoDB indexes are B-trees over one or more fields, and the difference between a query that reads three documents and one that reads fifty million is whether the planner can serve it from an index (an IXSCAN) or must examine every document (a COLLSCAN) — for a compound index the field order follows the ESR rule (Equality fields first, then the Sort field, then Range fields), and explain() is the instrument that tells you which path the planner chose and how many documents it had to examine to return your results. Every performance conversation about an aggregation reduces to two numbers from explain(): totalDocsExamined and nReturned. When they are far apart, you are scanning; when they are close, your index is doing its job.

The index types a data engineer reaches for.

Single-field. { created_at: 1 } — the default building block. _id is always indexed.
Compound. { status: 1, created_at: -1 } — one B-tree over several fields, ordered left to right. Serves queries that filter on a prefix of the fields.
Multikey. An index on an array field ({ "line_items.sku": 1 }) automatically indexes every element — this is what makes embedded arrays queryable.
Partial and TTL. A partial index covers only documents matching a filter ({ status: "paid" }), saving space; a TTL index ({ created_at: 1 }, expireAfterSeconds) auto-deletes old documents — handy for event collections.

The ESR rule for compound-index order.

Equality first. Fields matched with $eq / $in go leftmost, so the B-tree seeks directly to the matching range.
Sort next. The field(s) the query sorts by come after equality, so the index returns rows already in order — no in-memory sort, no 100 MB limit.
Range last. Inequality fields ($gte, $lt) go last, because a range scan on an earlier field would break the ordering the sort relies on.
Why order matters. A compound index only serves a query that uses a left prefix of its fields; { status, created_at } serves a filter on status alone or status + created_at, but not created_at alone.

Reading explain().

winningPlan.stage. IXSCAN (good — index used) vs COLLSCAN (every document examined). For aggregations, run db.coll.explain("executionStats").aggregate([...]).
totalDocsExamined vs nReturned. The ratio is your efficiency: examining 50M to return 10 is a scan; examining 12 to return 10 is a healthy index.
totalKeysExamined. Index entries read; close to nReturned means the index is selective.
Covered queries. When the index contains every field the query needs (filter + projection) and you exclude _id, the planner never fetches the document — totalDocsExamined is 0.

What interviewers listen for.

Do you name the ESR rule for ordering compound-index fields? — senior signal.
Do you read totalDocsExamined vs nReturned from explain() rather than guessing? — required answer.
Do you know a compound index serves only a left prefix of its fields? — a real-experience tell.
Do you mention covered queries (index answers the whole query, no document fetch)? — senior signal.

Worked example — COLLSCAN to IXSCAN with `explain()`

Detailed explanation. The diagnostic workflow: run explain("executionStats") on a slow aggregation, see a COLLSCAN with totalDocsExamined equal to the whole collection, add the right index, and confirm the plan flips to IXSCAN with totalDocsExamined near nReturned.

Before. No index on status; $match scans everything.
After. Index on { status: 1 }; $match seeks the matching range.
Instrument. explain("executionStats") reports the two counts.

Question. Diagnose and fix a $match: { status: "paid" } aggregation that scans all 50M documents.

Input.

Metric	Before index	After index
winningPlan	COLLSCAN	IXSCAN
totalDocsExamined	50,000,000	1,240,000
nReturned	1,240,000	1,240,000
elapsed	~40 s	~0.9 s

Code.

// 1. Diagnose — what does the planner do today?
db.orders.explain("executionStats").aggregate([
  { $match: { status: "paid" } },
  { $group: { _id: "$customer_id", n: { $sum: 1 } } }
]);
// winningPlan.stage: "COLLSCAN"  ->  totalDocsExamined: 50000000

// 2. Fix — index the filtered field
db.orders.createIndex({ status: 1 });

// 3. Confirm — plan flips to IXSCAN
db.orders.explain("executionStats").aggregate([
  { $match: { status: "paid" } },
  { $group: { _id: "$customer_id", n: { $sum: 1 } } }
]);
// winningPlan.inputStage.stage: "IXSCAN"  ->  totalDocsExamined ≈ nReturned

Step-by-step explanation.

explain("executionStats") runs the pipeline and reports actual execution numbers. A COLLSCAN with totalDocsExamined: 50000000 is the smoking gun: the planner had no index for status, so it read every document.
createIndex({ status: 1 }) builds a B-tree on status. Now the planner can seek directly to the "paid" entries instead of scanning.
Re-running explain shows the $match served by an IXSCAN feeding the $group; totalDocsExamined drops from 50M to the ~1.24M paid orders — it now examines only the documents it returns.
The $group still processes 1.24M documents, but the collection is no longer scanned to find them — the index provides them directly, cutting wall time from ~40 s to ~0.9 s.
The workflow generalizes: never guess. Run explain, look at COLLSCAN/IXSCAN and the examined-vs-returned ratio, add the index that makes the leading $match seekable, and confirm.

Output.

Query	Plan	Docs examined	Time
status = paid (no index)	COLLSCAN	50,000,000	~40 s
status = paid (indexed)	IXSCAN	1,240,000	~0.9 s

Rule of thumb. When an aggregation is slow, run explain("executionStats") first and read totalDocsExamined vs nReturned. A COLLSCAN or a huge examined-to-returned ratio means the leading $match has no usable index — add one and confirm the plan flips to IXSCAN.

Worked example — compound index following the ESR rule

Detailed explanation. A query that filters on status (equality), sorts by created_at (sort), and ranges on total_cents (range) needs a compound index ordered E-S-R: { status: 1, created_at: -1, total_cents: 1 }. Ordered this way, the index serves the filter, returns rows already sorted (no in-memory sort), and range-scans last.

Equality. status = "paid" → leftmost.
Sort. created_at desc → middle, matching the index direction.
Range. total_cents >= 1000 → rightmost.

Question. Design one compound index that serves a filter + sort + range query without an in-memory sort.

Input.

Clause	Field	Role
`$match` eq	status	Equality
`$sort`	created_at desc	Sort
`$match` range	total_cents ≥ 1000	Range

Code.

// ESR: Equality (status) -> Sort (created_at) -> Range (total_cents)
db.orders.createIndex({ status: 1, created_at: -1, total_cents: 1 });

db.orders.explain("executionStats").aggregate([
  { $match: { status: "paid", total_cents: { $gte: 1000 } } },
  { $sort:  { created_at: -1 } },
  { $limit: 20 }
]);
// IXSCAN serves status=, walks created_at in -1 order (no SORT stage),
// applies total_cents range on the trailing key.

Step-by-step explanation.

status is an equality predicate, so it goes first: the index seeks straight to the "paid" sub-tree, discarding all other statuses without examining them.
created_at: -1 is the sort field and comes second, matching the query's $sort direction. Because the index already stores entries in that order within the "paid" sub-tree, the planner streams them out sorted — there is no separate SORT stage and thus no 100 MB memory risk.
total_cents is a range predicate and goes last. A range on an earlier key would scatter the sort order; keeping it last means the range is applied along the already-ordered scan.
With $limit: 20, the index-ordered scan can stop after 20 qualifying entries — a true top-N served entirely by the index.
Violating ESR (e.g. putting total_cents before created_at) forces the planner to either scan more of the index or add an in-memory SORT; explain would then show a SORT stage, the tell that the index order is wrong.

Output.

Aspect	With ESR index	Wrong order
Filter (status)	index seek	index seek
Sort (created_at)	index-provided	in-memory SORT stage
Range (total_cents)	trailing scan	scan then filter
Memory	O(limit)	up to 100 MB

Rule of thumb. Order a compound index Equality → Sort → Range. If explain still shows a SORT stage, your sort field is in the wrong position — move all equality fields ahead of it and the range fields behind it.

Worked example — covered query and multikey index

Detailed explanation. Two more levers. A covered query is answered entirely from the index — when the index holds every field the query filters and returns (and you exclude _id), MongoDB never touches the documents (totalDocsExamined: 0). A multikey index on an array field indexes each element, making $match/$unwind on embedded arrays fast.

Covered. Index { status: 1, customer_id: 1 }; project only those fields, exclude _id.
Multikey. Index { "line_items.sku": 1 } to seek line items by SKU.

Question. Build a covered query for (status, customer_id) and a multikey index for SKU lookups.

Input.

Goal	Index	Query shape
Covered	`{status:1, customer_id:1}`	match status, return customer_id, no `_id`
Multikey	`{"line_items.sku":1}`	match a SKU inside the array

Code.

// Covered query — index holds every field the query needs
db.orders.createIndex({ status: 1, customer_id: 1 });
db.orders.explain("executionStats").aggregate([
  { $match: { status: "paid" } },
  { $project: { _id: 0, customer_id: 1 } }   // only indexed fields, _id excluded
]);
// -> totalDocsExamined: 0   (answered from the index alone)

// Multikey index — each array element is indexed
db.orders.createIndex({ "line_items.sku": 1 });
db.orders.find({ "line_items.sku": "TSHIRT-BLK-M" });  // IXSCAN, not COLLSCAN

Step-by-step explanation.

The covered-query index { status: 1, customer_id: 1 } contains both fields the pipeline touches. Because the $project returns only customer_id and excludes _id, the planner can satisfy the whole query from index keys — totalDocsExamined is 0, the fastest possible read.
Excluding _id is mandatory for coverage: if _id is returned, MongoDB must fetch the document to get it (unless _id is in the index), breaking coverage.
The multikey index on line_items.sku indexes every element of every array. A find for a SKU seeks the B-tree instead of scanning and unwinding the whole collection — this is why embedded arrays remain queryable at scale.
Multikey has one restriction worth stating: an index cannot be multikey on two array fields at once (that would be a combinatorial explosion), so you index one array path per compound index.
Together, covered queries (zero document fetches) and multikey indexes (arrays stay seekable) are the two advanced levers that keep document-store analytics fast.

Output.

Query	Plan	totalDocsExamined
covered (status→customer_id)	IXSCAN, PROJECTION_COVERED	0
multikey (line_items.sku)	IXSCAN	≈ nReturned

Rule of thumb. For a hot read that returns only a few fields, build a covering compound index and exclude _id so the query is answered from the index alone. For queries into embedded arrays, add a multikey index on the array path — but only one array path per index.

Data engineering interview question on indexing

A senior interviewer might ask: "An aggregation over a 50-million-document orders collection filters status = 'paid', restricts created_at to the last 7 days, sorts newest-first, and returns the top 100. It takes 30 seconds. Walk me through how you'd diagnose it with explain() and the exact index you'd build, ordered correctly."

Solution Using an ESR compound index verified with explain()

// 1. Diagnose the current plan
db.orders.explain("executionStats").aggregate([
  { $match: { status: "paid",
              created_at: { $gte: ISODate("2026-08-29T00:00:00Z") } } },
  { $sort:  { created_at: -1 } },
  { $limit: 100 }
]);
// Symptom: COLLSCAN + in-memory SORT, totalDocsExamined ≈ 50,000,000

// 2. Build the ESR index:
//    Equality = status, Sort = created_at (desc to match), Range = (none extra here)
db.orders.createIndex({ status: 1, created_at: -1 });

// 3. Re-check — plan should be IXSCAN with no SORT stage
db.orders.explain("executionStats").aggregate([
  { $match: { status: "paid",
              created_at: { $gte: ISODate("2026-08-29T00:00:00Z") } } },
  { $sort:  { created_at: -1 } },
  { $limit: 100 }
]);
// winningPlan: IXSCAN {status:1, created_at:-1}; no SORT; stops after 100 keys

Step-by-step trace.

Step	Observation	Action
Diagnose	COLLSCAN, examined ≈ 50M, SORT stage present	read `explain`
Root cause	no index serves `status` + `created_at` order	ESR analysis
Build	`{status:1, created_at:-1}` (E then S)	createIndex
`status`	equality → index prefix	seek to "paid"
`created_at`	sort desc, matches index dir	no in-memory SORT
`$limit 100`	index-ordered scan stops early	O(100) work

The index puts the equality field (status) first so the scan seeks straight to paid orders, then stores created_at descending so the requested newest-first order is provided by the index — the SORT stage disappears. The 7-day created_at filter is applied along that ordered scan, and $limit: 100 lets the scan stop after 100 qualifying keys. totalDocsExamined collapses from ~50M to ~100.

Output:

Metric	Before	After
winningPlan	COLLSCAN + SORT	IXSCAN, no SORT
totalDocsExamined	~50,000,000	~100
totalKeysExamined	n/a	~a few hundred
elapsed	~30 s	~5 ms

Why this works — concept by concept:

COLLSCAN vs IXSCAN — the whole fix is moving the leading $match from a full collection scan to an index seek; explain's winningPlan.stage names which one you got, and totalDocsExamined quantifies the waste.
ESR ordering — status (Equality) first lets the B-tree jump to the paid sub-tree; created_at: -1 (Sort) second means the index already yields rows newest-first, eliminating the blocking in-memory sort and its 100 MB ceiling.
Index-provided sort + $limit — because the scan emerges pre-sorted, $limit: 100 stops the scan after 100 matching keys instead of sorting millions then slicing — a true index-served top-N.
Read explain, don't guess — the before/after explain("executionStats") is the proof: examined-vs-returned went from 50M:100 to ~100:100, and the SORT stage vanished, confirming the index order is right.
Cost — O(log n) seek + O(limit) ordered scan, versus O(n) scan + O(n log n) sort. The index converts the dominant cost from collection size n to the requested limit — the defining win of correct indexing.

Database
Topic — database
Indexing, explain-plan, and performance problems

Practice →

Aggregation Topic — aggregation Aggregation performance and index-tuning problems

Practice →

5. $merge/$out to a warehouse + sharding

`$merge` upserts pipeline output incrementally; `$out` replaces a collection; sharding partitions data by a shard key

The mental model in one line: $out and $merge are the pipeline stages that write results back into a collection instead of returning them to the client — $out fully replaces a target collection with the pipeline's output, while $merge upserts the output into an existing target on a match key (whenMatched / whenNotMatched), making it the primitive for incremental materialized rollups — and when one server can no longer hold the data, sharding partitions each collection across shards by a shard key, which every aggregation and every write must be designed around. For a data engineer, $merge is on-cluster ELT (build the rollup in MongoDB, then export the small rollup rather than the huge raw collection), and the shard key is the single decision that determines whether queries hit one shard or fan out to all of them.

$out vs $merge — full replace vs incremental upsert.

$out. Writes the pipeline result to a collection, replacing it entirely (atomically swaps in the new collection). Perfect for a full nightly snapshot; wrong for incremental updates because it discards whatever was there.
$merge. Writes into a target with per-document semantics: whenMatched (replace, merge, keepExisting, fail, or a custom pipeline) and whenNotMatched (insert, discard, fail). Matched on the target's unique key (often _id or a unique index). This is how you maintain an incremental materialized view.
Materialized views. Run a pipeline that computes today's rollup and $merge it into a daily_rollup collection keyed by (day, customer); re-running only updates the changed days. This is on-cluster incremental ELT.

Getting data to the warehouse.

Rollup then export. $merge the aggregate into a small collection, then let a connector (the MongoDB Kafka/Spark connector, Airbyte, or a mongoexport) ship that to Snowflake/BigQuery — you move megabytes of rollup, not terabytes of raw documents.
Change streams for CDC. For continuous sync, a change stream (db.coll.watch()) tails the oplog and emits insert/update/delete events with resume tokens — the document-store equivalent of log-based CDC.
Incremental watermark. Combine a $match on updated_at > last_run with a $merge upsert so each export processes only changed documents.

Sharding — scaling writes and reads horizontally.

The shard key. The field(s) MongoDB uses to partition documents into chunks across shards. Chosen once, painful to change. Must have high cardinality, low update frequency, and even access distribution.
Hashed vs ranged. A hashed shard key spreads writes evenly (great for monotonically increasing keys like timestamps or ObjectIds that would otherwise hot-spot one shard); a ranged shard key keeps nearby values together (great for range queries, risky for monotonic inserts).
Targeted vs scatter-gather. A query that includes the shard key is targeted to the one shard holding it; a query without it is scatter-gather, broadcast to every shard and merged — much more expensive. Aggregations should carry the shard key in an early $match whenever possible.

What interviewers listen for.

Do you use $merge for incremental rollups and $out for full snapshots, not interchangeably? — senior signal.
Do you warn that a monotonic shard key hot-spots one shard and prefer hashed for it? — required answer.
Do you distinguish targeted (shard key present) from scatter-gather queries? — a real-experience tell.
Do you mention rollup-then-export (move the small aggregate, not the raw collection) and change streams for CDC? — senior signal.

Worked example — incremental daily rollup with `$merge`

Detailed explanation. The materialized-view pattern: a pipeline computes per-day, per-customer revenue and $merges it into a daily_rollup collection keyed by (day, customer_id). Re-running for a day replaces just that day's rows, so the rollup is idempotent and incremental.

Compute. $match a day window, $group by (day, customer).
Key. on: ["day", "customer_id"] (a unique index on the target).
Semantics. whenMatched: "replace", whenNotMatched: "insert".

Question. Maintain an incremental daily_rollup of revenue per customer per day using $merge.

Input.

source day	customer	revenue_cents
2026-09-04	c1	4200
2026-09-04	c2	1850

Code.

// Target needs a unique index on the merge key
db.daily_rollup.createIndex({ day: 1, customer_id: 1 }, { unique: true });

db.orders.aggregate([
  { $match: { created_at: { $gte: ISODate("2026-09-04T00:00:00Z"),
                            $lt:  ISODate("2026-09-05T00:00:00Z") } } },
  { $group: {
      _id: { day: "2026-09-04", customer_id: "$customer_id" },
      revenue_cents: { $sum: "$total_cents" },
      order_count:   { $sum: 1 }
  }},
  { $project: {
      _id: 0,
      day: "$_id.day",
      customer_id: "$_id.customer_id",
      revenue_cents: 1,
      order_count: 1
  }},
  { $merge: {
      into: "daily_rollup",
      on: ["day", "customer_id"],       // unique key to match on
      whenMatched: "replace",           // idempotent re-run of a day
      whenNotMatched: "insert"
  }}
]);

Step-by-step explanation.

The target daily_rollup has a unique index on (day, customer_id) — $merge's on clause requires a unique index over the match fields so it can decide matched vs not-matched deterministically.
The pipeline computes one row per (day, customer_id) for the target day only, thanks to the $match window — so a re-run touches just that day.
$merge with whenMatched: "replace" overwrites any existing row for a (day, customer_id) with the freshly computed one; whenNotMatched: "insert" adds new customer-days. The operation is idempotent: running it twice yields the same rollup.
Because only the target day is recomputed and merged, this is incremental — yesterday's and last week's rollup rows are untouched, unlike $out which would wipe the whole collection.
The pattern is on-cluster ELT: heavy aggregation runs where the data lives, and only the compact rollup is produced — ready to export to a warehouse cheaply.

Output (daily_rollup after the run):

day	customer_id	revenue_cents	order_count
2026-09-04	c1	4200	1
2026-09-04	c2	1850	1

Rule of thumb. Use $merge with a unique index on the match key and whenMatched: "replace" for idempotent, incremental rollups — re-running a day corrects it without touching other days. Reserve $out for when you genuinely want to rebuild the whole target from scratch.

Worked example — `$out` full snapshot for export

Detailed explanation. When a consumer wants a clean, complete snapshot (a nightly full extract with no incremental complexity), $out is the right tool: it atomically replaces the target with the pipeline output. The trade-off is that it rebuilds everything each run.

Compute. Flatten and coerce the full collection.
Write. $out to a orders_export collection.
Export. A connector ships orders_export to the warehouse.

Question. Produce a full, warehouse-typed snapshot of orders into orders_export with $out.

Input.

Field	From	To
order_id	`_id` ObjectId	string
revenue	total_cents	decimal
created_at	`_id` timestamp	date

Code.

db.orders.aggregate([
  { $project: {
      _id: 0,
      order_id:   { $toString: "$_id" },
      customer_id:{ $toString: "$customer_id" },
      status: 1,
      revenue: { $toDecimal: { $divide: ["$total_cents", 100] } },
      created_at: { $toDate: "$_id" }
  }},
  { $out: "orders_export" }        // replaces orders_export entirely
]);

Step-by-step explanation.

The $project coerces BSON types into warehouse-friendly ones (ObjectId → string, cents → decimal, ObjectId → date) — the same source-side contract discipline from section 1.
$out: "orders_export" writes the entire result into orders_export, atomically swapping the new collection in for the old one. Readers see either the complete old snapshot or the complete new one, never a half-written state.
Unlike $merge, $out does not upsert or key-match — it replaces. There is no incremental behavior; every run reprocesses the whole collection.
This is the right choice when the downstream export is a full-refresh (truncate-and-load into the warehouse) and the collection is small enough that a full rebuild each night is acceptable.
For large collections where a full nightly rebuild is too expensive, prefer the incremental $merge pattern with an updated_at watermark instead.

Output (orders_export, sample):

order_id	customer_id	status	revenue	created_at
6650…01	6651…a1	paid	42.00	2026-08-14T10:22:00Z

Rule of thumb. Use $out for a full, atomic snapshot when a truncate-and-load export is acceptable and the collection is modest; switch to $merge with a watermark once a nightly full rebuild costs more than processing only the changed documents.

Worked example — shard key choice and query routing

Detailed explanation. On a sharded cluster, the shard key decides both write distribution and query routing. A monotonically increasing key (like created_at or a raw ObjectId) sends every new write to the same "highest" chunk — a hot shard. Hashing the key spreads writes; including the shard key in queries keeps them targeted.

Bad. Ranged shard key on created_at → all inserts hit one shard.
Good. Hashed shard key on customer_id → even writes, targeted per-customer queries.
Routing. Queries with customer_id are targeted; queries without it scatter-gather.

Question. Choose a shard key for orders that spreads writes and keeps per-customer analytics targeted, and show a targeted vs scatter-gather query.

Input.

Candidate key	Write spread	Per-customer query	Range query
ranged `created_at`	hot shard (bad)	scatter	good
hashed `customer_id`	even (good)	targeted	scatter
hashed `_id`	even	scatter	scatter

Code.

// Enable sharding and choose a hashed customer_id shard key
sh.enableSharding("shop");
sh.shardCollection("shop.orders", { customer_id: "hashed" });

// TARGETED — includes the shard key, routed to one shard
db.orders.aggregate([
  { $match: { customer_id: ObjectId("6651…a1") } },   // shard key present
  { $group: { _id: "$status", revenue: { $sum: "$total_cents" } } }
]);

// SCATTER-GATHER — no shard key, broadcast to every shard then merged
db.orders.aggregate([
  { $match: { status: "paid" } },                      // shard key absent
  { $group: { _id: "$customer_id", revenue: { $sum: "$total_cents" } } }
]);

Step-by-step explanation.

sh.shardCollection("shop.orders", { customer_id: "hashed" }) partitions orders by a hash of customer_id. Hashing spreads inserts evenly across shards even though customer ids are not sequential — no single hot shard.
Choosing customer_id (not created_at) as the key means the common analytical access — "everything for this customer" — carries the shard key, so mongos routes the query to the single shard holding that customer's chunk. That is a targeted query.
The second aggregation filters on status only, which is not the shard key. mongos must broadcast it to every shard, run it everywhere, and merge — a scatter-gather query that costs proportionally to the number of shards.
The shard-key lesson: pick the key that (a) spreads writes (hash a monotonic or low-cardinality candidate) and (b) appears in your hottest queries so they stay targeted. These two goals sometimes conflict, and resolving that trade-off is the senior judgment call.
On a sharded cluster, always try to put the shard key in an early $match in an aggregation; without it, every stage runs on every shard.

Output.

Query	Shard key present?	Routing	Shards touched
per-customer	yes (`customer_id`)	targeted	1
by status	no	scatter-gather	all

Rule of thumb. Pick a shard key with high cardinality that appears in your hottest queries, and hash it when the natural key is monotonic (timestamps, ObjectIds) to avoid a hot shard. Design aggregations to carry the shard key in an early $match so they stay targeted instead of scatter-gather.

Data engineering interview question on $merge and sharding

A senior interviewer might ask: "You run a sharded orders cluster. You need a nightly incremental revenue rollup per customer per day, exported to Snowflake, with re-runs that safely correct a day without rewriting history. Walk me through the shard key, the incremental pipeline, the $merge semantics, and how you keep the aggregation from scatter-gathering across all shards."

Solution Using $merge whenMatched upsert with a watermark on a hashed shard key

// 0. Cluster: hashed shard key spreads writes; customer_id keeps per-customer targeted
sh.shardCollection("shop.orders", { customer_id: "hashed" });

// Target rollup with a unique merge key
db.daily_rollup.createIndex({ day: 1, customer_id: 1 }, { unique: true });

// 1. Incremental pipeline — only changed docs since the last watermark
const lastRun = ISODate("2026-09-04T00:00:00Z");
db.orders.aggregate([
  { $match: {
      updated_at: { $gte: lastRun },                  // watermark: only new/changed
      created_at: { $gte: ISODate("2026-09-04T00:00:00Z"),
                    $lt:  ISODate("2026-09-05T00:00:00Z") }
  }},
  { $addFields: { day: { $dateToString: { format: "%Y-%m-%d", date: "$created_at" } } } },
  { $group: {
      _id: { day: "$day", customer_id: "$customer_id" },
      revenue_cents: { $sum: "$total_cents" },
      order_count:   { $sum: 1 }
  }},
  { $project: { _id: 0, day: "$_id.day", customer_id: "$_id.customer_id",
                revenue_cents: 1, order_count: 1 } },
  { $merge: {
      into: "daily_rollup",
      on: ["day", "customer_id"],
      whenMatched: "replace",        // idempotent re-run of a day
      whenNotMatched: "insert"
  }}
], { allowDiskUse: true });

// 2. Export just the small rollup to Snowflake (connector / mongoexport)
//    -> ships megabytes of rollup, not terabytes of raw orders

Step-by-step trace.

Layer	Choice	Why
Shard key	`customer_id` hashed	even writes; per-customer queries targeted
Watermark	`updated_at >= lastRun`	process only changed documents
Grouping	`(day, customer_id)`	one rollup row per customer-day
Merge key	unique `{day, customer_id}`	deterministic matched/not-matched
`whenMatched`	`replace`	idempotent day re-runs
Export	rollup only	move the aggregate, not the raw data

The hashed customer_id shard key spreads inserts so no shard hot-spots, and because customer-scoped reads carry that key they stay targeted. The updated_at watermark limits the pipeline to changed documents; the (day, customer_id) group + $merge whenMatched: "replace" makes a re-run of any day idempotent, correcting it without touching other days. Only the compact daily_rollup crosses the wire to Snowflake.

Output:

day	customer_id	revenue_cents	order_count
2026-09-04	c1	4200	1
2026-09-04	c2	1850	1

Why this works — concept by concept:

$merge whenMatched: replace — keyed on a unique (day, customer_id) index, the merge overwrites exactly the recomputed rows and inserts new ones, so re-running a day is idempotent and never corrupts history — the defining property of an incremental materialized view.
Watermark on updated_at — filtering to updated_at >= lastRun means each run processes only changed documents, turning a full recompute into an incremental one; the watermark advances each run like log-based/timestamp CDC.
Hashed shard key on customer_id — hashing spreads inserts evenly (no monotonic hot shard), while choosing customer_id keeps the common per-customer analytics targeted to one shard instead of scatter-gathering across all of them.
Rollup-then-export — the heavy $group runs on-cluster where the data lives, and only the small daily_rollup is exported, so the warehouse feed moves megabytes, not terabytes.
Cost — O(changed docs) per run for the watermark-limited scan, O(groups) for the merge, and O(rollup size) for the export, versus O(all orders) for a full nightly rebuild and O(shards) for a scatter-gather. Incrementality and shard-key targeting keep every term proportional to change, not to total data.

Database
Topic — database
Sharding, $merge, and warehouse-export problems

Practice →

Aggregation
Topic — aggregation
Materialized-rollup and incremental-aggregation problems

Practice →

Cheat sheet — MongoDB aggregation recipes

Embed vs reference. Embed data that is read together and bounded (order + line items); reference data that is shared across parents or unbounded (a customer referenced by many orders, a user's millions of events). The hard limit is the 16 MB document cap — any array that can grow without bound must be referenced, not embedded.
Extraction projection. End every warehouse-feed pipeline with an explicit $project that coerces types: { $toString: "$_id" } for ids, { $toDate: "$_id" } to recover a creation timestamp from an ObjectId, { $toDecimal: { $divide: ["$cents", 100] } } for money. Coerce at the source, never in the warehouse.
Stage skeleton. [$match → $group → $sort → $limit → $project]. Lead with $match (index-eligible, shrinks the stream), fold with $group ($sum, $avg, $push, $sum: 1 for count), rank with adjacent $sort + $limit (coalesced top-N), shape last with $project. Set allowDiskUse: true when a $group/$sort can exceed 100 MB.
$project vs $addFields. $project replaces the document with only the listed fields; $addFields (alias $set) augments and keeps the rest. Use $addFields for intermediate computed values, $project for the final minimal contract. $match after $group is SQL HAVING.
$lookup join. Equality form { from, localField, foreignField, as } for a foreign-key attach — always index the foreignField (joining to _id is free). Sub-pipeline form { from, let, pipeline, as } with $expr to filter/aggregate the joined side. The as field is always an array; $unwind it to get an object/scalar.
$unwind semantics. $unwind: "$arr" emits one document per element and drops empty/missing arrays; add { path: "$arr", preserveNullAndEmptyArrays: true } for left-join semantics. Explode late (after $match) and $group back down immediately, because the stream grows N×.
ESR index rule. Order compound-index fields Equality → Sort → Range: equality predicates first (seek), the sort field next (index-provided order, no in-memory SORT), range predicates last. A compound index serves only a left prefix of its fields — {status, created_at} serves status or status+created_at, never created_at alone.
explain() triage. Run db.coll.explain("executionStats").aggregate([...]). Read winningPlan.stage (IXSCAN good, COLLSCAN bad), and totalDocsExamined vs nReturned — a large ratio means you are scanning. A lingering SORT stage means your index order violates ESR.
Covered query. When the index holds every field the query filters and returns, and you exclude _id in the projection, the query is answered from the index alone (totalDocsExamined: 0). Build a covering compound index for hot, few-field reads.
Multikey index. Index an array path ({ "line_items.sku": 1 }) to keep embedded arrays seekable; each element is indexed. Restriction: an index cannot be multikey on two array fields at once — one array path per index.
$out vs $merge. $out fully replaces the target collection (atomic full snapshot) — use for truncate-and-load exports of modest collections. $merge upserts with on: [keys], whenMatched (replace/merge/keepExisting/pipeline), whenNotMatched (insert/discard) — use for incremental materialized rollups keyed by a unique index; whenMatched: "replace" makes day re-runs idempotent.
Sharding. Pick a shard key with high cardinality that appears in your hottest queries; hash it when the natural key is monotonic (timestamps, ObjectIds) to avoid a hot shard. Queries carrying the shard key are targeted to one shard; queries without it scatter-gather across all shards — put the shard key in an early $match. For warehouse feeds, $merge a rollup then export the rollup, and use change streams (watch()) for continuous CDC.

Frequently asked questions

What is the MongoDB aggregation pipeline in one sentence?

The mongodb aggregation pipeline is an ordered array of stages passed to db.collection.aggregate([...]), where each stage consumes the stream of documents produced by the previous stage and emits a transformed stream — filtering with $match, folding with $group, joining with $lookup, flattening with $unwind, and reshaping with $project — until the final stage returns (or, with $out/$merge, writes) the result. It is MongoDB's answer to SQL's SELECT … WHERE … GROUP BY … JOIN, but written as an explicit, ordered sequence you control stage by stage. Because the stages run in the order you write them, stage ordering is part of the program: a $match placed first can use an index and shrink the stream before any expensive stage runs.

Embed or reference — how do I decide?

Embed data that is read together and bounded; reference data that is shared or unbounded. An order and its handful of line items are read together and few in number, so embed the line items as an array inside the order — one read returns the whole aggregate with no join. A customer referenced by thousands of orders is shared, so store the customer once and reference it by _id — otherwise a name change means rewriting thousands of copies. The hard constraint is the 16 MB document cap: any array that can grow without bound (a user's events, a post's comments) must be referenced into its own collection, often with a small embedded preview on the parent for the common read. Model for the read pattern first; normalize only where sharing or unbounded growth forces your hand.

Is $lookup a real join, and how slow is it?

$lookup is a genuine left outer join, but it is implemented as a nested loop: for each input document it probes the foreign collection, attaching matches as an array on the as field. That means its cost depends entirely on whether the foreignField is indexed. Joining orders.customer_id to customers._id uses the built-in _id index, so each probe is an O(log n) seek and the whole join is O(orders · log customers) — fast. Without an index on the foreignField, each probe becomes a full collection scan and the join degrades to O(n · m), which is catastrophic at scale. Two rules follow: always index the field you join to, and filter the driving collection with a $match before the $lookup so you perform fewer probes. For filtering or aggregating the joined side, use the sub-pipeline form (let + $expr) so each probe returns only what you need.

$match vs $group vs $project — what does each stage do?

$match filters the document stream using the same query syntax as find(); placed first, it can use an index, and placed after a $group it filters grouped output (SQL's HAVING). $group folds all documents sharing the same _id expression into one output document, computing accumulators like $sum, $avg, $min, $max, and $push; _id: null collapses everything into a single total, and $sum: 1 is the idiomatic row count. $project reshapes each document, keeping only the fields you list and adding computed ones — use it to lock down the final output contract; its cousin $addFields (alias $set) instead augments the document with new fields while keeping the rest, which is what you want for intermediate values a later stage still needs.

How do I make an aggregation use an index?

Only a $match (or $sort) at the front of the pipeline can use a collection index — once a $group or transforming $project has run, later stages filter in memory. So lead with $match, and build a compound index ordered by the ESR rule: Equality fields first, then the Sort field, then Range fields. Verify with db.coll.explain("executionStats").aggregate([...]) and read two numbers: winningPlan.stage should be IXSCAN (not COLLSCAN), and totalDocsExamined should be close to nReturned — a large gap means you are scanning. A lingering SORT stage in the plan means your sort field is in the wrong index position; move all equality fields ahead of it. For hot reads that return only a couple of fields, build a covering index and exclude _id so the query is answered from the index alone.

$out vs $merge — which do I use for a warehouse feed?

Use $out when you want a full snapshot: it atomically replaces the entire target collection with the pipeline's output, ideal for a modest truncate-and-load export where rebuilding everything each run is acceptable. Use $merge when you want an incremental materialized rollup: it upserts the output into an existing target keyed by a unique index, with whenMatched (replace, merge, keepExisting, or a custom pipeline) and whenNotMatched (insert, discard) controlling per-document behavior. The standard warehouse pattern is $merge with whenMatched: "replace" on a (day, key) unique index plus an updated_at watermark: each run recomputes only changed days and corrects them idempotently, and you export the small rollup rather than the raw collection. On a sharded cluster, keep the aggregation targeted by carrying the shard key in an early $match, and consider change streams (watch()) for continuous CDC instead of batch re-runs.

Practice on PipeCode

Drill the database practice library → for the document-modelling, indexing, explain(), sharding, and $merge problems senior interviewers love.
Rehearse on the aggregation practice library → for the $match/$group/$project stage-ordering, $lookup + $unwind join, and incremental-rollup patterns.
Sharpen document-shape fluency on the JSON practice library → for nested documents, embedded arrays, and polymorphic-schema queries.
Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the embed-vs-reference and ESR-index decisions against real graded inputs.

Lock in MongoDB aggregation muscle memory

Docs explain the stages. PipeCode drills explain the decision — when to embed versus reference, why `$match` goes first, how `$lookup` becomes a nested-loop trap without an index, how the ESR rule turns a COLLSCAN into an IXSCAN, and when `$merge` beats `$out` for a warehouse feed. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs data engineers actually face.

Practice aggregation problems →
Practice database problems →

1. The document data model

One document per entity, nested not normalized — model for how you read, not for how you store

Worked example — embedding line items vs referencing them

Worked example — _id, ObjectId, and BSON types you will meet in the warehouse

Worked example — polymorphic and versioned documents

Data engineering interview question on the document model

Solution Using an embed-plus-reference hybrid keyed by ObjectId

2. Aggregation pipeline stages ($match/$group/$project)

The pipeline is an ordered conveyor — $match filters, $group folds, $project reshapes, and order is the program

Worked example — revenue per status with $match + $group

Worked example — $project vs $addFields and computed fields

Worked example — multi-stage top-N per customer

Data engineering interview question on the aggregation pipeline

Solution Using $match → $group → $sort → $limit with a date-bucket key

3. $lookup joins & $unwind

$lookup is a left outer join into an array; $unwind flattens that array into one document per element

Worked example — basic $lookup from orders to customers

Worked example — $unwind an embedded array then group back

Worked example — correlated sub-pipeline $lookup

Data engineering interview question on $lookup and $unwind

Solution Using $lookup on customers + $unwind on line_items with an index

4. Indexing & performance

Indexes turn a COLLSCAN into an IXSCAN — order compound keys Equality → Sort → Range

Worked example — COLLSCAN to IXSCAN with explain()

Worked example — compound index following the ESR rule

Worked example — covered query and multikey index

Data engineering interview question on indexing

Solution Using an ESR compound index verified with explain()

5. $merge/$out to a warehouse + sharding

$merge upserts pipeline output incrementally; $out replaces a collection; sharding partitions data by a shard key

Worked example — incremental daily rollup with $merge

Worked example — $out full snapshot for export

Worked example — shard key choice and query routing

Data engineering interview question on $merge and sharding

Solution Using $merge whenMatched upsert with a watermark on a hashed shard key

Cheat sheet — MongoDB aggregation recipes

Frequently asked questions

What is the MongoDB aggregation pipeline in one sentence?

Embed or reference — how do I decide?

Is $lookup a real join, and how slow is it?

$match vs $group vs $project — what does each stage do?

How do I make an aggregation use an index?

$out vs $merge — which do I use for a warehouse feed?

Practice on PipeCode

Lock in MongoDB aggregation muscle memory

Worked example — `_id`, `ObjectId`, and BSON types you will meet in the warehouse

The pipeline is an ordered conveyor — `$match` filters, `$group` folds, `$project` reshapes, and order is the program

Worked example — revenue per status with `$match` + `$group`

Worked example — `$project` vs `$addFields` and computed fields

`$lookup` is a left outer join into an array; `$unwind` flattens that array into one document per element

Worked example — basic `$lookup` from orders to customers

Worked example — `$unwind` an embedded array then group back

Worked example — correlated sub-pipeline `$lookup`

Worked example — COLLSCAN to IXSCAN with `explain()`

`$merge` upserts pipeline output incrementally; `$out` replaces a collection; sharding partitions data by a shard key

Worked example — incremental daily rollup with `$merge`

Worked example — `$out` full snapshot for export