More Aggregation Stages

Go further with aggregation: flatten arrays with $unwind, count values with $sortByCount, group numbers into ranges with $bucket, run several analyses at once with $facet, use conditional and date expressions, and save results with $out and $merge.

Lesson 13 of 43Intermediate19 min read

The aggregation basics lesson covered the core stages: $match, $group, $sort, $limit and $project. Real reports need a few more tools. Orders contain arrays of items, and to total sales per product you first need one document per item. Price filters on a shop page need products counted in ranges. A search results page needs the results and the counts for each filter in a single round trip. And expensive reports should be saved so dashboards load instantly.

MongoDB has stages for each of these. $unwind turns each element of an array into its own document. $sortByCount counts how often each value appears. $bucket groups numbers into ranges, like a histogram. $facet runs several sub-pipelines over the same input at once. $out and $merge write the results into a collection.

Inside stages you can also use rich expressions: $cond and $switch for conditions, $dateToString for grouping by day or month, and $filter, $map and $size for working with arrays without unwinding them. The examples use this orders collection:

MongoDB Shell
db.orders.insertMany([
  { _id: 1, customer: 'ada', at: ISODate('2026-03-01T10:00:00Z'), items: [{ sku: 'MUG', qty: 2, price: 8.5 }, { sku: 'PEN', qty: 5, price: 1.2 }] },
  { _id: 2, customer: 'ben', at: ISODate('2026-03-01T15:30:00Z'), items: [{ sku: 'LAMP', qty: 1, price: 24 }] },
  { _id: 3, customer: 'ada', at: ISODate('2026-03-02T09:15:00Z'), items: [{ sku: 'MUG', qty: 1, price: 8.5 }, { sku: 'RUG', qty: 1, price: 79 }] },
  { _id: 4, customer: 'cy', at: ISODate('2026-03-03T18:45:00Z'), items: [] }
])

$unwind and counting

MongoDB Shell
db.orders.aggregate([
  { $unwind: '$items' },
  { $group: { _id: '$items.sku', units: { $sum: '$items.qty' } } }
])
  • $unwind outputs one document per array element, with the array field replaced by that element.
  • Documents with an empty or missing array are dropped, unless you use { $unwind: { path: '$items', preserveNullAndEmptyArrays: true } }.
  • $sortByCount: '$field' groups by a value, counts each group and sorts by count, a shortcut for $group plus $sort.
  • $count: 'name' outputs a single document with the number of documents that reached it.

$bucket, $facet and saving results

  • $bucket puts documents into ranges you choose: { groupBy: '$price', boundaries: [0, 10, 50, 100], default: 'Other', output: { count: { $sum: 1 } } }. $bucketAuto picks evenly sized ranges for you.
  • $facet runs several pipelines on the same input and returns all their results in one document, perfect for search pages that need results plus filter counts.
  • $out: 'collection' replaces a collection with the pipeline's results; $merge inserts or updates into an existing collection, which suits incremental summary tables.

Useful expressions

ExpressionPurpose
{ $cond: [cond, a, b] }If-then-else
{ $switch: { branches: [...], default } }Several conditions
{ $ifNull: ['$field', default] }Default for missing or null values
{ $dateToString: { format: '%Y-%m-%d', date: '$at' } }Group by day
{ $size: '$items' }Array length
{ $filter: { input: '$items', cond: ... } }Keep some array elements
{ $map: { input: '$items', in: ... } }Transform each element
{ $sum: '$items.qty' } in $projectTotal an array's values without unwinding

Reshaping with $replaceWith and $unwind options

Sometimes the document you want to work with is inside another one. $replaceWith (also written $replaceRoot: { newRoot: ... }) makes any embedded document or expression the new top level:

MongoDB Shell
db.orders.aggregate([{ $replaceWith: '$customer' }])
// { name: 'Ada', city: 'Leeds' }: the customer, without the order around it

It's especially handy after $unwind, when each document holds one array element and you'd like that element to be the document. $mergeObjects combines it with fields from the parent, so you don't lose track of where it came from:

MongoDB Shell
db.orders.aggregate([
  { $unwind: '$items' },
  { $replaceWith: { $mergeObjects: [{ orderId: '$_id' }, '$items'] } }
])
// { orderId: 1, sku: 'A', qty: 1 }, { orderId: 1, sku: 'B', qty: 2 }

The result is a flat list of order lines, ready for a CSV export or a $group by SKU.

$unwind has one more option worth knowing. includeArrayIndex: 'line' adds a field holding each element's position in the original array, starting at 0 and stored as a 64-bit integer. Use it when position matters, such as "the first item in each basket", or when you plan to rebuild the array in its original order after processing each element.

MongoDB Aggregation Syntax

Stage and expression cheat sheet

