How do $out and $merge differ when writing aggregation results to a collection?

middleshould knowfreq 50%

answer

both are last-stage, server-side writes
one swaps a collection, one folds into it
matching needs a uniquely indexed key
two options decide match and no-match behaviour
incremental rollups need the folding one

basics

~20 s

$out replaces a target collection wholesale with the pipeline's results, keeping the collection's existing indexes. $merge writes incrementally into an existing collection, matching on the on field and applying whenMatched and whenNotMatched rules, so it can update in place rather than rebuilding.

solid answer

~50 s

Both are terminal stages — each must be the last stage of a pipeline — and both write server-side rather than shipping results to the client. The difference is replace versus upsert. $out takes the pipeline's entire output and makes it the target collection's contents: the collection is created if absent, its previous documents are gone, and the indexes that existed on it are preserved. If the pipeline errors before completing, the existing collection is left untouched. $merge (MongoDB 4.2+) matches each result document against the target on the on field or fields — _id by default, and whatever you name must be backed by a unique index — then applies whenMatched (merge, replace, keepExisting, fail, or a custom update pipeline) and whenNotMatched (insert, discard, fail). That makes $merge the stage for incrementally maintained summary collections: recompute one window, fold it into the rollup, leave the rest alone. Use $out for a full rebuild, $merge for incremental maintenance.

Both are terminal stages — each must be the last stage of a pipeline — and both write server-side rather than shipping results to the client. The difference is replace versus upsert. **`$out`** takes the pipeline's entire output and makes it the target collection's contents: the collection is created if absent, its previous documents are gone, and the indexes that existed on it are preserved. If the pipeline errors before completing, the existing collection is left untouched. **`$merge`** (MongoDB 4.2+) matches each result document against the target on the `on` field or fields — `_id` by default, and whatever you name must be backed by a unique index — then applies `whenMatched` (`merge`, `replace`, `keepExisting`, `fail`, or a custom update pipeline) and `whenNotMatched` (`insert`, `discard`, `fail`). That makes `$merge` the stage for incrementally maintained summary collections: recompute one window, fold it into the rollup, leave the rest alone. Use `$out` for a full rebuild, `$merge` for incremental maintenance.

code

javascript · 17 lines

javascript

// Full rebuild: whatever was in reportingSummary is replaced
db.orders.aggregate([
  { $match: { status: "paid" } },
  { $group: { _id: "$customerId", total: { $sum: "$amount" } } },
  { $out: "reportingSummary" }
])

// Incremental fold: only the recomputed window is rewritten
db.reportingSummary.createIndex({ customerId: 1, day: 1 }, { unique: true })
db.orders.aggregate([
  { $match: { createdAt: { $gte: start, $lt: end } } },
  { $group: { _id: { customerId: "$customerId", day: day },
              total: { $sum: "$amount" } } },
  { $project: { _id: 0, customerId: "$_id.customerId", day: "$_id.day", total: 1 } },
  { $merge: { into: "reportingSummary", on: ["customerId", "day"],
              whenMatched: "replace", whenNotMatched: "insert" } }
])

go deeper

for a junior

Know that a pipeline can write its results into a collection instead of returning them, and that one stage replaces the collection while the other updates documents in it.

for a middle

Explain the mechanics: last-stage only, $out replaces contents while preserving existing indexes, $merge matches on a uniquely indexed key and applies whenMatched and whenNotMatched rules, with merge and insert as the defaults.

for a senior

Choose deliberately for a real job: full rebuild versus incremental fold, idempotent re-runs of a window, what happens if the pipeline fails halfway, and who else writes to the target collection.

for a principal

Own the derived-data strategy: which computations get materialized at all, how the derived collection is keyed so re-runs stay idempotent, and how consumers are told what the data's freshness guarantee is.

Both are terminal, server-side writes

Neither stage returns documents to the client. The pipeline runs on the server and its results land in a collection, which is why they are the natural way to build derived datasets: no round trip, no client-side loop. Both must appear as the last stage of the pipeline — you cannot write and then continue processing.

$out: replace the whole thing

$out names a target collection (and, since MongoDB 4.2, optionally a database):

javascript

