MongoDB Aggregation for SQL Developers: GROUP BY, JOIN and More

If you know SQL, you already understand most of MongoDB’s aggregation pipeline. The ideas are the same; the shape is different. SQL describes the result in one statement. MongoDB describes the steps: documents flow through a list of stages, and each stage changes them before passing them on.

Want to see your own query converted? Paste it into the SQL to MongoDB converter.

SQL clauses and their pipeline stages

SQL	MongoDB stage
WHERE	$match
GROUP BY with COUNT, SUM, AVG	$group with $sum, $avg, $min, $max
HAVING	$match placed after $group
SELECT col AS name	$project or $addFields
ORDER BY	$sort
LIMIT / OFFSET	$limit / $skip
LEFT JOIN	$lookup (plus $unwind for one-to-one)
COUNT(*) of the result	$count

Example 1: Revenue per city (GROUP BY + HAVING + ORDER BY)

SELECT city, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY city
HAVING revenue > 100000
ORDER BY revenue DESC
LIMIT 5;

db.orders.aggregate([
  { $match: { status: "paid" } },
  { $group: { _id: "$city", orders: { $sum: 1 }, revenue: { $sum: "$total" } } },
  { $match: { revenue: { $gt: 100000 } } },
  { $sort: { revenue: -1 } },
  { $limit: 5 },
  { $project: { _id: 0, city: "$_id", orders: 1, revenue: 1 } }
])

Two things to notice. In $group, the grouping key always goes in _id, and field names in values start with $ to mean “the value of this field”. The final $project renames _id back to city so the output reads like your SQL result.

Example 2: Monthly sales

db.orders.aggregate([
  { $match: { status: "paid", createdAt: { $gte: ISODate("2026-01-01") } } },
  { $group: {
      _id: { $dateToString: { format: "%Y-%m", date: "$createdAt", timezone: "Asia/Kolkata" } },
      revenue: { $sum: "$total" }
  } },
  { $sort: { _id: 1 } }
])

The timezone option matters: without it, months are cut at midnight UTC, so late-evening orders in India land in the wrong day or month.

Example 3: Orders with customer names (LEFT JOIN)

SELECT o._id, o.total, u.name
FROM orders o
LEFT JOIN users u ON o.userId = u._id;

db.orders.aggregate([
  { $lookup: { from: "users", localField: "userId", foreignField: "_id", as: "user" } },
  { $unwind: { path: "$user", preserveNullAndEmptyArrays: true } },
  { $project: { total: 1, customer: "$user.name" } }
])

$lookup always returns an array. $unwind turns it into a single object, and preserveNullAndEmptyArrays: true keeps orders without a matching user, exactly like a LEFT JOIN. Leave it out to get INNER JOIN behavior.

The same pipeline in Mongoose

const top = await Order.aggregate([
  { $match: { status: 'paid', userId: new mongoose.Types.ObjectId(userId) } },
  { $group: { _id: null, spent: { $sum: '$total' } } },
]);

Mongoose does not convert types inside aggregate() the way it does in find(). A string id in $match won’t match an ObjectId, so convert it yourself, as shown above. This catches many developers out.

Performance rules

• Put $match first. Only the first stages can use indexes, and filtering early means later stages handle fewer documents.
• Index the fields you match and sort on, such as { status: 1, createdAt: -1 } for the monthly sales example.
• Project early when documents are large, so less data moves between stages.
• Use explain to check index use: db.orders.explain("executionStats").aggregate([...]).
• Join less than you would in SQL. If you always read two pieces of data together, consider storing them together in one document.

More SQL patterns and their pipeline versions

COUNT(DISTINCT …)

-- SQL
SELECT COUNT(DISTINCT userId) AS buyers FROM orders WHERE status = 'paid';

// MongoDB
db.orders.aggregate([
  { $match: { status: "paid" } },
  { $group: { _id: "$userId" } },
  { $count: "buyers" }
])

Grouping by the field removes duplicates, then $count counts the groups. For small result sets, db.orders.distinct("userId", { status: "paid" }).length also works.

CASE WHEN

