How to write GROUP BY in MongoDB if you only know SQL

#The one-liner that became a pipeline

In SQL, grouping is a single clause. You have written it a thousand times:

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

MongoDB expresses the same idea as an aggregation pipeline — a list of stages that documents flow through:

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

The mapping looks small here, but three details trip up SQL developers every time.

#The grouping key becomes _id

The GROUP BY column moves into the _id field of the $group stage. If you group by several columns, _id becomes a document:

{ $group: { _id: { customerId: "$customerId", year: "$year" } } }

Reading the result, your grouping keys now live inside _id, not at the top level of the document. Most post-processing code needs a $project stage to flatten them back out.

#Aggregate functions become accumulator operators

The surprising one is COUNT(*) — there is no counting operator, only the convention of summing the constant 1 for every document in the group.

#HAVING is just another $match

SQL separates row filters (WHERE) from group filters (HAVING). A pipeline has no such distinction: a $match before $group filters rows, a $match after it filters groups.

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

The order of stages is the semantics. Move the second $match before the $group and you have changed the question, not the performance.

#Let the translation happen for you

Keeping both shapes in your head is exactly the kind of bookkeeping a tool should do. TableCore converts the SQL you write into the MongoDB query it runs, and shows you both — so the pipeline stops being a wall between you and your data. Which GROUP BY shapes it translates, and which it refuses instead of approximating, is listed in SQL support.

Paste the example into the browser-based SQL to MongoDB converter to inspect the generated pipeline without installing anything. Then compare how the same mapping changes for a SQL JOIN and MongoDB $lookup, or follow one query all the way from SQL through MongoDB to C# code.