Study guide
4.4 Materialization & Window Operations ($out, $merge, $setWindowFields)
MongoDB Associate Developer Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Document Model & MongoDB Architecture
1.1 Document Model Fundamentals & BSON Data Types 1.2 Databases, Collections & Schema Flexibility 1.3 Distributed Architecture Fundamentals: Replica Sets & Sharded Clusters
2 Chapter 2: CRUD Operations: Create & Read
2.1 Document Insertion Operations 2.2 Query Selectors & Filter Operators 2.3 Array Queries & Element Evaluation 2.4 Projections, Cursor Methods & Collation
3 Chapter 3: CRUD Operations: Update & Delete
3.1 Field Update Operators 3.2 Array Update Operators & Positional Modifiers 3.3 Document Replacement & Deletion Operations 3.4 Atomic Find-and-Modify Operations
4 Chapter 4: Aggregation Framework Mastery
4.1 Pipeline Fundamentals & Core Stages ($match, $project, $addFields) 4.2 Grouping, Reshaping & Array Manipulation ($group, $unwind, $replaceWith) 4.3 Multi-Document Joins & Faceted Pipelines ($lookup, $facet, $bucket) 4.4 Materialization & Window Operations ($out, $merge, $setWindowFields) 4.5 Atlas Search: Search Index Definitions & $search Queries
5 Chapter 5: Indexing Strategies & Performance Optimization
5.1 Index Types & Storage Mechanics 5.2 Compound Indexes & the Equality-Sort-Range (ESR) Rule 5.3 Multikey Indexes & Array Indexing Constraints 5.4 Specialized Index Types: TTL, Partial, Unique & Text 5.5 Query Optimization & Explain Plan Analysis
6 Chapter 6: Data Modeling & Schema Design Patterns
6.1 Embedding vs. Referencing Tradeoffs 6.2 Core Schema Design Patterns 6.3 Schema Validation with JSON Schema
7 Chapter 7: Application Development & Driver Integration
7.1 Connection Strings & Connection Pool Management 7.2 Driver CRUD Paradigms & Object Mapping 7.3 Cursor Batching, Streaming & Memory Handling 7.4 Write Concerns, Read Concerns & Retryable Operations
8 Chapter 8: Developer Tooling & Administration Essentials
8.1 mongosh Scripting & Administrative Commands 8.2 Data Ingestion & Export Utilities 8.3 Backup, Restore & Performance Telemetry 8.4 Atlas Sample Dataset & Data Explorer
4.4 Materialization & Window Operations ($out, $merge, $setWindowFields)
Key Takeaways Explain This Topic AI
Aggregation pipeline results can be materialized directly into persistent MongoDB collections using either ' o u t ′ o r ′ out' or ' o u t ′ o r ′ merge' as the final pipeline stage.
'$out' atomically replaces an entire target collection with pipeline output; it cannot output to sharded collections or across different databases in older MongoDB versions.
'$merge' provides flexible, fine-grained on-demand materialization, supporting writes to sharded collections, cross-database targets, and custom matching strategies ('whenMatched' and 'whenNotMatched').
The '$setWindowFields' stage computes rolling averages, cumulative sums, and ranking metrics across partitioned document windows without collapsing the document stream like '$group'.
Window partition ranking functions include ' r a n k ′ , ′ rank', ' r an k ′ , ′ denseRank', and '$documentNumber', while sliding windows support both 'documents' (row-count offsets) and 'range' (field-value offsets) boundaries.
Last updated: September 2026
4.4 Materialization & Window Operations ($out, $merge, $setWindowFields)
In analytical systems and high-throughput production environments, executing complex aggregation pipelines repeatedly on real-time transactional collections can strain server resources. To optimize read performance, MongoDB provides materialization stages ( $out and $merge ) that persist transformed aggregation results into permanent collections for fast indexed retrieval.
Additionally, MongoDB provides $setWindowFields , an advanced analytical stage that performs window operations (such as rolling averages, cumulative totals, and ranking) across document partitions without collapsing documents into grouped rows.
1. Materialization Overview: $out vs. $merge
Materialization is the process of writing the output documents of an aggregation pipeline directly into a collection on disk rather than returning a transient cursor to the client application. +-----------------------------------------------------------------------------------------+
- | MongoDB Materialization Comparison |
- | |
- | $out : Total Collection Replacement (Drop & Replace Target Atomically) |
- | $merge : Fine-Grained Incremental Upsert / Merge / Replace on Key Match |
- +-----------------------------------------------------------------------------------------+
- High-Level Feature Comparison
- Feature $out $merge
- Write Behavior Replaces entire target collection Incremental update / insert / merge
- Target Collection Support Un-sharded collections only Sharded and un-sharded collections
- Database Scope Same or different database Same or different database
- On Key Match Action N/A (Overwrites whole collection) replace , keepExisting , merge , fail , pipeline
- On Key No-Match Action N/A insert , discard , fail
- Pipeline Position Must be the final stage Must be the final stage
- Preserves Existing Indexes Drops custom indexes on target Preserves existing target indexes
- 2. The $out Stage: Atomic Collection Replacement
The $out stage writes the resulting documents of the aggregation pipeline to a specified collection. If the target collection already exists, $out replaces it atomically upon pipeline completion.
Syntax // In same database:
{ $out: "<targetCollection>" }
// To a different database:
- { $out: { db: "<targetDatabase>", coll: "<targetCollection>" } }
- Atomicity & Mechanics of $out
Temporary Staging : MongoDB writes the pipeline results into a temporary collection in the target database as documents stream through.
Atomic Rename : When the pipeline completes successfully, MongoDB atomically renames the temporary collection to the target collection name ( renameCollection with drop target).
Failure Protection : If the pipeline throws an error midway through execution, the temporary collection is discarded, and the existing target collection remains untouched. // Materializing daily sales summary to a reporting collection
- db.orders.aggregate([
- {
- $match: { orderDate: { $gte: ISODate("2026-09-01T00:00:00Z") } }
- },
- {
- $group: {
- _id: "$region",
- dailyRevenue: { $sum: "$totalAmount" },
- orderCount: { $sum: 1 }
- }
- },
- {
- $out: { db: "reporting_db", coll: "daily_regional_summary" }
- }
- ]);
Exam Trap: Because $out replaces the entire target collection, any custom secondary indexes previously built on the target collection are dropped and lost (except the default _id index).
3. The $merge Stage: Fine-Grained Incremental Materialization
Introduced in MongoDB 4.2, the $merge stage provides flexible, production-grade output materialization. Unlike $out , $merge can update existing documents in place, insert new records, merge subdocuments, or execute custom update pipelines against target collections.
- Syntax {
- $merge: {
- into: "<targetCollection>" | { db: "<dbName>", coll: "<collName>" },
- on: "<identifierField>" | [ "<field1>", "<field2>" ],
- let: { <var1>: "$<expression1>" },
- whenMatched: "replace" | "keepExisting" | "merge" | "fail" | [ <pipeline> ],
- whenNotMatched: "insert" | "discard" | "fail"
- }
- }
- Configuration Options
into (String or Object, Required) : The target collection and optional database.
on (String or Array, Optional, Default: "_id" ) : The field or combination of fields that uniquely identify a document. The target collection must have a unique index matching the on specification.
whenMatched (String or Pipeline Array, Optional, Default: "merge" ) :
"replace" : Replaces the existing document in the target collection with the aggregation output document.
"merge" : Merges the fields of the output document into the matching target document (new fields added, existing overwritten, unmentioned fields preserved).
"keepExisting" : Keeps the target document unchanged and discards the new pipeline output.
"fail" : Throws an error and halts the aggregation pipeline.
[ <pipeline> ] : Executes an aggregation update pipeline on the matching target document.
whenNotMatched (String, Optional, Default: "insert" ) :
"insert" : Inserts the output document into the target collection.
"discard" : Discards the output document without inserting.
"fail" : Throws an error and halts the aggregation pipeline.
- Example: Incremental ETL Pipeline with $merge db.orders.aggregate([
- {
- $match: { lastModified: { $gte: ISODate("2026-09-02T00:00:00Z") } }
- },
- {
- $group: {
- _id: "$customerId",
- totalSpentToday: { $sum: "$totalAmount" },
- transactionCountToday: { $sum: 1 },
- lastActive: { $max: "$orderDate" }
- }
- },
- {
- $merge: {
- into: "customer_lifetime_stats",
- on: "_id", // Matches on customerId
- whenMatched: [
- {
- $set: {
- lifetimeSpend: { $add: [ "$lifetimeSpend", "$$new.totalSpentToday" ] },
- totalTransactions: { $add: [ "$totalTransactions", "$$new.transactionCountToday" ] },
- lastActive: "$$new.lastActive"
- }
- }
- ],
- whenNotMatched: "insert"
- }
- }
- ]);
- 4. Window Operations: $setWindowFields
In standard aggregations, $group computes summary statistics by collapsing multiple documents into a single summary document per partition. In contrast, window functions compute metrics over a designated span of documents (a "window") without changing the number of documents in the stream .
Introduced in MongoDB 5.0, the $setWindowFields stage brings SQL-style window functions to MongoDB.
- Syntax {
- $setWindowFields: {
- partitionBy: <partitioning expression>,
- sortBy: { <sortField1>: 1 | -1 },
- output: {
- <outputField1>: {
- <windowOperator>: <operatorArgs>,
- window: {
- documents: [ <lowerBound>, <upperBound> ] |
- range: [ <lowerBound>, <upperBound> ],
- unit: "<timeUnit>"
- }
- }
- }
- }
- }
- Window Operators Taxonomy +---------------------------------------------------------------------------------+
- | $setWindowFields Operator Types |
- | |
- | 1. Ranking Functions : $rank, $denseRank, $documentNumber |
- | 2. Shifting Functions : $shift, $linearFill, $expMovingAvg |
- | 3. Aggregate Functions : $sum, $avg, $min, $max, $stdDevSamp, $covariancePop |
- +---------------------------------------------------------------------------------+
- 1. Ranking Operators ( $rank , $denseRank , $documentNumber )
Operator Description & Behavior with Ties
$documentNumber Assigns a strictly sequential integer ( 1, 2, 3, 4 ) to each document regardless of ties.
$rank Assigns identical ranks to ties, skipping subsequent ranks ( 1, 2, 2, 4 ).
$denseRank Assigns identical ranks to ties without skipping subsequent ranks ( 1, 2, 2, 3 ).
- Example: Ranking Sales Reps within Departments db.employees.aggregate([
- {
- $setWindowFields: {
- partitionBy: "$department",
- sortBy: { quarterlySales: -1 },
- output: {
- deptRank: {
- $denseRank: {}
- }
- }
- }
- }
- ]);
- 2. Cumulative Sums and Rolling Averages with Window Frames
Window frames define the boundaries of the calculation relative to the current document:
documents: [ -N, "current" ] : Evaluates across N N N physical documents prior to the current document plus the current document.
range: [ -N, "current" ], unit: "day" : Evaluates across documents where the sorted date/numeric field falls within N N N units of the current document's value.
- Example: 3-Day Moving Average & Cumulative Revenue db.dailyRevenue.aggregate([
- {
- $setWindowFields: {
- partitionBy: "$storeId",
- sortBy: { date: 1 },
- output: {
- cumulativeRevenue: {
- $sum: "$dailyAmount",
- window: { documents: [ "unbounded", "current" ] } // Running total from beginning of partition
- },
- threeDayMovingAvg: {
- $avg: "$dailyAmount",
- window: { documents: [ -2, "current" ] } // Current day + past 2 days
- }
- }
- }
- }
- ]);