-- SQL
SELECT name, CASE WHEN total >= 5000 THEN 'large' ELSE 'small' END AS size FROM orders;

// MongoDB
db.orders.aggregate([
  { $project: { name: 1, size: { $cond: [{ $gte: ["$total", 5000] }, "large", "small"] } } }
])

For more than two branches, use $switch with a list of case/then pairs and a default.

Top N per group

“The three best-selling products in each category” is awkward in SQL without window functions. In MongoDB, sort first, then collect into groups and slice:

db.products.aggregate([
  { $sort: { sold: -1 } },
  { $group: { _id: "$category", top: { $push: { name: "$name", sold: "$sold" } } } },
  { $project: { _id: 0, category: "$_id", top: { $slice: ["$top", 3] } } }
])

On MongoDB 5.2 and newer, the $topN accumulator does the same in one step and uses less memory: top: { $topN: { n: 3, sortBy: { sold: -1 }, output: { name: "$name", sold: "$sold" } } }.

Window functions: running totals

MongoDB 5.0 added $setWindowFields, the equivalent of SQL’s OVER (PARTITION BY … ORDER BY …):

db.orders.aggregate([
  { $match: { status: "paid" } },
  { $setWindowFields: {
      partitionBy: "$city",
      sortBy: { createdAt: 1 },
      output: { runningRevenue: { $sum: "$total", window: { documents: ["unbounded", "current"] } } }
  } }
])

Arrays: the part SQL doesn’t have

MongoDB documents often contain arrays, such as the items in an order. $unwind turns one document with an array of five items into five documents, one per item, which you can then group like rows:

// Best-selling products across all orders
db.orders.aggregate([
  { $match: { status: "paid" } },
  { $unwind: "$items" },
  { $group: { _id: "$items.productId", units: { $sum: "$items.qty" }, revenue: { $sum: { $multiply: ["$items.qty", "$items.price"] } } } },
  { $sort: { units: -1 } },
  { $limit: 10 }
])

In SQL, this data would live in a separate order_items table and you would join it. In MongoDB it is already inside the order, so no join is needed; $unwind does the job of the join.

Build pipelines step by step

Long pipelines are hard to debug when written in one go. Build them one stage at a time: run the first stage, check the output, add the next stage, check again. MongoDB Compass has an Aggregations tab that does exactly this, showing sample output after each stage, and it can export the finished pipeline as Node.js code. It is the fastest way to learn the pipeline style.

In Mongoose code, keeping each stage in a named variable makes long pipelines easier to read and reuse:

const paidSince = (date) => ({ $match: { status: 'paid', createdAt: { $gte: date } } });
const byCity = { $group: { _id: '$city', revenue: { $sum: '$total' } } };
const topFirst = { $sort: { revenue: -1 } };

const report = await Order.aggregate([paidSince(new Date('2026-01-01')), byCity, topFirst, { $limit: 10 }]);

Memory limits

Each pipeline stage can use up to 100 MB of memory by default. Large $group or $sort stages on big collections can hit that limit and fail. Recent MongoDB versions write to disk automatically when needed; on older versions or Atlas tiers that restrict it, pass { allowDiskUse: true }. Better still, filter early with $match so the heavy stages see fewer documents.

Thinking in pipelines instead of queries

The hardest part of switching from SQL is not the syntax but the mindset. A SQL query describes the result you want and lets the database decide how to get it. A pipeline describes the steps. That feels more manual at first, but it has a real advantage: you can read a pipeline from top to bottom and know exactly what happens to the data at each step, which makes complex reports easier to debug than one large SQL statement with several subqueries.

A good habit is to describe the report in plain words first, one sentence per step: “keep only paid orders from this year, split each order into its items, total the quantity per product, sort by quantity, keep the top ten.” Each sentence becomes one stage: $match, $unwind, $group, $sort, $limit. Once you can write the sentences, the pipeline almost writes itself.

Remember too that the order of stages matters in a way the order of SQL clauses doesn’t. A $match before $group filters documents, like WHERE; the same $match after $group filters groups, like HAVING. Moving a stage changes the result, so double-check the order whenever a report looks wrong.

Frequently asked questions