SQL to MongoDB: WHERE, ORDER BY, GROUP BY, and JOIN translated

Posted by Kyle Hankinson July 12, 2026

---

If you come to MongoDB from SQL, the frustrating part is not the concepts. You know exactly what result you want; you just cannot spell it. The good news is that half of SQL maps onto one method, find(), almost mechanically. The catch is the other half: GROUP BY and JOIN have no place in find() at all and require a second API, the aggregation pipeline. Knowing where that line falls is most of the battle.

Every example below was executed on MongoDB 8.2.11 and spot-checked on 7.0.37 (the official mongo:8 and mongo:7 Docker images) using mongosh, which has been the default shell since MongoDB 5.0. Output shown is real.

First, the vocabulary. MongoDB's own SQL comparison chart uses the same mapping:

SQL	MongoDB
database	database
table	collection
row	document
column	field
primary key	_id (always present, always unique)

And the clause map, which the rest of this article walks through:

SQL clause	MongoDB equivalent
WHERE	the filter document passed to find()
LIKE	$regex
ORDER BY	.sort()
LIMIT / OFFSET	.limit() / .skip()
GROUP BY	$group stage in aggregate()
HAVING	$match stage placed after $group
JOIN	$lookup stage in aggregate()
COUNT(*)	countDocuments()
SELECT DISTINCT	distinct()
UNION ALL	$unionWith stage (MongoDB 4.4+)

The examples use two small collections you can recreate in seconds:

db.customers.insertMany([
  { _id: 1, name: "Alice Zhang",   city: "Toronto", country: "CA" },
  { _id: 2, name: "Bob Smith",     city: "Boston",  country: "US" },
  { _id: 3, name: "Carol Alvarez", city: "Chicago", country: "US" },
  { _id: 4, name: "Dan Smithers",  city: "Halifax", country: "CA" },
  { _id: 5, name: "Eve Laurent",   city: "Paris",   country: "FR" }
]);
db.orders.insertMany([
  { _id: 101, customerId: 1, product: "keyboard", amount: 89.00,  status: "shipped" },
  { _id: 102, customerId: 1, product: "monitor",  amount: 349.99, status: "shipped" },
  { _id: 103, customerId: 2, product: "mouse",    amount: 25.50,  status: "pending" },
  { _id: 104, customerId: 3, product: "monitor",  amount: 349.99, status: "shipped" },
  { _id: 105, customerId: 3, product: "webcam",   amount: 59.00,  status: "cancelled" },
  { _id: 106, customerId: 3, product: "keyboard", amount: 89.00,  status: "shipped" },
  { _id: 107, customerId: 5, product: "dock",     amount: 199.00, status: "pending" }
]);

WHERE is a document, not a string

The filter you would put after WHERE becomes a JSON document. Equality is just field: value; everything else uses $-prefixed operators.

SELECT product, amount FROM orders WHERE status = 'shipped' AND amount >= 300;

db.orders.find({ status: "shipped", amount: { $gte: 300 } },
               { _id: 0, product: 1, amount: 1 })
// { product: 'monitor', amount: 349.99 }  (twice, orders 102 and 104)

Two things to absorb here. The second argument is the projection, MongoDB's SELECT list (1 includes a field, _id: 0 suppresses the key you did not ask for). And multiple conditions in one filter document are implicitly ANDed; OR needs the explicit operator:

db.orders.find({ $or: [ { status: "cancelled" }, { amount: { $lt: 30 } } ] })
// matches the mouse (25.5, pending) and the webcam (59, cancelled)

IN translates directly: { status: { $in: ["pending", "cancelled"] } } returned the mouse, webcam, and dock orders. The comparison operators are $eq, $ne, $gt, $gte, $lt, $lte.

LIKE becomes $regex

There is no LIKE operator; patterns are regular expressions. LIKE 'Bob%' is an anchored regex, and LIKE '%smith%' (case-insensitive) is an unanchored one with the i option:

db.customers.find({ name: { $regex: "^Bob" } })                    // Bob Smith
db.customers.find({ name: { $regex: "smith", $options: "i" } })    // Bob Smith, Dan Smithers