{ $unwind: { path: '$items', preserveNullAndEmptyArrays: true } }
{ $sortByCount: '$category' }
{ $bucket: { groupBy: '$price', boundaries: [0, 20, 50], default: '50+' } }
{ $facet: { results: [{ $limit: 10 }], byCategory: [{ $sortByCount: '$category' }] } }
{ $set: { total: { $sum: { $map: { input: '$items', in: { $multiply: ['$$this.qty', '$$this.price'] } } } } } }
{ $set: { size: { $cond: [{ $gte: ['$total', 50] }, 'large', 'small'] } } }
{ $group: { _id: { $dateToString: { format: '%Y-%m-%d', date: '$at' } }, n: { $sum: 1 } } }
{ $merge: { into: 'daily_sales', on: '_id', whenMatched: 'replace' } }

MongoDB Aggregation Summary

  • $unwind turns each array element into a document; preserveNullAndEmptyArrays keeps empty ones.
  • $sortByCount counts and sorts values; $count counts documents.
  • $bucket and $bucketAuto group numbers into ranges; $facet runs several pipelines at once.
  • $cond, $switch, $ifNull, date and array expressions compute values inside stages.
  • $out and $merge save pipeline results into a collection.

MongoDB Aggregation Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Units sold per product with $unwind

MongoDB Shell
db.orders.aggregate([
  { $unwind: '$items' },
  { $group: {
      _id: '$items.sku',
      units: { $sum: '$items.qty' },
      revenue: { $sum: { $multiply: ['$items.qty', '$items.price'] } }
  } },
  { $sort: { revenue: -1 } }
])
db.orders.aggregate([{ $unwind: '$items' }, { $count: 'lines' }])
db.orders.aggregate([{ $unwind: { path: '$items', preserveNullAndEmptyArrays: true } }, { $count: 'lines' }])
Output
[
  { _id: 'RUG', units: 1, revenue: 79 },
  { _id: 'MUG', units: 3, revenue: 25.5 },
  { _id: 'LAMP', units: 1, revenue: 24 },
  { _id: 'PEN', units: 5, revenue: 6 }
]
[ { lines: 5 } ]
[ { lines: 6 } ]
Line-by-line explanation
  1. Line 2Each order becomes one document per item, so totals can be grouped across orders.
  2. Lines 4 to 6Item fields are reached with dot notation after unwinding.
  3. Line 10Cy's order has no items, so $unwind drops it: five lines.
  4. Line 11preserveNullAndEmptyArrays keeps the empty order, giving six.
Example 2

Order totals, sizes and daily sales without unwinding

MongoDB Shell
db.orders.aggregate([
  { $set: {
      total: { $sum: { $map: { input: '$items', in: { $multiply: ['$$this.qty', '$$this.price'] } } } },
      lines: { $size: '$items' }
  } },
  { $set: { size: { $switch: { branches: [
      { case: { $eq: ['$lines', 0] }, then: 'empty' },
      { case: { $gte: ['$total', 50] }, then: 'large' }
    ], default: 'small' } } } },
  { $group: { _id: { $dateToString: { format: '%Y-%m-%d', date: '$at' } }, orders: { $sum: 1 }, revenue: { $sum: '$total' }, sizes: { $push: '$size' } } },
  { $sort: { _id: 1 } }
])
Output
[
  {
    _id: '2026-03-01',
    orders: 2,
    revenue: 47,
    sizes: [ 'small', 'small' ]
  },
  { _id: '2026-03-02', orders: 1, revenue: 87.5, sizes: [ 'large' ] },
  { _id: '2026-03-03', orders: 1, revenue: 0, sizes: [ 'empty' ] }
]
Line-by-line explanation
  1. Line 3$map works out each item's line total, and $sum adds them up, all inside one document.
  2. Line 4$size counts the items.
  3. Lines 6 to 9$switch picks a label from the first matching condition, or the default.
  4. Line 10$dateToString turns each timestamp into a day, so orders group by date.
Example 3

Price ranges and a search page with $facet

MongoDB Shell
db.orders.aggregate([
  { $unwind: '$items' },
  { $facet: {
      priceBands: [
        { $bucket: { groupBy: '$items.price', boundaries: [0, 10, 50], default: '50+', output: { lines: { $sum: 1 } } } }
      ],
      topCustomers: [
        { $sortByCount: '$customer' }
      ],
      totalLines: [{ $count: 'n' }]
  } }
])
Output
[
  {
    priceBands: [
      { _id: 0, lines: 3 },
      { _id: 10, lines: 1 },
      { _id: '50+', lines: 1 }
    ],
    topCustomers: [ { _id: 'ada', count: 4 }, { _id: 'ben', count: 1 } ],
    totalLines: [ { n: 5 } ]
  }
]
Line-by-line explanation
  1. Line 3$facet runs three separate pipelines over the same unwound items.
  2. Line 5$bucket counts lines in price ranges 0-10 and 10-50; anything else goes into the default bucket.
  3. Line 8$sortByCount counts items per customer, highest first.
  4. Line 10Several results come back in one round trip, ideal for search pages with filter counts.

