MongoDB Academy · Lesson

$out and $merge: Writing Pipeline Results

Learners will direct aggregation output into a new or existing collection using $out and $merge for ETL and materialised views.

$out and $merge: Writing Pipeline Results is a free MongoDB Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the MongoDB Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Writing Pipeline Results to Collections

By default, aggregation pipeline results are returned to the client as a cursor. Sometimes you want to persist the results into a MongoDB collection for later use as a materialised view, a reporting cache, or an ETL target. MongoDB provides two stages for this: $out, which replaces a target collection atomically, and $merge, which upserts or merges results into an existing collection.

The $out Stage

$out writes all pipeline output documents to a new or existing collection in an atomic operation. If the target collection exists, $out replaces it entirely with the new results—the old collection is dropped and the new one takes its place atomically. If it doesn't exist, MongoDB creates it. $out must be the last stage in the pipeline and returns nothing to the client.

// Materialise monthly sales summary into its own collection
db.orders.aggregate([
  { $match: { status: 'completed' } },
  { $group: {
    _id: {
      year: { $year: '$createdAt' },
      month: { $month: '$createdAt' }
    },
    totalRevenue: { $sum: '$amount' },
    orderCount: { $sum: 1 }
  }},
  { $sort: { '_id.year': 1, '_id.month': 1 } },
  { $out: 'monthly_sales_summary' }  // last stage, writes to collection
]);

$out Atomicity Guarantee

$out provides atomic replacement: it writes all results to a temporary collection first, then renames the temporary collection to the target name in a single atomic operation. This means readers of the target collection always see either the old complete data or the new complete data—never a partial result. This makes $out safe for production use as a daily refresh of a reporting collection.

// Readers of 'monthly_sales_summary' always see complete data
// Even while $out is running, they see the previous full snapshot
// Only after $out completes does the new snapshot become visible

// Scheduled nightly refresh pattern:
// 1. Run at midnight: aggregate([...stages..., { $out: 'sales_report' }])
// 2. During the day: app reads from 'sales_report' (fast, pre-computed)
// 3. Next midnight: repeat

db.orders.aggregate([
  ...stages,
  { $out: 'sales_report' }  // atomic swap
]);

$out to a Different Database

Since MongoDB 4.4, $out supports an object syntax that lets you write to a collection in a different database. Specify both db (database name) and coll (collection name) in the object form. This is useful for separating operational and reporting databases on the same cluster.

// Write to a collection in a different database
db.orders.aggregate([
  { $match: { status: 'completed' } },
  { $group: { _id: '$region', revenue: { $sum: '$amount' } } },
  {
    $out: {
      db: 'reporting',          // target database
      coll: 'regional_revenue'  // target collection
    }
  }
]);
// Result is in reporting.regional_revenue

The $merge Stage

Introduced in MongoDB 4.2, $merge is more flexible than $out: instead of replacing the target collection, it upserts each output document into the target. You define the merge key (which field(s) identify existing documents), and for each output document, MongoDB decides whether to insert it, update an existing document, replace it, fail, or keep the existing document.

// Upsert daily stats into a persistent stats collection
db.events.aggregate([
  { $group: {
    _id: {
      date: { $dateToString: { format: '%Y-%m-%d', date: '$timestamp' } },
      eventType: '$type'
    },
    count: { $sum: 1 }
  }},
  {
    $merge: {
      into: 'daily_event_stats',
      on: ['_id'],              // match key
      whenMatched: 'replace',   // update matching docs
      whenNotMatched: 'insert'  // insert new docs
    }
  }
]);

$merge whenMatched Options

The whenMatched option controls what happens when a pipeline output document matches an existing document in the target collection. Options are: 'replace' — overwrite the existing document; 'merge' — merge fields (existing fields not in the output are kept); 'keepExisting' — do nothing, preserve the existing document; 'fail' — throw an error; or a custom pipeline for complex update logic.

// whenMatched: 'merge' - only update changed fields, keep others
db.orders.aggregate([
  { $project: { userId: 1, orderCount: { $literal: 1 } } },
  { $merge: {
    into: 'user_order_counts',
    on: 'userId',
    whenMatched: [{ $set: { orderCount: { $add: ['$orderCount', '$$new.orderCount'] } } }],
    whenNotMatched: 'insert'
  }}
]);
// Custom pipeline in whenMatched adds to existing count instead of replacing

$merge whenNotMatched Options

