Problem Statement
Explain the difference between $out and $merge stages in aggregation pipelines.
Explanation
Both dollar out and dollar merge are output stages that write aggregation results to a collection, but they behave differently. Dollar out must be the last stage in the pipeline and completely replaces the target collection. If the collection exists, dollar out drops it and creates a new one with the pipeline results. This is destructive and cannot be undone.
Dollar merge is more flexible and can be placed as the last stage. It merges the pipeline results into an existing collection without dropping it. You can specify how to handle existing documents using the on field for matching and whenMatched options like replace, merge, keepExisting, or fail.
Use dollar out when you want to create a new collection or completely replace an existing one with fresh results. This is suitable for full refresh scenarios like nightly reports. Use dollar merge when you want to update or upsert documents in an existing collection, preserving documents that are not in the pipeline results.
Dollar merge also supports writing to collections in different databases and offers more control over the merge behavior. It is the preferred choice for incremental updates and more complex output scenarios.
Code Solution
SolutionRead Only
// $out - replaces entire collection
db.orders.aggregate([
{ $match: { status: "completed" } },
{ $group: { _id: "$customerId", total: { $sum: "$amount" } } },
{ $out: "customer_totals" } // Replaces customer_totals collection
])
// $merge - merges into existing collection
db.orders.aggregate([
{ $match: { status: "completed" } },
{ $group: { _id: "$customerId", total: { $sum: "$amount" } } },
{
$merge: {
into: "customer_totals",
on: "_id",
whenMatched: "replace",
whenNotMatched: "insert"
}
}
])