mongodb-aggregation-stages-deep

Parent: MongoDB Aggregation Pipeline · Published reference · snapshot 2026-09-08 · skill mongodb-expert/references/mongodb-aggregation-stages-deep.md

↓ Facts as markdown all context files

This skill is the deep-dive companion to mongodb-aggregation-pipeline. It

These notes link each claim to its source. Some citations identify archived research reports that are no longer hosted on this site. A published reference means the content is available; it does not certify independent review or accuracy.Read the editorial policy and follow the sources before relying on a claim.

MongoDB Aggregation Stages — Deep Reference

This skill is the deep-dive companion to mongodb-aggregation-pipeline. It covers the highest-impact stages where most aggregation bugs and performance problems hide: cross-collection joins, recursive graph traversal, parallel facets, materialized-view writes, window functions, and time-series gap filling. Each section gives syntax, index/memory requirements, working examples, and the trade-offs that decide whether you should reach for the stage at all. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#mongodb-aggregation-stages-deep-reference]
When to use this skill: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#mongodb-aggregation-stages-deep-reference]
- Designing a pipeline that joins collections, traverses a hierarchy, runs parallel facets, materializes a view, computes window functions, or fills time-series gaps. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#mongodb-aggregation-stages-deep-reference]
- Diagnosing stage-level memory limits, disk spills, BSONObjectTooLarge errors, or $lookup NestedLoopJoin warnings in explain output. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#mongodb-aggregation-stages-deep-reference]
- Choosing between $lookup vs denormalization, $out vs $merge, or $bucket vs $bucketAuto. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#mongodb-aggregation-stages-deep-reference]
When NOT to use this skill: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#mongodb-aggregation-stages-deep-reference]
- Basic pipeline mechanics ($match, $project, $group, $sort, $addFields, $unwind) - see mongodb-aggregation-pipeline. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#mongodb-aggregation-stages-deep-reference]
- Index design and query planner reads - see mongodb-indexes-deep and mongodb-query-performance. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#mongodb-aggregation-stages-deep-reference]
- Atlas-specific features (Search, Data Federation, Online Archive, Charts) outside aggregation - see the corresponding mongodb-atlas-* skills. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#mongodb-aggregation-stages-deep-reference]
- Document-level schema modeling decisions unrelated to a specific aggregation stage - see mongodb-schema-design. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#mongodb-aggregation-stages-deep-reference]
Cross-link reading list: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#mongodb-aggregation-stages-deep-reference]
- mongodb-aggregation-pipeline - pipeline mental model, optimizer rewrites, $match/$project/$group/$sort basics. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#mongodb-aggregation-stages-deep-reference]
- mongodb-query-performance - $lookup join tuning, equality vs hash join hints, INDEXED vs NLJ in explain output. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#mongodb-aggregation-stages-deep-reference]
- mongodb-schema-design - denormalize-vs-$lookup decision tree. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#mongodb-aggregation-stages-deep-reference]
- mongodb-views-materialized-views - on-demand materialized views via $merge, db.createView semantics. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#mongodb-aggregation-stages-deep-reference]
- mongodb-time-series - when to use $densify and $fill with bucketed time-series collections. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#mongodb-aggregation-stages-deep-reference]
- mongodb-indexes-deep - covering indexes for the foreignField / connectToField of $lookup and $graphLookup. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#mongodb-aggregation-stages-deep-reference]

1. `$lookup` — left outer join

$lookup performs an unsharded-style left outer join from the input collection (the "local" side) to a foreign collection. The optimizer picks between four physical operators based on input cardinality, index presence, and pipeline shape: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#1-lookup-left-outer-join]
explain("executionStats") exposes the choice under $lookup.strategy / eqLookupStrategy. Treat NestedLoopJoin over hundreds of thousands of documents as a red flag - it scans the foreign collection once per input document. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#1-lookup-left-outer-join]

1.1 Equality form (`localField`/`foreignField`)