{ $out: { db: "reports", coll: "daily_totals" } }
// or the short form
{ $out: "daily_totals" }

Semantics:

If the target does not exist, it is created.
If it does exist, its contents are replaced by the pipeline's output. Anything previously in it is gone.
Indexes that existed on the target collection are not changed by the operation, so a rebuilt reporting collection keeps the indexes you created for it.
If the aggregation fails partway, the pre-existing collection is left as it was rather than half-overwritten.

That makes $out a good fit for a full rebuild: a nightly denormalized snapshot, an export table, a derived collection cheap enough to recompute in its entirety.

$merge: fold results into what is already there

$merge was introduced in MongoDB 4.2 and is the more expressive stage:

javascript

{
  $merge: {
    into: "daily_user_hits",
    on: ["userId", "day"],
    whenMatched: "merge",
    whenNotMatched: "insert"
  }
}

into names the target collection (optionally with a database).
on is the field or fields used to decide whether a result document corresponds to an existing one. It defaults to _id; anything else must be backed by a unique index on the target, because the match has to identify at most one document.
whenMatched decides what happens when a target document matches: merge (the default — field-by-field merge of the new document into the old), replace, keepExisting, fail, or a custom aggregation pipeline for a computed update.
whenNotMatched decides what happens otherwise: insert (the default), discard, or fail.

Because $merge touches only the documents it matched, everything else in the target survives. That is what makes incremental maintenance possible: compute the last hour, merge it into a rollup keyed by hour, and the previous eleven months are untouched. It is also the documented way to write into the same collection the pipeline is reading from, and the stage that supports a sharded output collection.

Choosing between them

Ask what the recompute costs and what the target is.

Cheap to recompute everything, target owned entirely by this job, no concurrent writers: $out.
Expensive history that changes only at the edges, or a target that other processes also write to, or a sharded target: $merge.
Need custom conflict handling — increment rather than overwrite, keep the first value seen, fail loudly on collision: $merge with the appropriate whenMatched.

Gotchas worth naming

They are last-stage only. A pipeline cannot $merge and then $project.
$merge's on needs a unique index. Forgetting it is the most common first failure, and it is not an arbitrary restriction: without uniqueness the stage could not decide which document to update.
$out is destructive by design. Pointing it at a collection someone else writes to will silently erase their data on the next run.
Defaults surprise people. whenMatched: "merge" combines fields rather than replacing the document, so stale fields from a previous run can survive. If you want the new document to win outright, say replace.
Re-running a window must be idempotent. Key the on fields to the window (for example the grouping key plus the day) so that re-running the same window overwrites its own output instead of appending a duplicate.

In an interview

The two-sentence answer is "replace versus upsert": $out swaps a whole collection, $merge folds documents into an existing one under rules you choose. The follow-through that shows experience is naming on plus its unique-index requirement, the whenMatched/whenNotMatched defaults, and the incremental-rollup pattern that $merge exists to serve.