One performance note carries over from SQL intuition: like LIKE 'Bob%', the anchored form ^Bob can use an index on name; a pattern that starts with a wildcard cannot.

ORDER BY, LIMIT, and OFFSET chain onto the cursor

SELECT product, amount FROM orders ORDER BY amount DESC LIMIT 3;

db.orders.find({}, { _id: 0, product: 1, amount: 1 }).sort({ amount: -1 }).limit(3)
// monitor 349.99, monitor 349.99, dock 199

1 is ascending, -1 descending, and OFFSET is .skip(): .sort({ placedAt: 1 }).skip(2).limit(2) is ORDER BY placedAt LIMIT 2 OFFSET 2. The same caveat as SQL's OFFSET applies, since skipped documents are still walked and discarded server-side.

GROUP BY means leaving find() behind

This is the line in the sand. find() cannot aggregate, so GROUP BY switches you to aggregate(), which takes an array of stages:

SELECT status, COUNT(*) AS orderCount, SUM(amount) AS total
FROM orders GROUP BY status;

db.orders.aggregate([
  { $group: { _id: "$status", orderCount: { $sum: 1 }, total: { $sum: "$amount" } } }
])
// { _id: 'cancelled', orderCount: 1, total: 59 }
// { _id: 'pending',   orderCount: 2, total: 224.5 }
// { _id: 'shipped',   orderCount: 4, total: 877.98 }

The grouping key always lands in a field called _id, not under its original name, and field references on the right-hand side need a $ prefix ("$status", "$amount"). Both rules trip up nearly everyone once. HAVING is nothing special in MongoDB: it is just a second filter placed after the $group:

SELECT customerId, SUM(amount) AS total FROM orders
GROUP BY customerId HAVING SUM(amount) > 400;

db.orders.aggregate([
  { $group: { _id: "$customerId", total: { $sum: "$amount" } } },
  { $match: { total: { $gt: 400 } } }
])
// { _id: 1, total: 438.99 }
// { _id: 3, total: 497.99 }

The pipeline is a deep enough topic to earn its own article; the aggregation pipeline for beginners builds one stage by stage.

JOIN becomes $lookup (but ask if you need it)

Before translating your JOIN, pause: idiomatic MongoDB often embeds related data in one document (order lines inside the order), which makes the join disappear. When the data really lives in two collections, $lookup inside aggregate() does the job:

SELECT o.product, o.amount, 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, product: 1, amount: 1, customerName: "$customer.name" } }
])
// { product: 'keyboard', amount: 89, customerName: 'Alice Zhang' }  ...7 rows total

$lookup attaches the matches as an array; $unwind flattens it to one document per match, which is what makes the output look like an inner join. Without the $unwind, $lookup behaves like a LEFT OUTER JOIN: running it from customers toward orders kept Dan Smithers with an empty array (orderCount: 0 after a $size). If the join varieties themselves are the fuzzy part, the SQL joins explained walkthrough covers them engine-agnostically. Version notes: $lookup has existed since 3.2, and per the $lookup documentation, the concise correlated-subquery form (localField/foreignField combined with a pipeline) requires MongoDB 5.0+.

COUNT and DISTINCT

SELECT COUNT(*) FROM orders is db.orders.countDocuments(), which returned 7; pass a filter for a WHERE'd count (countDocuments({ status: "shipped" }) returned 4). SELECT DISTINCT status FROM orders is db.orders.distinct("status"), which returned ['cancelled', 'pending', 'shipped'].

The fastest way to make these mappings stick is to run both forms against live data and look at what comes back. Every snippet in this article pastes directly into the shell interface of SQLPro for MongoDB, where returned documents land in a table-style grid, so the row-to-document correspondence stops being abstract and there is a visual find builder when you would rather not type the filter document at all.

Keep the clause table above within reach for the first few weeks. The find-side translations become muscle memory quickly; the real shift is remembering that the moment a query aggregates or joins, you are writing a pipeline.

---