The as field is always an array, even when at most one document matches. Add { $unwind: { path: "$stock", preserveNullAndEmptyArrays: true } } to flatten while keeping unmatched rows. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#11-equality-form-localfieldforeignfield]
If localField is an array, MongoDB matches any element of the array against the scalar foreignField (no $unwind required since 3.4). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#11-equality-form-localfieldforeignfield]
The foreign collection must live in the same database. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#11-equality-form-localfieldforeignfield]
Index the foreign side on the foreignField (or compound index where foreignField is the leading key for the optimizer to choose indexed nested loop). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#11-equality-form-localfieldforeignfield]

1.2 Pipeline form with `let`

The pipeline form unlocks correlated subqueries, multi-key joins, projection, sorting, and limiting on the joined side: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#12-pipeline-form-with-let]
- Variables bound in let are referenced with the $$ prefix inside the sub-pipeline; fields of the foreign documents use the normal $ prefix. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#12-pipeline-form-with-let]
- localField/foreignField and let/pipeline can coexist - when both are present, the equality join runs first and the pipeline filters the matched documents, which can make the optimizer choose IndexedLoopJoin + NLJ for the second predicate. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#12-pipeline-form-with-let]
- For the indexed nested loop to engage on $expr pipeline joins, the foreign index must cover the equality side; range predicates inside the pipeline are evaluated post-fetch. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#12-pipeline-form-with-let]

1.3 Atlas Search inside `$lookup` (6.0+)

$search (and $searchMeta) must be the first stage in the inner pipeline. The from collection must have an Atlas Search index. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#13-atlas-search-inside-lookup-60]

1.4 Sharded collections

Before MongoDB 5.1, $lookup could not target a sharded foreign collection. From 5.1 onward, sharded $lookup is supported but the optimizer routes through the primary shard of the join collection for non-equality joins, which can serialize throughput. Strategies: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#14-sharded-collections]
- Keep the foreign collection unsharded if it is small (a few hundred MB). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#14-sharded-collections]
- Use a covering index on the shard key so $lookup can target a single shard. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#14-sharded-collections]
- For analytics, materialize via $merge to a denormalized collection. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#14-sharded-collections]

1.5 `$lookup` vs denormalization decision tree

See mongodb-schema-design for the embed-vs-reference framework. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-lookup-vs-denormalization-decision-tree]

1.6 Common `$lookup` pitfalls

The as array grows large: a $lookup that joins to a 50 000-document parent will return a 50 000-element array per input document, which can push the result over 16 MB. Use the pipeline form with $match/$limit, or add a $lookup-then-$unwind-then-aggregate sequence. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#16-common-lookup-pitfalls]
Type mismatch silently returns empty arrays: localField ObjectId vs foreignField string will never match. Normalize types first or use $expr with $toObjectId / $toString. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#16-common-lookup-pitfalls]
$lookup placed before $match loses pushdown: if the post-lookup $match is on the input side, move it before the $lookup so it shrinks the input set. The optimizer does this automatically only when the predicate is on a field that exists pre-lookup. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#16-common-lookup-pitfalls]

2. `$graphLookup` — recursive single-collection traversal

$graphLookup performs a recursive walk over the same (or another) collection, accumulating every reachable document into a single output array. It is MongoDB's answer to SQL CONNECT BY / recursive CTEs. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#2-graphlookup-recursive-single-collection-traversal]

2.1 Syntax

startWith - expression evaluated against the input document; can be a scalar or array. Each value becomes a starting point. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#21-syntax]
connectFromField - the field on the matched documents whose value feeds the next iteration's lookup. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#21-syntax]
connectToField - the field on the foreign documents to match against. Index this field. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#21-syntax]
maxDepth - inclusive upper bound on recursion depth. 0 means a single non-recursive lookup. Always set this on production queries to guard against cycles. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#21-syntax]
depthField - when present, every output document gets a numeric depthField indicating how many hops away from the start it is. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#21-syntax]
restrictSearchWithMatch - pre-filter applied at every recursion step; cannot reference $$ROOT of the originating document but can use standard query operators against the foreign collection. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#21-syntax]

2.2 Tree / hierarchy patterns

The classic org-chart query: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#22-tree-hierarchy-patterns]

2.3 Category trees and bill-of-materials

