138: MongoDB Aggregation — Pipelines, Expressions, `$match`, `$group`, `$unwind`, `$lookup`, `$facet`, and Optimization

Learning objectives

By the end of this lesson, you should be able to:

understand aggregation pipelines;
use common stages;
distinguish stages from expressions;
filter/project/transform;
group and calculate metrics;
unwind arrays;
join collections with $lookup;
build multi-result $facet pipelines;
understand accumulators;
use date/string/array expressions;
reason about stage ordering and index use;
use explain() for aggregation;
understand memory and disk-use concerns;
know when precomputation is better than repeated heavy aggregation.

The goal is not just to memorize stage names. You should be able to look at a pipeline, explain what shape of document each stage receives and emits, and identify where filtering, indexing, memory use, or tenant scope could change the result.

Pipeline mental model

An aggregation pipeline is a sequence of stages. Documents flow through those stages from left to right:

collection
↓
$match
↓
$project / $set
↓
$group
↓
$sort
↓
$limit
↓
result

Each stage consumes documents and emits documents. A stage does not operate in isolation: the output shape from one stage becomes the input shape for the next. This is the most useful mental model when debugging an aggregation. Ask, "What documents exist at this point, and what fields are still available?"

For example, this pipeline restricts the input to completed orders for a tenant, groups the remaining orders by customer, sorts customers by their total, and keeps the first ten results:

db.orders.aggregate([
  {
    $match: {
      tenantId,
      status: "completed"
    }
  },
  {
    $group: {
      _id: "$customerId",
      totalPaise: {
        $sum: "$totalPaise"
      }
    }
  },
  {
    $sort: {
      totalPaise: -1
    }
  },
  {
    $limit: 10
  }
])

The result is no longer an order-shaped document. After $group, each document represents a customer and has _id and totalPaise. That matters if a later stage expects fields such as status or customerId: those fields are not automatically carried through the group.

Stage versus expression

This distinction is one of the first places people get confused. A stage changes the pipeline as a whole and appears as an object with a stage operator such as $match:

Stage:

{
  $match: {...}
}

An expression computes a value inside a stage. In this example, $multiply calculates total for each projected document:

Expression inside stage:

{
  $project: {
    total: {
      $multiply: [
        "$quantity",
        "$price"
      ]
    }
  }
}

The useful rule is: stages control document flow; expressions calculate values from a document or from values already being built. Operators can exist in different contexts, so read the MongoDB documentation for the exact syntax and the context in which an operator is valid. Similar-looking names do not guarantee identical behavior in a query predicate and an aggregation expression.

`$match`

$match filters pipeline input in much the same way as a normal MongoDB query filter. It is usually the first stage to consider because removing irrelevant documents early reduces the work required by later stages.

Place a selective match early where possible:

{
  $match: {
    tenantId,
    createdAt: {
      $gte: start,
      $lt: end
    }
  }
}

Early matching can use indexes when the pipeline and query optimizer permit it. That is a possibility to verify, not a promise to assume. The fields, operators, available index, and surrounding stages all affect the plan.

Do not start with an expensive $lookup and only filter by tenant or date afterward when the filter could have happened first. The lookup would then do work for documents that were going to be discarded anyway. Keeping the initial filter selective also makes the later stages easier to reason about.

`$project`

$project chooses fields and reshapes each document. It can also calculate new fields with expressions:

{
  $project: {
    _id: 0,
    orderId: "$_id",
    customerId: 1,
    totalPaise: 1
  }
}

Here the original _id is omitted, its value is exposed as orderId, and the two named fields are retained. Projection is useful for defining the output contract of a reporting endpoint, not merely for making the result look tidy.

Do not project giant fields that you do not need. Carrying large descriptions, blobs, or sensitive fields through several stages increases work and can increase memory pressure. Remove them once they are no longer required, while keeping any field needed by a later filter, join, sort, or calculation.

`$set` / `$addFields`

$set adds or replaces fields while retaining the other fields in the document. $addFields is the equivalent older spelling used in many existing pipelines:

{
  $set: {
    totalItems: {
      $sum: "$items.quantity"
    }
  }
}

The exact behavior of array expressions must be tested with the shapes your data actually contains. Missing fields, empty arrays, null values, and scalar values can produce different outcomes than a clean sample document suggests. Treat those cases as part of the schema contract rather than as an afterthought.

`$unset`

$unset removes fields from pipeline documents. It is useful when a large field is no longer needed or when a sensitive field should not continue through a reporting pipeline.

Dropping a field in an intermediate stage is not a substitute for authorization. The initial query still needs to be scoped correctly, and the application must still control which pipeline stages and output fields a caller is allowed to request.

`$group`

$group combines input documents according to a grouping key. The following example produces one output document per status and calculates three metrics for each status:

{
  $group: {
    _id: "$status",
    count: {
      $sum: 1
    },
    totalPaise: {
      $sum: "$totalPaise"
    },
    averagePaise: {
      $avg: "$totalPaise"
    }
  }
}

The _id in a $group stage is the grouping key; it is not necessarily the original document identifier. $sum: 1 counts input documents in each group, while $sum: "$totalPaise" adds the value from each input document. $avg calculates the average of the values that reach that group.

Common accumulators include:

$sum
$avg
$min
$max
$first
$last
$push
$addToSet

Ordering matters for $first and $last. If the first or last item has business meaning, sort the documents appropriately before grouping, and make the ordering rule explicit. Without that ordering, "first" and "last" should not be treated as stable business facts.

Group cardinality

The number of groups affects both the meaning and the cost of a pipeline. Grouping by a near-unique field creates many groups and can consume substantial memory:

Example:

_group by requestId

If nearly every request has a different requestId, this may be pointless or expensive. It produces little useful reduction and may prevent the group stage from behaving like a meaningful summary. Before choosing a key, state what one output row is supposed to represent. Then check whether the chosen key actually has that cardinality.

`$sort`

$sort orders the documents currently in the pipeline:

{
  $sort: {
    totalPaise: -1
  }
}

If the sort occurs after $group, totalPaise is a field on grouped, synthetic output documents. An index on the original orders cannot directly sort those generated totals. This kind of sort may therefore require memory or disk rather than being satisfied by an index on the source collection.

Limit early when the semantics allow it. A top-results query can often benefit from a useful sort-plus-limit shape, but the placement must match the question being asked. Sorting and limiting before a group is not equivalent to sorting and limiting the grouped results.

`$limit`

$limit keeps only the specified number of documents:

{
  $limit: 20
}

It can reduce downstream work, particularly when later stages are expensive. But moving a limit before a group changes the result semantics: the group would summarize only the first twenty input documents rather than all matching documents.

Optimization must preserve meaning. A shorter-running pipeline that answers a different question is not an optimization.

`$skip`

$skip is useful for offset-style pagination, but deep skips have the same pagination concerns as ordinary queries. MongoDB still has to advance past the skipped results, so a large offset can become increasingly expensive.

For reporting output on small datasets, this may be acceptable. For user-facing pagination over a large or changing dataset, range or cursor pagination is generally a better fit. The cursor should use a stable, indexed ordering and a tie-breaker when the primary sort value is not unique.

`$unwind`

Suppose an order contains an array of items:

Document:

{
  items: [
    { sku: "A", quantity: 2 },
    { sku: "B", quantity: 1 }
  ]
}

$unwind turns each array element into a separate pipeline document:

{
  $unwind: "$items"
}

The example now produces one pipeline document per item. That expanded shape makes item-level aggregation possible. For example, the next stage can total units by SKU:

{
  $group: {
    _id: "$items.sku",
    units: {
      $sum: "$items.quantity"
    }
  }
}

The expansion is also a cost boundary. An order with many items produces many working documents, and unwinding multiple arrays can multiply that effect. Check whether the expanded documents are really needed before adding the stage.

Preserve empty/null arrays

By default, the treatment of missing, null, or empty arrays can cause documents to disappear from the stream. $unwind options can preserve null or empty values and can include the original array index.