## Both are terminal, server-side writes Neither stage returns documents to the client. The pipeline runs on the server and its results land in a collection, which is why they are the natural way to build derived datasets: no round trip, no client-side loop. Both must appear as the **last** stage of the pipeline — you cannot write and then continue processing. ## $out: replace the whole thing `$out` names a target collection (and, since MongoDB 4.2, optionally a database): ```javascript { $out: { db: "reports", coll: "daily_totals" } } // or the short form { $out: "daily_totals" } ``` Semantics: - If the target does not exist, it is created. - If it does exist, its contents are replaced by the pipeline's output. Anything previously in it is gone. - Indexes that existed on the target collection are not changed by the operation, so a rebuilt reporting collection keeps the indexes you created for it. - If the aggregation fails partway, the pre-existing collection is left as it was rather than half-overwritten. That makes `$out` a good fit for a full rebuild: a nightly denormalized snapshot, an export table, a derived collection cheap enough to recompute in its entirety. ## $merge: fold results into what is already there `$merge` was introduced in MongoDB 4.2 and is the more expressive stage: ```javascript { $merge: { into: "daily_user_hits", on: ["userId", "day"], whenMatched: "merge", whenNotMatched: "insert" } } ``` - **`into`** names the target collection (optionally with a database). - **`on`** is the field or fields used to decide whether a result document corresponds to an existing one. It defaults to `_id`; anything else must be backed by a unique index on the target, because the match has to identify at most one document. - **`whenMatched`** decides what happens when a target document matches: `merge` (the default — field-by-field merge of the new document into the old), `replace`, `keepExisting`, `fail`, or a custom aggregation pipeline for a computed update. - **`whenNotMatched`** decides what happens otherwise: `insert` (the default), `discard`, or `fail`. Because `$merge` touches only the documents it matched, everything else in the target survives. That is what makes incremental maintenance possible: compute the last hour, merge it into a rollup keyed by hour, and the previous eleven months are untouched. It is also the documented way to write into the same collection the pipeline is reading from, and the stage that supports a sharded output collection. ## Choosing between them Ask what the recompute costs and what the target is. - Cheap to recompute everything, target owned entirely by this job, no concurrent writers: `$out`. - Expensive history that changes only at the edges, or a target that other processes also write to, or a sharded target: `$merge`. - Need custom conflict handling — increment rather than overwrite, keep the first value seen, fail loudly on collision: `$merge` with the appropriate `whenMatched`. ## Gotchas worth naming - **They are last-stage only.** A pipeline cannot `$merge` and then `$project`. - **`$merge`'s `on` needs a unique index.** Forgetting it is the most common first failure, and it is not an arbitrary restriction: without uniqueness the stage could not decide which document to update. - **`$out` is destructive by design.** Pointing it at a collection someone else writes to will silently erase their data on the next run. - **Defaults surprise people.** `whenMatched: "merge"` combines fields rather than replacing the document, so stale fields from a previous run can survive. If you want the new document to win outright, say `replace`. - **Re-running a window must be idempotent.** Key the `on` fields to the window (for example the grouping key plus the day) so that re-running the same window overwrites its own output instead of appending a duplicate. ## In an interview The two-sentence answer is "replace versus upsert": `$out` swaps a whole collection, `$merge` folds documents into an existing one under rules you choose. The follow-through that shows experience is naming `on` plus its unique-index requirement, the `whenMatched`/`whenNotMatched` defaults, and the incremental-rollup pattern that `$merge` exists to serve.

Why does $merge require a unique index on the fields named in on?
Because the stage must resolve each result document to at most one target document before deciding whether to update or insert. Without uniqueness the match could hit several documents and the outcome would be ambiguous. `_id` is the default precisely because it is already unique; any other key you name has to be backed by a unique index on the target collection.
What does the default whenMatched behaviour do, and when does it bite?
The default is `merge`, which combines the new document's fields into the existing one, leaving unmentioned fields in place. That bites when a recomputation should have removed a field — a category that no longer applies, a flag that cleared — because the stale value survives. Use `replace` when the newly computed document should be the whole truth for that key.
You need a nightly rebuild of a reporting collection with its own indexes. Which stage, and why?
`$out` fits: it replaces the contents wholesale and leaves the indexes that already exist on the target in place, so the reporting indexes survive the rebuild. It also leaves the previous contents intact if the pipeline fails, so a failed run does not leave a half-built report. Reach for `$merge` instead once the full recompute becomes too expensive to run nightly.

$out reprints the whole ledger from scratch; $merge writes today's entries into the ledger already on the shelf.

saying these in an interview costs you the question

Thinks $out updates matching documents rather than replacing the collection
Names arbitrary on fields for $merge without a unique index
Assumes whenMatched replaces the document by default
Puts $out or $merge somewhere other than the last stage
Points $out at a collection other processes also write to

How do $out and $merge differ when writing aggregation results to a collection?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Both are terminal, server-side writes

$out: replace the whole thing

$merge: fold results into what is already there

Choosing between them

Gotchas worth naming

In an interview

▾common follow-ups3 questions

▾analogy~10 s

▾red flags5 flags

§ context

▾related questions5 questions

▾related topics4 topics

Both are terminal, server-side writes

$out: replace the whole thing

$merge: fold results into what is already there

Choosing between them

Gotchas worth naming

In an interview

answer

go deeper

context