For category taxonomies, traverse from child to root via parentId: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#23-category-trees-and-bill-of-materials]
For bill-of-materials (BOM), the inverse direction reveals all sub-parts: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#23-category-trees-and-bill-of-materials]

2.4 Cycle protection

$graphLookup is naturally cycle-safe: it tracks already-visited documents by _id and will not revisit them. However, without maxDepth a deep or wide graph can still allocate huge intermediate state. Cap maxDepth at the deepest meaningful value (org chart: 10–12 levels; permission graph: 4–6 typical). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#24-cycle-protection]

2.5 Memory and performance

Memory limit: like every aggregation stage, 100 MB before spill. $graphLookup automatically writes temporary files to disk when it exceeds 100 MB only if allowDiskUse: true is set on the aggregation call (or allowDiskUseByDefault is true at server level). Otherwise the stage errors out. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#25-memory-and-performance]
Index connectToField. Without an index every recursion step is a collection scan. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#25-memory-and-performance]
restrictSearchWithMatch runs against the index when possible - use it to prune dead branches early (e.g. { active: true }, { tenantId: "..." }). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#25-memory-and-performance]
The result is one array per input document. Use $match upstream to bound starting points. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#25-memory-and-performance]

2.6 Common use cases

Org charts: who reports (directly or transitively) to a manager? [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#26-common-use-cases]
Category trees: ancestry from leaf to root, or all descendants of a category node. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#26-common-use-cases]
Permission inheritance: which groups grant a user a permission via nested group membership? [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#26-common-use-cases]
Friend-of-friend / social graph: limited-depth neighbors. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#26-common-use-cases]
Bill of materials: all sub-parts beneath an assembly. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#26-common-use-cases]
Dependency graphs: transitive package dependencies, file imports. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#26-common-use-cases]

3. `$facet` — parallel sub-pipelines

$facet runs multiple independent aggregation sub-pipelines over the same input set in a single stage, returning a single document whose fields are the array results of each branch. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#3-facet-parallel-sub-pipelines]

3.1 Syntax and structure

Output is exactly one document with three fields, each containing the sub-pipeline's array of result documents. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#31-syntax-and-structure]

3.2 Constraints

One document, ≤16 MB: the entire output document must fit in BSON's 16 MB limit. If sub-pipelines return large arrays, the aggregation fails with BSONObjectTooLarge. Bound each branch with $limit, $project to drop fields, or move heavy branches outside $facet and union the results. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#32-constraints]
No cross-branch references: sub-pipelines cannot read each other's output. They share only the upstream input. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#32-constraints]
No $facet nesting: you cannot put a $facet stage inside another $facet. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#32-constraints]
Disallowed sub-stages: $out, $merge, $collStats, $indexStats, $planCacheStats, $facet (no nesting), $geoNear, $search, $searchMeta, and $vectorSearch cannot appear inside a $facet branch. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#32-constraints]
Memory limit per branch: each branch is independent and subject to its own 100 MB / allowDiskUse ceiling. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#32-constraints]

3.3 When to reach for `$facet`

The pagination idiom: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#33-when-to-reach-for-facet]

3.5 `$facet` vs running pipelines in parallel from the client

Because every branch shares the same upstream input, $facet avoids the duplicated work of running N independent pipelines that each repeat the filter and projection stages. Trade-offs: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#35-facet-vs-running-pipelines-in-parallel-from-the-client]
- $facet wins when the upstream pipeline is expensive and shared. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#35-facet-vs-running-pipelines-in-parallel-from-the-client]
- Multiple client-side pipelines win when each branch needs different pre-filters or when the 16 MB output ceiling is at risk. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#35-facet-vs-running-pipelines-in-parallel-from-the-client]

4. `$bucket` and `$bucketAuto` — histograms

Both stages bucket documents into ranges of a numeric (or date) expression and emit one document per bucket. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#4-bucket-and-bucketauto-histograms]

4.1 `$bucket` — explicit boundaries

boundaries must be strictly increasing values of the same type as the groupBy expression results. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#41-bucket-explicit-boundaries]
Each bucket covers [boundaries[i], boundaries[i+1]) - lower-inclusive, upper-exclusive. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#41-bucket-explicit-boundaries]
A document falls into the default bucket when its groupBy value is outside the explicit range or its type does not match boundary type. Without default, out-of-range documents cause an error. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#41-bucket-explicit-boundaries]
The _id of each output document is the lower bound of the bucket (or the literal default value for the catch-all). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#41-bucket-explicit-boundaries]

4.2 `$bucketAuto` — automatic boundaries

$bucketAuto distributes documents as evenly as possible across the requested number of buckets. granularity (optional) snaps boundaries to a "preferred number" series: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#42-bucketauto-automatic-boundaries]
Boundary values are multiplied by powers of 10 so they cover the actual data range. Use POWERSOF2 for log-scale histograms (latency, file size). Use Renard for engineering-style "nice number" bin edges. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#42-bucketauto-automatic-boundaries]

4.4 Histogram + summary together

Combine with $facet to render a dashboard widget in one round-trip: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#44-histogram-summary-together]

5. `$out` vs `$merge` — writing pipeline results

Both are terminal stages: they must appear last in the pipeline. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#5-out-vs-merge-writing-pipeline-results]

5.1 `$out` — wholesale collection replacement

Drops or atomically replaces the target collection. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#51-out-wholesale-collection-replacement]
Preserves indexes on the target collection if it already exists (MongoDB re-creates the same indexes after the replacement). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#51-out-wholesale-collection-replacement]
Fails if the target is the source collection. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#51-out-wholesale-collection-replacement]
Cannot output to a sharded collection (on any version). The input/source collection may be sharded, but the $out target must be unsharded - use $merge to write into a sharded collection. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#51-out-wholesale-collection-replacement]
Does not allow you to write conditionally - every run is an unconditional overwrite. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#51-out-wholesale-collection-replacement]

5.2 `$merge` — incremental upsert

into - target collection (string or { db, coll }). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#52-merge-incremental-upsert]
on - single field name or array used as the unique key. Default is _id. If you supply a custom on, a unique index must back it. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#52-merge-incremental-upsert]
let - variables usable by the whenMatched pipeline. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#52-merge-incremental-upsert]
whenMatched - behavior when a document with the same on value exists in the target: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#52-merge-incremental-upsert]
- "merge" (default) - $set-style field merge (incoming fields overwrite; existing-only fields are kept). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#52-merge-incremental-upsert]
- "replace" - replace the matched document entirely (preserves _id). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#52-merge-incremental-upsert]
- "keepExisting" - keep target untouched. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#52-merge-incremental-upsert]
- "fail" - error on any collision. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#52-merge-incremental-upsert]
- [ ...pipeline ] - custom update pipeline. Reference incoming fields via $$new and existing fields with normal $ paths. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#52-merge-incremental-upsert]
whenNotMatched - behavior when no match: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#52-merge-incremental-upsert]
- "insert" (default) - insert the incoming document. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#52-merge-incremental-upsert]
- "discard" - drop unmatched results silently. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#52-merge-incremental-upsert]
- "fail" - error if any input has no match in the target. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#52-merge-incremental-upsert]

5.3 Materialized view refresh patterns

Full rebuild on schedule (use $out): [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#53-materialized-view-refresh-patterns]
Incremental refresh (use $merge with a watermark): [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#53-materialized-view-refresh-patterns]
The custom whenMatched pipeline lets the new run add to the existing total rather than replace it - essential for additive rollups where you process only the delta. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#53-materialized-view-refresh-patterns]

5.4 Idempotency rules

Set _id (or the on key) deterministically from the source data so re-running the same window produces identical keys. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#54-idempotency-rules]
For additive metrics with incremental refresh, the math must compensate if the same source documents are seen twice. Either (a) use a strict watermark that never overlaps, or (b) make whenMatched "replace" and aggregate over the full window each run. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#54-idempotency-rules]
For whenMatched: "merge", missing fields in the new document do not remove existing fields. Use "replace" if you want hard overwrite. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#54-idempotency-rules]

5.5 Pitfalls

Same-collection $merge can loop: writing back to the source collection in a way that changes document size or shard-key value can cause documents to be re-read by the same pipeline. MongoDB docs explicitly warn that this can result in documents being processed multiple times or an infinite loop. Prefer $out or write to a separate collection. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#55-pitfalls]
Unique index required for non-default on: without it, $merge fails at execution time. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#55-pitfalls]
$merge/$out are not transactional with the source reads: a client reading the source mid-merge sees a mix. Wrap downstream consumers around a "last refresh at" marker. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#55-pitfalls]
Sharding: $merge is supported into sharded targets; the on key must include the shard key (or be the shard key). $out cannot target a sharded collection on any version - use $merge when the destination is sharded. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#55-pitfalls]

6. `$setWindowFields` — window functions

Introduced in 5.0, $setWindowFields brings SQL-style window functions to MongoDB: running totals, moving averages, lead/lag, ranks, derivatives, integrals - all computed over a window of documents while every input document survives in the output (unlike $group). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#6-setwindowfields-window-functions]

6.1 Anatomy

partitionBy - segments the input into independent groups. Each partition gets its own sliding window. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#61-anatomy]
sortBy - orders documents within a partition. Window boundaries are defined relative to this order. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#61-anatomy]
window - defines the inclusive range of documents/values the accumulator covers: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#61-anatomy]
- documents: [a, b] - position-based, where "current", integers, or "unbounded" are valid bounds. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#61-anatomy]
- range: [a, b] - value-based on the sortBy field; for date sort fields, supply unit (e.g. "day"). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#61-anatomy]

You cannot mix documents and range in the same window. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#61-anatomy]

6.2 Operator catalog

Accumulators usable inside $setWindowFields.output: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#62-operator-catalog]

6.3 Ranking semantics

For sortBy values [7, 9, 9, 10]: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#63-ranking-semantics]
- $rank → 1, 2, 2, 4 (gap after tie) [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#63-ranking-semantics]
- $denseRank → 1, 2, 2, 3 (no gap) [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#63-ranking-semantics]
- $documentNumber → 1, 2, 3, 4 (always unique) [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#63-ranking-semantics]

6.6 Derivative and integral for time-series

$derivative requires unit for time-based sortBy fields and returns the rate per that unit. $integral returns the trapezoidal area under the curve over the window. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#66-derivative-and-integral-for-time-series]

6.7 Memory limit

$setWindowFields is subject to the 100 MB / allowDiskUse rule per partition, not per pipeline overall. Very wide partitions with unbounded windows can spill. Reduce partition size by adding partitionBy keys, or bound the window with documents / range. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#67-memory-limit]

7. `$densify` — close gaps in numeric / time ranges

$densify (5.1+) inserts synthetic documents to make a sequence dense along a numeric or date field. The synthetic documents carry only the densified field (and any partition fields); other fields are absent and typically populated by a following $fill stage. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#7-densify-close-gaps-in-numeric-time-ranges]

7.1 Syntax

"full" - span the global min/max of the field across all input documents (one synthetic sequence covers every partition). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#71-syntax]
"partition" - span the min/max within each partition independently. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#71-syntax]
[lower, upper] - explicit literal bounds. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#71-syntax]

unit is required when field is a date and optional for numeric fields. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#71-syntax]

7.2 Hourly observations example

Result: every device gets one document per hour from its earliest to its latest reading. Missing hours appear as { deviceId, ts } with no other fields. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#72-hourly-observations-example]

7.3 Numeric densification example

Use cases: tax periods, fiscal weeks, leaderboard rank slots - anywhere a key is expected to be contiguous but real data has holes. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#73-numeric-densification-example]

7.4 Rules

$densify does not modify existing documents; it only adds new ones. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#74-rules]
Inserted documents inherit only partitionByFields and the densified field. Use $fill next to populate value fields. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#74-rules]
If two existing documents collide with the same generated key, the existing documents survive - $densify is a no-op for those positions. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#74-rules]
$densify must precede operations that assume dense input ($setWindowFields for moving averages, charts, time-aligned joins). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#74-rules]

8. `$fill` — populate null and missing values

$fill (5.3+) sets a value for fields that are null or missing. It is typically paired with $densify to fill in the synthetic gap rows, but works on any pipeline. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#8-fill-populate-null-and-missing-values]

8.1 Methods

For linear and locf, you must supply sortBy so ordering is defined. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#81-methods]

8.3 Time-series gap fill (densify + fill)

The canonical pattern from the MongoDB blog: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#83-time-series-gap-fill-densify-fill]

8.4 `$linearFill` expression (vs `$fill`'s `linear` method)

$linearFill is also available as a window function inside $setWindowFields. Use the standalone $fill stage when you want a self-contained gap-fill step; use $linearFill inside $setWindowFields when you are already partitioning / sorting for other window operators. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#84-linearfill-expression-vs-fills-linear-method]

8.5 Rules

For locf, the first run of nulls before any non-null value remains null - there is no value to carry forward. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#85-rules]
For linear, the same boundary rule applies and additionally: leading and trailing nulls (before the first or after the last non-null) remain null because interpolation requires two endpoints. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#85-rules]
$fill only writes the output fields you specify. Other fields are untouched. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#85-rules]
$fill's partitionBy / partitionByFields must match the upstream $densify partition keys, or you will see synthetic rows that never get filled. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#85-rules]

9. `$unionWith` — UNION ALL across collections

$unionWith (4.4+) appends documents from another collection (and optionally its own pipeline) to the running stream. It is MongoDB's equivalent of SQL UNION ALL. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#9-unionwith-union-all-across-collections]

9.1 Syntax

pipeline is optional and runs on the union'd collection's documents before they are appended. The current pipeline's documents pass through untouched and the union'd documents are appended at this point in the pipeline. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#91-syntax]

9.3 Removing duplicates (`UNION` not `UNION ALL`)

$unionWith includes duplicates. To deduplicate, follow it with $group: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#93-removing-duplicates-union-not-union-all]

9.4 Rules

All collections involved must be in the same database. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#94-rules]
The combined stream of documents may have heterogeneous shapes. Project to a common shape before grouping or merging. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#94-rules]
$unionWith cannot be used inside a multi-document transaction. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#94-rules]
Disallowed stages inside the inner pipeline: $out, $merge. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#94-rules]
Each collection scan inside $unionWith is independent - index it appropriately if you push down a $match. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#94-rules]

9.5 Use cases

Sharded archive + hot collection: union an archive collection with the live collection for unified reporting. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#95-use-cases]
Cross-tenant report: union per-tenant collections (when the schema forbids a single collection). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#95-use-cases]
Migration: dual-read from old and new collections during cutover. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#95-use-cases]
Heterogeneous corpora: union "cases", "slack", "meetings" into a single search/scoring pipeline. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#95-use-cases]

10.1 The 100 MB-per-stage rule

Each blocking aggregation stage may use up to 100 MB of RAM for its in-memory state. Blocking stages include $sort, $group, $bucket, $bucketAuto, $setWindowFields, $facet (per branch), $graphLookup, and $lookup (when buffering). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#101-the-100-mb-per-stage-rule]
When a stage exceeds the limit: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#101-the-100-mb-per-stage-rule]
- MongoDB 6.0+ with allowDiskUseByDefault: true (the default for Atlas / current community builds) - the stage spills to temporary files transparently. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#101-the-100-mb-per-stage-rule]
- allowDiskUseByDefault: false - the stage errors out with QueryExceededMemoryLimitNoDiskUseAllowed unless the call explicitly passes allowDiskUse: true. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#101-the-100-mb-per-stage-rule]
- Pre-6.0 - the stage always errors unless allowDiskUse: true is set on the aggregation command. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#101-the-100-mb-per-stage-rule]

10.2 Enabling disk spill

Server: db.adminCommand({ setParameter: 1, allowDiskUseByDefault: true }) [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#102-enabling-disk-spill]
Atlas: allowDiskUseByDefault is enabled by default; it can be toggled per cluster. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#102-enabling-disk-spill]
Drivers: AggregateOptions.allowDiskUse(true) (Java), aggregate({ allowDiskUse: true }) (Node), aggregate(..., allowDiskUse=True) (Python). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#102-enabling-disk-spill]

10.3 `explain("executionStats")` for spill detection

Key signals in the output: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#103-explainexecutionstats-for-spill-detection]
- usedDisk: true - at least one stage spilled to temporary files. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#103-explainexecutionstats-for-spill-detection]
- spillFileSize / spilledBytes / spilledRecords - magnitude of the spill (newer versions). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#103-explainexecutionstats-for-spill-detection]
- executionTimeMillisEstimate per stage - locate the slow stage. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#103-explainexecutionstats-for-spill-detection]
- nReturned, totalKeysExamined, totalDocsExamined - index health. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#103-explainexecutionstats-for-spill-detection]
- $lookup.strategy / eqLookupStrategy - "IndexedLoopJoin" (good), "NestedLoopJoin" (bad on large input), "HashJoin", or "DynamicIndexedLoopJoin". [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#103-explainexecutionstats-for-spill-detection]

10.4 The 16 MB document limit

Every document at every stage must fit in 16 MB. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#104-the-16-mb-document-limit]
$facet output is one document containing every branch's array - this is where the 16 MB ceiling hits hardest. Bound each branch. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#104-the-16-mb-document-limit]
$group with $push can blow up an array beyond 16 MB. Use $accumulator chunks, $bucket + $group, or write to a collection with $merge instead of returning to the client. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#104-the-16-mb-document-limit]
Pipelines that join with $lookup and then $unwind are fine; pipelines that $lookup and then leave the joined array intact may overflow. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#104-the-16-mb-document-limit]

10.5 Stage ordering for memory and performance

The optimizer reorders stages where it can, but you should write pipelines that already minimize the working set: [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#105-stage-ordering-for-memory-and-performance]
- $match first - filter early, ideally on an indexed field. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#105-stage-ordering-for-memory-and-performance]
- $project / $unset next - drop fields you do not need. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#105-stage-ordering-for-memory-and-performance]
- $sort before $group only if the sort is needed for output, and only after the dataset is reduced. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#105-stage-ordering-for-memory-and-performance]
- $lookup / $graphLookup after pruning - every joined document multiplies cost. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#105-stage-ordering-for-memory-and-performance]
- Terminal $out / $merge last. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#105-stage-ordering-for-memory-and-performance]

10.6 Sharded aggregation specifics

Stages run on each shard up to the first split point (a stage that requires a global view, like $group without the shard key in _id, $sort over the full result, or $facet). [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#106-sharded-aggregation-specifics]
After the split point, intermediate results stream to the mongos (or the merging shard) for the rest of the pipeline. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#106-sharded-aggregation-specifics]
$out / $merge execute on the merging side; the target collection may need to be unsharded or $merge.on must include the shard key. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#106-sharded-aggregation-specifics]

13. Anti-patterns

$lookup without an index on foreignField - turns every join into an O(N*M) collection scan. Always index the foreign side. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#13-anti-patterns]
$graphLookup without maxDepth - unbounded recursion can hit the 100 MB ceiling or, with disk spill, run for hours on dense graphs. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#13-anti-patterns]
$facet branches without $limit - easiest way to hit BSONObjectTooLarge. Cap arrays in every branch. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#13-anti-patterns]
$merge into the source collection - risk of infinite loops if the merge changes document size or shard key. Write to a separate collection. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#13-anti-patterns]
$out to a sharded target - unsupported on any version; $out cannot write to a sharded collection. Use $merge when the destination is sharded. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#13-anti-patterns]
$setWindowFields over one giant partition - single-partition window over millions of documents will spill. Add a partitionBy key or split by tenant / day. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#13-anti-patterns]
$densify without a following $fill - produces ghost rows with missing fields that downstream code may not expect. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#13-anti-patterns]
$unionWith followed by a $lookup - the join may need to scan twice, once per input branch, without index pushdown for one side. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#13-anti-patterns]
Pipelines that ignore explain - without executionStats, you cannot detect usedDisk, NestedLoopJoin, or unindexed sorts. [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#13-anti-patterns]

15. Sources

MongoDB Docs - $lookup (aggregation stage) [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]
MongoDB Docs - $graphLookup (aggregation stage) [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]
MongoDB Docs - $facet (aggregation stage) [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]
MongoDB Docs - $bucket (aggregation stage) [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]
MongoDB Docs - $bucketAuto (aggregation stage) [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]
MongoDB Docs - $merge (aggregation stage) [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]
MongoDB Docs - $out (aggregation stage) [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]
MongoDB Docs - $setWindowFields (aggregation stage) [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]
MongoDB Docs - $densify (aggregation stage) [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]
MongoDB Docs - $fill (aggregation stage) [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]
MongoDB Docs - $linearFill (expression) [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]
MongoDB Docs - $unionWith (aggregation stage) [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]
MongoDB Docs - Aggregation Pipeline Limits [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]
MongoDB Blog - Introducing Gap Filling For Time Series Data in MongoDB 5.3 [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]
MongoDB Developer Hub - Preparing time-series data with $densify and $fill [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]
Practical MongoDB Aggregations Book - Faceted Classification [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]
MongoDB Performance Tuning (Guy Harrison) - Getting started with MongoDB 5.0 window functions [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]
Percona - Window Functions in MongoDB 5.0 [archived source: https://llms-explorer.com/sources/mdb-context-hub/mongodb-aggregation-stages-deep/#15-sources]

Where this helps

Designing a pipeline that must join across collections at query time and needs to choose correctly between the equality form of $lookup and the pipeline+let form for correlated subqueries. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Building an org chart, category tree, or bill-of-materials feature that requires recursive traversal within a single collection ($graphLookup). [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Building a single-round-trip dashboard widget that needs several independent aggregations, such as a histogram plus a summary, over the same filtered input ($facet). [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Diagnosing a pipeline that is hitting BSONObjectTooLarge, spilling to disk, or showing NestedLoopJoin in explain output, and needing to know which stage and which fix applies. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.

Project ideas

Build a materialized reporting view that refreshes nightly with $out and a second view that updates incrementally with $merge, to see firsthand when each tool is the wrong choice for the other's job. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Build a permission-inheritance checker using $graphLookup with restrictSearchWithMatch and a bounded maxDepth, then intentionally remove maxDepth on a test dataset to see the stage's memory behavior. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Build a dashboard $facet stage that returns a histogram ($bucketAuto) and a running total ($setWindowFields) in one round trip, then push output size past 16 MB on purpose to see BSONObjectTooLarge. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Take a sparse hourly time-series collection and build a $densify + $fill pipeline to produce dense hourly buckets for charting, then feed it into $setWindowFields for a moving average. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.

Common mistakes

Forgetting maxDepth on a $graphLookup against a large or unbounded graph, letting recursion run until it hits the memory ceiling or spills to disk for hours. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Leaving a $facet branch unbounded instead of adding $limit, which is the most common way to hit the 16 MB BSONObjectTooLarge ceiling. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Running $merge back into its own source collection when the write can change document size or shard-key value, risking documents being reprocessed multiple times or looping. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Reading explain output without executionStats and therefore missing usedDisk, NestedLoopJoin, or an unindexed sort that is quietly degrading the pipeline. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.

Known issues

Every blocking stage — $sort, $group, $bucket, $facet per branch, $graphLookup, buffering $lookup — is capped at 100 MB before it must spill to disk or error, and the cap applies per stage (or per partition for $setWindowFields), not per pipeline. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
$out cannot write to a sharded collection on any MongoDB version; a pipeline that needs a sharded target has to use $merge instead. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
$lookup silently returns an empty array on a type mismatch between localField and foreignField (e.g. ObjectId vs string) rather than raising an error, which can hide a bug for a long time. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Sharded $lookup (5.1+) routes non-equality joins through the primary shard of the joined collection, which can serialize throughput in ways that aren't obvious from the pipeline syntax alone. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.

Children

$lookup
$graphLookup
$facet
$bucket-bucketAuto
$setWindowFields
$densify-$fill
$unionWith
allowDiskUse-100MB-stage (frontier, viability 2.85 / 5, below research gate)

Frontier under this node: allowDiskUse-100MB-stage (2.85), explain-executionStats-usedDisk (2.85)

← the whole tree · 3D view· how to read this page