MongoDB aggregation pipeline examples, with the SQL each one replaces

#Eight pipelines, and the SQL each one replaces

The aggregation pipeline is a list of stages. A document enters the first stage, whatever comes out enters the second, and so on. That is the entire model — the difficulty is never the concept, it is remembering which stage does which job.

Every example below shows the SQL first, because the SQL is the part you already know.

#1. Filter

SELECT * FROM orders WHERE status = 'paid' AND total > 100

db.orders.aggregate([
  { $match: { status: "paid", total: { $gt: 100 } } }
])

Put $match first whenever you can. It is the one stage that can use an index, and only while nothing has reshaped the documents ahead of it.

#2. Pick columns

SELECT customerId, total FROM orders

db.orders.aggregate([
  { $project: { _id: 0, customerId: 1, total: 1 } }
])

_id comes back unless you ask it not to. That is the most common surprise in this list.

#3. Group and aggregate

SELECT customerId, SUM(total) AS revenue, COUNT(*) AS orders
FROM orders GROUP BY customerId

db.orders.aggregate([
  {
    $group: {
      _id: "$customerId",
      revenue: { $sum: "$total" },
      orders: { $sum: 1 }
    }
  }
])

The grouping key is always called _id. COUNT(*) is $sum: 1 — there is no count accumulator.

#4. Filter after grouping

SELECT customerId, SUM(total) AS revenue
FROM orders GROUP BY customerId HAVING SUM(total) > 1000

db.orders.aggregate([
  { $group: { _id: "$customerId", revenue: { $sum: "$total" } } },
  { $match: { revenue: { $gt: 1000 } } }
])

HAVING is not a stage. It is $match in the second position, which is also the clearest explanation of what HAVING ever was.

#5. Sort and page

SELECT * FROM orders ORDER BY total DESC LIMIT 10 OFFSET 20

db.orders.aggregate([
  { $sort: { total: -1 } },
  { $skip: 20 },
  { $limit: 10 }
])

$skip before $limit, and both after $sort — the order of the stages is the order of the operations, always.

#6. Join

SELECT o.total, c.name
FROM orders o JOIN customers c ON o.customerId = c._id

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      localField: "customerId",
      foreignField: "_id",
      as: "customer"
    }
  },
  { $unwind: "$customer" },
  { $project: { _id: 0, total: 1, name: "$customer.name" } }
])

$lookup produces an array, always. The plain $unwind that follows is what makes it an inner join; add preserveNullAndEmptyArrays: true and it becomes a left join.

#7. Distinct values

SELECT DISTINCT status FROM orders

db.orders.aggregate([
  { $group: { _id: "$status" } },
  { $project: { _id: 0, status: "$_id" } }
])

Grouping by a key and projecting nothing else is exactly what DISTINCT means.

#8. Count the matching rows

SELECT COUNT(*) AS n FROM orders WHERE status = 'paid'

db.orders.aggregate([
  { $match: { status: "paid" } },
  { $count: "n" }
])

$count produces no document at all when nothing matched, where SQL answers 0. If a dashboard needs a number, that difference is the one that will bite you.

#The stage order that matters

Two rules cover most performance questions:

1. $match as early as possible, so an index can serve it and later stages see fewer documents.
2. $project late rather than early, unless the documents are large — dropping fields before $sort or $group saves memory, but dropping a field a later stage needs is a longer debugging session than it saves.

#Read the pipeline your SQL produces

Every example here is a mapping you can look up once and then forget. Writing the SQL and reading the generated pipeline next to it is a faster way to learn the stages than memorising them.

Paste a query into the browser-based SQL to MongoDB converter — it runs in your browser. For the two stages that cause the most trouble, there are dedicated guides on GROUP BY and $lookup. SQL support lists which of these shapes the engine translates and which it refuses.