The whenNotMatched option controls what happens when a pipeline output document has no match in the target collection. Options are: 'insert' — add the new document to the target; 'discard' — ignore it (don't insert); or 'fail' — throw an error. The most common combination is whenMatched: 'replace', whenNotMatched: 'insert', which implements a full upsert.

// Full upsert: replace existing, insert new
{ $merge: {
  into: 'product_stats',
  on: '_id',
  whenMatched: 'replace',
  whenNotMatched: 'insert'
}}

// Update only existing, silently skip new
{ $merge: {
  into: 'product_stats',
  on: '_id',
  whenMatched: 'replace',
  whenNotMatched: 'discard'  // only update existing products
}}

Incremental Materialised Views With $merge

One of the most powerful patterns enabled by $merge is incremental materialised views: instead of recomputing the entire summary every time, you run the pipeline only on new data (using a $match on a recent timestamp) and merge the incremental results into the summary collection. This makes refresh much faster for large datasets.

// Incremental update: only process last hour of orders
const oneHourAgo = new Date(Date.now() - 3600000);

db.orders.aggregate([
  { $match: { createdAt: { $gte: oneHourAgo } } },  // only NEW data
  { $group: {
    _id: '$productId',
    recentRevenue: { $sum: '$amount' },
    recentOrders: { $sum: 1 }
  }},
  { $merge: {
    into: 'product_revenue',
    on: '_id',
    whenMatched: [
      { $set: {
        totalRevenue: { $add: ['$totalRevenue', '$$new.recentRevenue'] },
        totalOrders: { $add: ['$totalOrders', '$$new.recentOrders'] }
      }}
    ],
    whenNotMatched: 'insert'
  }}
]);

$out vs $merge: When to Use Each

Use $out when you want a complete snapshot replacement: the target should always be the full, fresh result of the pipeline—no partial updates, no retained history. Good for nightly batch reports that replace yesterday's data. Use $merge when you want to incrementally update, append to, or upsert into an existing collection without losing data that was not re-computed in this run. Good for real-time or near-real-time aggregation that runs hourly.

// $out: nightly full replace
// Run at midnight: compute full summary, atomically replace target
{ $out: 'monthly_report' }

// $merge: hourly incremental update
// Run every hour: compute last hour's delta, merge into running total
{ $merge: {
  into: 'running_totals',
  on: '_id',
  whenMatched: 'merge',
  whenNotMatched: 'insert'
}}

Permissions and Indexes on $out/$merge Targets

When $out recreates a collection, it drops all indexes on the target (except the _id index). You must recreate any secondary indexes after an $out run. $merge preserves existing indexes on the target collection. This is another reason to prefer $merge for frequently refreshed collections—you don't lose your indexes on each run.

// After $out, recreate indexes on the refreshed collection
db.orders.aggregate([...stages, { $out: 'order_summary' }]);
// Now recreate needed indexes:
db.order_summary.createIndex({ userId: 1 });
db.order_summary.createIndex({ createdAt: -1 });

// $merge preserves existing indexes automatically
// No index recreation needed after $merge

ETL Pipelines With $merge

$merge enables MongoDB-native ETL (Extract-Transform-Load) pipelines: extract data from a source collection, transform it through aggregation stages, and load the results into a destination collection. This avoids the need for an external ETL tool for common data movement tasks within the same MongoDB cluster.

// ETL: clean and transform raw events into a processed_events collection
db.raw_events.aggregate([
  // Extract: filter valid events
  { $match: { eventType: { $in: ['click', 'view', 'purchase'] }, userId: { $exists: true } } },
  // Transform: reshape and enrich
  { $addFields: {
    processedAt: '$$NOW',
    eventDate: { $dateToString: { format: '%Y-%m-%d', date: '$timestamp' } }
  }},
  { $project: { _id: 0, eventType: 1, userId: 1, eventDate: 1, processedAt: 1 } },
  // Load: upsert into destination
  { $merge: {
    into: 'processed_events',
    on: ['userId', 'eventDate', 'eventType'],
    whenMatched: 'keepExisting',  // don't reprocess
    whenNotMatched: 'insert'
  }}
]);

Quick Check

Test your understanding of $out and $merge pipeline stages.

Lesson Recap

In this lesson you learned: $out atomically replaces a target collection but drops secondary indexes, $merge upserts documents with configurable whenMatched and whenNotMatched behavior, and incremental materialised views with $merge allow efficient partial refreshes. This completes the Advanced Aggregation Stages course—next up we explore aggregation accumulators in depth.

Frequently asked questions

Is the “$out and $merge: Writing Pipeline Results” lesson free?

Yes — the full text of “$out and $merge: Writing Pipeline Results” is free to read here on the web, and the MongoDB Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the MongoDB Academy course, upgrade to CoddyKit PRO.

What will I learn in “$out and $merge: Writing Pipeline Results”?

Learners will direct aggregation output into a new or existing collection using $out and $merge for ETL and materialised views. You practise MongoDB Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start MongoDB Academy?

No prior experience is required. MongoDB Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “$out and $merge: Writing Pipeline Results” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this MongoDB Academy lesson?

Yes. Every MongoDB Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

$lookup: Joining Collections in the Pipeline
$unwind: Deconstructing Array Fields
$addFields, $replaceRoot, and $mergeObjects
$out and $merge: Writing Pipeline Results

← Back to MongoDB Academy