MongoDB aggregation pipeline basics: $match, $group, $sort, $project

Posted by Kyle Hankinson July 26, 2026

---

Sooner or later every MongoDB query outgrows find(). The moment you need a GROUP BY, a computed column, or a join, you are writing an aggregation pipeline, and the pipeline rewards one mental model above all others: it is a conveyor belt. Documents enter at the left, each stage transforms or filters what it receives and passes the result to the next stage, and order matters exactly as it does with Unix pipes. That is the whole trick. Most beginner pain comes not from the model but from two syntax rules of $group, and this article reproduces both mistakes on purpose so you recognize the symptoms.

Everything below ran on MongoDB 8.2.11 (official mongo:8 Docker image), with the failure cases re-confirmed on 7.0.37, against 1,000 generated order documents shaped like this:

db.orders.findOne()
// {
//   orderId: 1,
//   category: 'toys',
//   region: 'east',
//   quantity: 5,
//   price: 141.25,
//   placedAt: ISODate('2026-05-16T00:00:00.000Z')
// }

Suppose the question is: for electronics, what is the revenue per region, highest first? In SQL that is one statement; in MongoDB it is four stages, and the productive way to write it is one stage at a time, inspecting output after each addition.

Stage one, $match, is the WHERE. Run it alone first (a trailing $count is a cheap sanity check):

db.orders.aggregate([
  { $match: { category: "electronics" } },
  { $count: "n" }
])
// [ { n: 205 } ]

205 of 1,000 documents survive. Put $match as early as possible: at the beginning of a pipeline it can use an index, per the aggregation pipeline documentation, and every document it discards is work no later stage has to do.

Stage two, $group, is the GROUP BY. The grouping key goes in a field that must be called _id, and accumulators like $sum and $avg compute the aggregates:

db.orders.aggregate([
  { $match: { category: "electronics" } },
  { $group: { _id: "$region",
              revenue: { $sum: { $multiply: ["$price", "$quantity"] } },
              orders:  { $sum: 1 } } }
])
// { _id: 'north', revenue: 15388.880000000001, orders: 61 }
// { _id: 'south', revenue: 13978.19, orders: 48 }
// { _id: 'west',  revenue: 10811.94, orders: 48 }
// { _id: 'east',  revenue: 14458.869999999999, orders: 48 }

Note the $ prefixes: "$region" and "$price" mean "the value of that field in the incoming document." (The floating-point dust on north is ordinary double arithmetic, cleaned up below.) $sum: 1 adds one per document, which is how you spell COUNT(*). Grouping with _id: null collapses everything into one row; that variant returned the grand total, 1,000 orders and 295,749.87 in revenue.

Stage three, $sort, orders what $group emitted (-1 descending), and stage four, $project, shapes the final output: here it renames _id back to something readable and rounds the noise away.

db.orders.aggregate([
  { $match: { category: "electronics" } },
  { $group: { _id: "$region",
              revenue: { $sum: { $multiply: ["$price", "$quantity"] } },
              orders:  { $sum: 1 } } },
  { $sort: { revenue: -1 } },
  { $project: { _id: 0, region: "$_id", orders: 1, revenue: { $round: ["$revenue", 2] } } }
])
// { orders: 61, region: 'north', revenue: 15388.88 }
// { orders: 48, region: 'east',  revenue: 14458.87 }
// { orders: 48, region: 'south', revenue: 13978.19 }
// { orders: 48, region: 'west',  revenue: 10811.94 }

That is the whole pattern: filter, group, sort, shape. Since MongoDB 4.2, $set and $unset exist as friendlier aliases for the add-a-field and drop-a-field uses of $project.

The two ways your $group goes wrong

Both classic $group bugs share a symptom profile: no error, plausible-looking output, wrong numbers. Here is each one, run for real.

Mistake one: dropping the $ prefix on the grouping key. _id: "category" does not mean "group by category"; it means "group by the constant string category", so every document lands in a single bucket:

db.orders.aggregate([ { $group: { _id: "category", n: { $sum: 1 } } } ])
// { _id: 'category', n: 1000 }   <- one bucket, not five

Mistake two: dropping the $ inside an accumulator. $avg: "price" averages the constant string "price", which is not a number, so you get null; the same slip inside $sum quietly produces 0 because $sum ignores non-numeric values:

db.orders.aggregate([ { $group: { _id: "$category", avgPrice: { $avg: "price" } } } ])
// { _id: 'books', avgPrice: null } ...
db.orders.aggregate([ { $group: { _id: "$category", total: { $sum: "price" } } } ])
// { _id: 'books', total: 0 } ...

With the $ restored, avgPrice came back as 92.65 for books and 97.08 for electronics. A $sum column of zeros or an $avg of nulls is almost always this bug.

There is a third stumble worth naming: forgetting that the grouping key now lives in _id. Filtering on the original field name after a $group matches nothing at all:

db.orders.aggregate([
  { $group: { _id: "$category", n: { $sum: 1 } } },
  { $match: { category: "books" } }    // wrong: that field no longer exists
])
// []
db.orders.aggregate([
  { $group: { _id: "$category", n: { $sum: 1 } } },
  { $match: { _id: "books" } }         // right
])
// [ { _id: 'books', n: 208 } ]

That post-$group $match is also precisely how HAVING translates. Categories with more than 200 orders:

db.orders.aggregate([
  { $group: { _id: "$category", n: { $sum: 1 } } },
  { $match: { n: { $gt: 200 } } },
  { $sort: { n: -1 } }
])
// { _id: 'books', n: 208 }, { _id: 'electronics', n: 205 }, { _id: 'kitchen', n: 201 }

And COUNT DISTINCT is a two-stage idiom, group on the field then count the groups:

db.orders.aggregate([ { $group: { _id: "$region" } }, { $count: "distinctRegions" } ])
// [ { distinctRegions: 4 } ]

Memory limits, briefly

Each pipeline stage is capped at 100 MB of RAM. On MongoDB 6.0 and later the allowDiskUseByDefault parameter lets stages that exceed it spill to temporary disk files automatically, per the aggregation pipeline limits documentation, so the once-notorious "Exceeded memory limit for $group" error is mostly a pre-6.0 experience. On 4.x and 5.x you opt in per query with allowDiskUse: true. Either way, an early $match that shrinks the stream is the better fix than a bigger spill file. And if an older Stack Overflow answer suggests mapReduce for any of this, note it has been deprecated since MongoDB 5.0; the pipeline is the replacement.

The stage-at-a-time habit is also the debugging technique: when a pipeline misbehaves, delete stages from the end until the output looks right again, and the last stage you removed is your suspect. A client can shorten that loop considerably. The aggregation builder in SQLPro for MongoDB edits the pipeline as a stage list with insertable $match, $group, $sort, $project, and $lookup (and more) templates, and re-running after each edit puts the intermediate documents in a results grid.

From here, the natural next steps are joins with $lookup, covered in the SQL to MongoDB translation guide, and turning the pipeline loose on schema exploration, which is how you find out what fields a collection actually contains.

---