Multi-Faceted Aggregation: $facet, $bucket, $out and $merge

A search results page needs the matching products and the counts per brand and the price histogram, ideally from one query. A reporting job needs to compute a summary once and store it for cheap reads. This lesson covers the stages built for those jobs. After it you will be able to run several sub-pipelines in one pass with $facet, group values into ranges with $bucket and $bucketAuto, and write pipeline output to a collection with $out or $merge.

$facet: Several Pipelines, One Pass

$facet takes the documents that reach it and feeds a copy to each named sub-pipeline. The output is a single document with one array per facet:

javascript

db.products.aggregate([
  { $match: { category: "office", price: { $lte: 5000 } } },   // uses indexes
  { $facet: {
      results: [ { $sort: { rating: -1 } }, { $skip: 0 }, { $limit: 20 },
                 { $project: { name: 1, price: 1, brand: 1 } } ],
      byBrand: [ { $sortByCount: "$brand" } ],
      total:   [ { $count: "n" } ]
  } }
])
// { results: [ ... 20 docs ... ], byBrand: [ { _id: "Acme", count: 41 }, ... ], total: [ { n: 132 } ] }

$sortByCount is shorthand for $group by a value plus $sort by count descending. Rules to remember:

Put the shared $match before $facet; stages inside facets cannot use indexes.
The entire result is one document, so it must stay under 16 MB. Always $limit the facet that returns documents.
Sub-pipelines cannot contain $facet, $out, $merge or $search.

$bucket: Fixed Ranges

$bucket groups documents by which range of boundaries their groupBy value falls into. The lower bound is inclusive, the upper exclusive, and values outside every range go to default:

javascript

db.products.aggregate([
  { $bucket: {
      groupBy: "$price",
      boundaries: [0, 50, 100, 500],
      default: "500+",
      output: { count: { $sum: 1 }, avgRating: { $avg: "$rating" } }
  } }
])
// { _id: 0, count: 12, ... }  { _id: 50, count: 30, ... }  { _id: "500+", count: 4, ... }

Boundaries must be ascending and of the same type; _id is the lower bound of each bucket.

$bucketAuto: Even Distribution

When you do not know the data range, $bucketAuto splits documents into a requested number of buckets with roughly equal counts:

javascript

{ $bucketAuto: { groupBy: "$price", buckets: 4, output: { count: { $sum: 1 } } } }
// { _id: { min: 4.99, max: 39.5 }, count: 25 }  { _id: { min: 39.5, max: 120 }, count: 25 } ...

The optional granularity (for example "R5", "E12" or "POWERSOF2") snaps boundaries to a preferred-number series, which produces human-friendly ranges for charts.

$out: Replace a Collection

$out writes the pipeline output to a collection, replacing it entirely. It must be the last stage:

javascript

db.orders.aggregate([
  { $group: { _id: "$customerId", lifetimeValue: { $sum: "$total" } } },
  { $out: "customerValue" }                            // or { db: "reports", coll: "customerValue" }
])

The replacement is atomic: MongoDB builds a temporary collection, copies the indexes of the existing target onto it, then renames it into place. $out cannot target a sharded collection.

$merge: Incremental Materialized Views

$merge upserts pipeline output into an existing collection and lets you decide what happens on a match:

javascript

{ $merge: {
    into: "dailyRevenue",
    on: "_id",                        // must be _id or a field with a unique index
    whenMatched: "replace",           // or "keepExisting", "merge" (default), "fail", or a pipeline
    whenNotMatched: "insert"          // or "discard", "fail"
} }

Because it only touches the documents it produces, $merge is the tool for a scheduled job that keeps a summary collection current: run the pipeline over yesterday's orders, merge the results, and the dashboard reads a small pre-computed collection. A whenMatched pipeline can even combine the old and new values, referencing the incoming document as $$new.

| | $out | $merge | |---|---|---| | Existing target | replaced entirely | updated document by document | | Sharded target | not allowed | allowed | | Same collection as source | not allowed | allowed with care | | Typical use | full rebuild | incremental refresh |

Common Mistakes

Placing filters inside facets instead of before $facet, losing index usage.
Omitting default in $bucket when some values fall outside the boundaries; the stage errors.
Using $out for incremental work, rebuilding an entire collection to update a few rows.
Merging on a field without a unique index. $merge requires it for every on field other than _id.

Key Takeaways

$facet runs several sub-pipelines over the same input in one pass and returns one document; filter before it and limit inside it.
$bucket groups by fixed, ascending boundaries with a default catch-all; $bucketAuto picks boundaries for evenly sized buckets.
$sortByCount and $count are convenient shorthands for common summaries.
$out atomically replaces a collection with the pipeline result and must be the last stage.
$merge upserts results into an existing collection, which makes scheduled materialized views cheap to maintain.

Next lesson: Schema Design and Data Modeling — model documents around how your application reads and writes data.