Decide which behavior matches the report: should an order with no items disappear because there is no item row, or should it remain with an empty/null item value? This distinction affects counts and totals, so test it explicitly with missing, null, empty, and populated arrays.

`$lookup`

$lookup is the aggregation stage used for a join-like operation. For example, an order may store a customer identifier:

Orders:

{
  customerId: ObjectId(...)
}

The corresponding customer document might look like this:

Customers collection:

{
  _id: ObjectId(...),
  name: "Maya"
}

A simple lookup matches localField in the current document to foreignField in the other collection:

{
  $lookup: {
    from: "customers",
    localField: "customerId",
    foreignField: "_id",
    as: "customer"
  }
}

The lookup result is an array, even when the relationship is expected to be one-to-one. If the next stages need a single customer-shaped document, the pipeline often unwinds it:

{
  $unwind: "$customer"
}

Be deliberate about missing matches. Unwinding without preserving an empty result can remove the source order. If an order should remain visible even when its customer record is missing, use the relevant unwind option and handle the absent customer in the output contract.

`$lookup` pipeline

The pipeline form of $lookup is more powerful. It lets the foreign side apply its own matching, projection, and other stages using values passed from the current document:

{
  $lookup: {
    from: "payments",
    let: {
      orderId: "$_id"
    },
    pipeline: [
      {
        $match: {
          $expr: {
            $eq: [
              "$orderId",
              "$$orderId"
            ]
          }
        }
      },
      {
        $project: {
          amountPaise: 1,
          status: 1
        }
      }
    ],
    as: "payments"
  }
}

let.orderId defines a value from the current order, and $$orderId references that value inside the foreign pipeline. The foreign collection's indexes matter. A lookup that scans a large foreign collection for many input documents can behave like a massive nested join, so inspect the plan and the number of documents examined rather than judging the stage by its compact syntax.

`$expr`

$expr allows aggregation expressions inside query matching. It is useful when the comparison depends on two fields in the same document, rather than on a fixed application value:

{
  $match: {
    $expr: {
      $gt: [
        "$paidPaise",
        "$totalPaise"
      ]
    }
  }
}

This matches documents where paidPaise is greater than totalPaise. Indexability depends on the expression form and context. Do not infer index use from the fact that the predicate appears inside $match; use explain() to see the actual behavior.

`$facet`

$facet runs multiple subpipelines against the same input. A common use is returning a page of data and metadata in one aggregation:

Example list + count:

[
  {
    $match: filter
  },
  {
    $facet: {
      data: [
        { $sort: { createdAt: -1 } },
        { $limit: 20 }
      ],
      meta: [
        { $count: "total" }
      ]
    }
  }
]

The filter runs before the facet, then the same matching input feeds both branches. The data branch returns at most twenty newest documents, while meta counts all filtered documents.

This is convenient, but an exact count over a large filtered set can be expensive. It may cancel out the efficiency gained by returning only one page of data. Do not add a total count merely because a UI designer expects one; measure the actual workload and decide whether an approximate, delayed, cached, or omitted count is acceptable.

`$count`

$count turns the number of documents reaching that point into a named field:

{
  $count: "total"
}

The position of $count matters. It counts the documents that survive every preceding stage, not the original collection or an unfiltered dataset.

`$replaceRoot` / `$replaceWith`

$replaceRoot and $replaceWith promote an embedded document to become the root document. This is useful after a lookup, unwind, or transformation when the next stages should work with the embedded document's fields as top-level fields.

Promotion changes the document shape and can discard access to the previous root unless that root was copied into the new structure first. Treat it as a deliberate shape change, especially when later stages still need the original order or tenant fields.

`$map`

$map transforms each element of an array and returns a new array. This example extracts item names without expanding the order into multiple documents:

{
  $project: {
    itemNames: {
      $map: {
        input: "$items",
        as: "item",
        in: "$$item.name"
      }
    }
  }
}

Use $map when the output should remain one document with a transformed array. That is a different shape and cost profile from $unwind, which creates one document per element.