Common MongoDB Aggregation Mistakes

$unwind dropping documents unexpectedly

Don't
[{ $unwind: '$items' }, { $group: { _id: '$customer', orders: { $sum: 1 } } }]   // customers with empty orders vanish
Do
[{ $unwind: { path: '$items', preserveNullAndEmptyArrays: true } }, { $group: { _id: '$customer', lines: { $sum: 1 } } }]

Unwinding just to compute a per-document total

Don't
[{ $unwind: '$items' }, { $group: { _id: '$_id', total: { $sum: '$items.price' } } }]   // many extra documents
Do
[{ $set: { total: { $sum: '$items.price' } } }]

Running a heavy report on every page view

Don't
// the dashboard re-aggregates two years of orders each time someone opens it
Do
db.orders.aggregate([ /* daily totals */ { $merge: { into: 'daily_sales', on: '_id', whenMatched: 'replace' } }])
// a scheduled job refreshes daily_sales; the dashboard reads the small summary collection

MongoDB Aggregation in Real Projects

Advanced aggregation in practice

E-commerce search pages use $facet to return products plus counts for each brand, price band and rating in one query. Sales reports unwind order items to rank products, and finance dashboards group by $dateTrunc or $dateToString for daily and monthly revenue. Nightly jobs use $merge to maintain summary collections, so heavy calculations run once instead of on every visit.

MongoDB Aggregation FAQ

What does $unwind do in MongoDB?

$unwind takes a document with an array and outputs one copy of the document for each element, with the array field replaced by that single element. An order with three items becomes three documents. It's the usual first step before grouping or counting values that live inside arrays across many documents.

What is the difference between $out and $merge?

$out replaces the whole target collection with the pipeline's results each time it runs. $merge writes into an existing collection and lets you choose what happens per document: replace, merge fields, keep the existing one or insert only. Use $merge for summary tables you update incrementally, and $out when you want a fresh snapshot.

How do I get search results and filter counts in one MongoDB query?

Use $facet, which runs several sub-pipelines over the same input and returns each one's output as an array in a single document. One facet can $sort, $skip and $limit the results while others use $sortByCount on category and $bucket on price for the filter sidebar. Put a $match before the $facet so every branch works on the filtered set.

How do I write an if-else inside a MongoDB aggregation?

Use $cond: { $cond: [{ $gte: ['$total', 100] }, 'large', 'small'] } returns 'large' when the condition is true and 'small' otherwise. For more than two outcomes, $switch takes a list of branches, each with a case and a then, plus a default. Both can be used in $project, $set and $group.

How do I add up values inside an array without $unwind?

Use an array expression in $project or $set: { $set: { units: { $sum: '$items.qty' } } } totals the qty of every item in each order. For calculations per element, such as price times quantity, combine $sum with $map. This keeps one document per order and avoids the extra work of unwinding and regrouping.

MongoDB Aggregation Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the stage that turns each element of items into its own document.

db.orders.aggregate([{ : '$items' }])
Multiple choiceeasy

What does $sortByCount do?

Predict the outputmedium

An order with an empty items array goes through { $unwind: '$items' }. How many documents come out for it?

Multiple choicemedium

What is $facet useful for?

Multiple choicehard

What's the difference between $out and $merge?

MongoDB Aggregation Quiz

5 questions to check what you learned.

  1. Which stage groups numbers into ranges you choose?

    • $group
    • $range
    • $bucket
    • $split

  2. What does $$this mean inside $map?

    • The whole document
    • The current array element
    • The previous stage
    • The collection

  3. Which expression gives a default for a missing field?

    • $ifNull
    • $default
    • $exists
    • $coalesce

  4. How do you group orders by day?

    • Group by '$at'
    • Group by $day
    • Group by { $dateToString: { format: '%Y-%m-%d', date: '$at' } }
    • Sort by date

  5. When should you avoid $unwind?

    • Always
    • When arrays contain numbers
    • When using $group
    • When you only need a value from each document's own array, which array expressions can compute directly

Related MongoDB topics

12Aggregation BasicsAnalyse data inside MongoDB with the aggregation pipeline: filter with $match, group and total with $group and accumulators like $sum and $avg, sort and limit results, and reshape documents with $project.14$lookup JoinsCombine data from several collections with $lookup: match local and foreign fields, flatten single matches with $unwind or $first, filter and shape joined documents with pipeline lookups, join arrays of ids, and keep joins fast with indexes.09ArraysStore and change lists inside documents: add items with $push and $addToSet, remove them with $pull and $pop, keep arrays sorted and capped with $each, $sort and $slice, and update matching elements with the positional $ operator and arrayFilters.
Esc
↑↓ to navigateEnter to open