$facet, $bucket, window functions, $merge and $out

conceptThe concept

• $facet: run several sub-pipelines on the same input and return one document with each result. Typical for search pages (results + counts per category + price ranges).
• $bucket (explicit boundaries) and $bucketAuto (N evenly filled buckets): histograms.
• $setWindowFields (5.0+): window functions over partitions sorted by a field: $sum with window: { documents: ["unbounded", "current"] } for running totals, $avg over a range for moving averages, $rank, $denseRank, $shift.
• $merge: write results into a collection, inserting, replacing or merging per document; can target the same or another database and run incrementally.
• $out: replace a collection atomically with the results.

whyWhy it matters

These stages replace application-side loops and separate batch jobs. $merge in particular lets you maintain pre-aggregated summary collections (an on-demand materialized view) so dashboards read 30 documents instead of aggregating millions on every page load.

Use caseReal-world scenario

A nightly job computes yesterday's revenue per product and $merges it into daily_product_sales keyed by { day, productId }. The dashboard reads that small collection. Re-running the job for the same day replaces the same keys, so it is idempotent.

Try itExamples

Run and edit these in the interactive lesson.

db.products.aggregate([
  { $match: { rating: { $gte: 4 } } },
  { $facet: {
      results: [{ $sort: { rating: -1 } }, { $limit: 3 }, { $project: { name: 1, price: 1, rating: 1 } }],
      byCategory: [{ $sortByCount: "$category" }],
      priceStats: [{ $group: { _id: null, min: { $min: "$price" }, max: { $max: "$price" }, avg: { $avg: "$price" } } }]
  } }
])

db.orders.aggregate([
  { $bucket: { groupBy: "$total", boundaries: [0, 100, 250, 500, 1000, 2500], default: "2500+", output: { orders: { $sum: 1 } } } }
])

db.orders.aggregate([
  { $match: { createdAt: { $gte: ISODate("2026-09-01") }, status: { $ne: "cancelled" } } },
  { $group: { _id: { $dateTrunc: { date: "$createdAt", unit: "day" } }, revenue: { $sum: "$total" } } },
  { $setWindowFields: { sortBy: { _id: 1 }, output: {
      runningTotal: { $sum: "$revenue", window: { documents: ["unbounded", "current"] } },
      movingAvg3d: { $avg: "$revenue", window: { documents: [-2, 0] } },
      rank: { $rank: {} } } } },
  { $limit: 7 }
])

db.orders.aggregate([
  { $match: { status: "delivered" } },
  { $group: { _id: "$customerId", orders: { $sum: 1 }, spend: { $sum: "$total" }, lastOrder: { $max: "$createdAt" } } },
  { $merge: { into: "customer_stats", on: "_id", whenMatched: "replace", whenNotMatched: "insert" } }
])
db.customer_stats.find().sort({ spend: -1 }).limit(3)

internalsUnder the hood

$facet sub-pipelines cannot use indexes (only the stages before $facet can), and the single output document must fit in 16 MB. $setWindowFields requires sorting each partition; with a large unbounded window it may spill to disk. $out writes to a temporary collection and renames it at the end, so readers see either the old or the new data. $merge needs a unique index on its on fields.

Avoid / preferDo this, not that

db.orders.aggregate([ /* today's totals */ ..., { $out: "daily_sales" }])

db.orders.aggregate([ /* today's totals */ ...,
  { $merge: { into: "daily_sales", on: "_id", whenMatched: "replace", whenNotMatched: "insert" } }])

db.orders.aggregate([{ $facet: {
  byStatus: [{ $sortByCount: "$status" }],
  recent: [{ $sort: { createdAt: -1 } }]
} }])

db.orders.aggregate([
  { $match: { createdAt: { $gte: ISODate("2026-09-01") } } },
  { $facet: {
    byStatus: [{ $sortByCount: "$status" }],
    recent: [{ $sort: { createdAt: -1 } }, { $limit: 10 }]
  } }
])

PitfallsCommon mistakes

• Using $facet as the first stage on a big collection, expecting each facet to use an index.
• Re-aggregating raw data on every dashboard request instead of maintaining a summary with $merge.
• Using $out when you meant to update incrementally; $out replaces the whole target collection.

LimitsWhen not to use it

$facet output is a single document and cannot exceed 16 MB. $out cannot write to a sharded collection; use $merge for that. For dashboards that refresh constantly, maintain a summary collection with $merge instead of re-aggregating raw data on every page load.

SecuritySecurity notes

$out and $merge need write privileges on the target, possibly in another database. A read-only analyst role should not have them, or a report query can overwrite production data.

ChallengeTry it yourself

db.products.aggregate([
  { $bucket: {
    groupBy: "$price",
    boundaries: [0, 50, 200, 500],
    default: "500+",
    output: { products: { $sum: 1 }, avgRating: { $avg: "$rating" } }
  } },
  { $set: { avgRating: { $round: ["$avgRating", 2] } } }
])

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.