`$filter`

$filter keeps only the array elements that satisfy a condition:

{
  $project: {
    activeItems: {
      $filter: {
        input: "$items",
        as: "item",
        cond: {
          $eq: [
            "$$item.active",
            true
          ]
        }
      }
    }
  }
}

The result remains an array on the original document. That makes $filter a better fit than $unwind when you need a subset of embedded values but do not need one result row per value.

`$reduce`

$reduce folds an array into a single value. It can implement advanced transformations such as a custom total, a constructed object, or a stateful calculation over array elements.

The flexibility comes with a maintenance cost. Prefer readable pipelines; deeply nested expressions become difficult to maintain and test. When a reduction is business-critical, cover empty arrays, null inputs, and representative mixed data in tests rather than validating only the happy path.

Conditional

Conditional operators include:

$cond
$switch
$ifNull

They are useful for classification and defaults. $cond handles a condition with two branches, $switch makes multiple cases explicit, and $ifNull supplies a fallback for null or missing values. Keep the resulting business rule visible enough that another developer can tell why a document received its classification.

String/date

Common string and date operators include:

$toLower
$concat
$dateTrunc
$dateToString
$dateDiff

Be explicit about timezone. A report grouped by "day" is using a business definition of a day, and that definition depends on the relevant timezone. Do not assume that UTC midnight matches the local business day. A boundary-time order can otherwise be counted on the previous or next day from the user's perspective.

Type conversion

Conversion operators include:

$convert
$toString
$toInt
$toDecimal

They can help clean or read historical data with inconsistent types. Repeated runtime conversion can, however, prevent index use and hide an underlying schema problem. If the same conversion is required on every request, prefer a data migration to consistent types where possible, then enforce the type for new writes.

Window functions

Modern MongoDB supports window stages and operators such as $setWindowFields. These are useful for calculations that depend on neighboring or preceding rows, including:

running totals;
ranks;
moving averages.

The basic concept might be:

partition by customer
sort by date
running total

The partition defines which documents belong to the same series, the sort defines their order, and the running calculation accumulates values along that order. Advanced analytics should be tested for memory and performance with production-like cardinality, not only a small fixture.

`$unionWith`

$unionWith combines collection or pipeline results. It can be useful for cross-collection reporting when the sources genuinely need to be presented together.

Frequent unions may signal that the data architecture needs review. The stage is not automatically wrong, but repeated cross-collection reporting can indicate that a materialized projection, a different document model, or an analytics warehouse would better serve the workload.

`$out` and `$merge`

$out and $merge write pipeline results to a collection. They are useful for:

materialized projections;
ETL;
precomputation.

Their write and replacement semantics can be dangerous or destructive, so permissions, target collections, rollout steps, and failure behavior require careful review. Do not expose arbitrary pipeline execution to a public client, especially when the pipeline can reach write-capable stages under the application's privileges.

Precomputation

Consider a report that performs this work on every request:

scan 20M orders
lookup 5 collections
group
sort

That may be the wrong architecture, even if the aggregation is syntactically correct. A user-facing request path should not repeatedly pay for a large historical scan and several joins when the result can be prepared ahead of time.

Possible alternatives include:

computed fields;
materialized summary collection;
scheduled aggregation;
event-driven projection;
analytics warehouse.

The right option depends on freshness, correctness, operational complexity, and query patterns. MongoDB aggregation is powerful, but it is not infinite free compute. If a result is requested frequently and changes less often than it is read, precomputation is worth evaluating.

Memory and disk

Some stages must hold working state and are therefore blocking stages. Common examples are:

$sort
$group

They may need significant memory, especially after a broad match, a high-cardinality group, or an expanding unwind. MongoDB can spill to disk under supported behavior and options. Disk spill can prevent a memory failure, but it can also be slow and may increase I/O pressure.

The answer is not simply to allow more spilling. Design useful indexes, reduce the input early, avoid unnecessary fields and array expansion, and consider preaggregation for recurring heavy work. Use measurements to distinguish a query that is merely correct from one that is safe for its expected load.

