4.4 Materialization & Window Operations ($out, $merge, $setWindowFields)

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

---

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

1. Temporary Staging: MongoDB writes the pipeline results into a temporary collection in the target database as documents stream through.
2. Atomic Rename: When the pipeline completes successfully, MongoDB atomically renames the temporary collection to the target collection name (renameCollection with drop target).
3. 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

1. into (String or Object, Required): The target collection and optional database.
2. 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.
3. 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.
4. 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)

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$ 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$ 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
        }
      }
    }
  }
]);