Pipeline optimization

MongoDB's optimizer can reorder or coalesce some stages. That does not remove the developer's responsibility to write a logically efficient pipeline. Start with a clear, selective shape:

selective $match;
reduce fields;
limit;
indexed lookups;
avoid exploding unwind unnecessarily.

These are principles, not permission to move stages blindly. A $limit before a group, for example, can change the answer. Use explain to verify the actual plan and compare documents examined, keys examined, and execution statistics against the intended behavior.

Explain

Run explain() with execution statistics when investigating an aggregation:

db.orders.explain("executionStats").aggregate([
  ...
])

Inspect:

cursor plan;
index;
docs/keys examined;
stage behavior;
execution stats.

The specific explain structure can vary by MongoDB version. Focus on the evidence in the version you are running: whether an expected index is used, how many documents and keys are examined, where time is spent, and whether a stage is expanding or buffering far more data than expected. Compare a baseline and a changed pipeline rather than relying on the presence of a familiar stage name.

Tenant security

Tenant scope is part of the correctness of a report, not just an optional filter. Always include the tenant filter before joins and reporting stages:

{
  $match: {
    tenantId: authTenantId
  }
}

The server must derive authTenantId from authenticated and authorized context. Do not let a public client pass the tenant pipeline stage or choose an arbitrary tenant value.

For $lookup, also ensure that joined data cannot cross tenant boundaries. This is especially important when foreign collection IDs are not globally isolated or when an ID alone does not prove tenant ownership. Add tenant predicates to the foreign pipeline as needed, and index the fields used by that scoped lookup.

Aggregation injection

Never accept an arbitrary pipeline from an untrusted client and execute it directly:

{
  "pipeline": [...]
}

An unrestricted pipeline can:

access unintended fields;
perform expensive work;
write via stages under privileges;
exfiltrate data.

Expose a safe reporting DSL or allowlisted parameters instead. The server should construct the permitted stages, validate values and limits, enforce tenant scope, and decide whether write-capable stages are possible. Treat pipeline input as code-like input, not as harmless JSON configuration.

Common mistakes

Watch for these failure modes when reviewing or debugging an aggregation:

lookup before selective match;
exact count on huge list by default;
unwind exploding documents unexpectedly;
group by high-cardinality field;
no index foreignField;
timezone ignored;
runtime type conversion hides dirty schema;
total aggregation on request hot path;
arbitrary client pipeline;
missing tenant scope;
assuming aggregate pipeline order always equals physical execution without explain.

When a result is wrong or slow, inspect the pipeline at stage boundaries. Confirm the input count and document shape after the match, after any unwind, after the lookup, and after the group. Then inspect the execution plan and application-generated values. This separates a data-shape bug from an indexing or workload problem.

Exercises

Monthly order totals by status.
Unwind items and calculate top SKUs.
Lookup customer display names.
Add foreign index and compare.
Build facet list+count and measure.
Group by business-local day.
Use map/filter on embedded arrays.
Build running total with window functions.
Materialize a summary collection.
Explain pipeline and identify bottleneck.

Work through the exercises in order when possible. The early tasks establish stage shape and grouping; the later tasks require you to reason about indexes, timezones, materialization, and observed execution behavior. For the measurement exercises, compare actual explain("executionStats") output before and after the change rather than treating a new index or stage arrangement as automatically beneficial.

Mastery checklist

You should be able to explain:

pipeline;
stages/expressions;
match/project/set;
group/accumulators;
sort/limit;
unwind;
lookup;
facet;
array/date expressions;
window functions;
merge/out;
optimization/explain;
precomputation;
tenant/injection safety.

You should also be able to recognize when a technically valid pipeline is unsuitable for a hot request path, when a join needs a foreign index or additional tenant predicate, and when the observed plan does not match the mental model you started with.

Learning objectives

Pipeline mental model

Stage versus expression

$match

$project

$set / $addFields

$unset

$group

Group cardinality

$sort

$limit

$skip

$unwind