SQL to MongoDB: Mapping SELECT, WHERE, GROUP BY and JOIN

Moving from SQL to MongoDB is mostly new vocabulary, plus one real change in how you think about joins. Most of what you know about queries carries over. This post maps each SQL clause to its MongoDB form and tests every one.

The Vocabulary Map

Start with the words. Each SQL idea has a MongoDB twin, and a few of them live in the same command.

Two differences matter. Documents in one collection can have different fields, and a document can hold arrays and nested documents. My post on SQL terms vs MongoDB terms goes deeper into the vocabulary. If MongoDB isn’t installed yet, here is how to install MongoDB on Windows.

Every SQL to MongoDB translation follows the same path. Find the filter first, then the grouping, then the join, then the output columns. The sections below walk that path once, on a small shop.

A Vegetarian Shop to Test On

Every result below comes from MongoDB 9.0.2 with mongosh 2.13.0. The shop has four customers and seven orders. Dara has no orders yet, which will matter later. Each order carries its items inside it as an array.

use sqlToMongoDemo
db.customers.insertMany([
  { _id: 1, name: 'Asha', city: 'Austin' },
  { _id: 2, name: 'Ben',  city: 'Denver' },
  { _id: 3, name: 'Chen', city: 'Austin' },
  { _id: 4, name: 'Dara', city: 'Seattle' }
])
db.orders.insertMany([
  { _id: 101, customerId: 1, status: 'paid',      total: 30, items: [ { name: 'Oat Milk', qty: 2, price: 5 }, { name: 'Tofu Block', qty: 5, price: 4 } ] },
  { _id: 102, customerId: 1, status: 'paid',      total: 12, items: [ { name: 'Lentils', qty: 3, price: 4 } ] },
  { _id: 103, customerId: 2, status: 'paid',      total: 30, items: [ { name: 'Mango Juice', qty: 4, price: 5 }, { name: 'Oat Milk', qty: 2, price: 5 } ] },
  { _id: 104, customerId: 2, status: 'pending',   total: 8,  items: [ { name: 'Tofu Block', qty: 2, price: 4 } ] },
  { _id: 105, customerId: 3, status: 'paid',      total: 40, items: [ { name: 'Lentils', qty: 10, price: 4 } ] },
  { _id: 106, customerId: 3, status: 'cancelled', total: 10, items: [ { name: 'Mango Juice', qty: 2, price: 5 } ] },
  { _id: 107, customerId: 1, status: 'paid',      total: 13, items: [ { name: 'Tofu Block', qty: 2, price: 4 }, { name: 'Mango Juice', qty: 1, price: 5 } ] }
])

SELECT, WHERE and ORDER BY

A SQL query that picks two columns, filters rows and sorts them becomes one find call. The filter comes first, then the projection, then sort. A projection of 1 keeps a field. MongoDB returns _id unless you switch it off with 0.

SELECT name, city FROM customers WHERE city = 'Austin' ORDER BY name;

db.customers.find({ city: 'Austin' }, { _id: 0, name: 1, city: 1 }).sort({ name: 1 })
// [ { name: 'Asha', city: 'Austin' },
//   { name: 'Chen', city: 'Austin' } ]

Comparisons use operators written as field names that start with a dollar sign. $gt means greater than. Two conditions in one filter are joined with AND. In the next query, sort -1 means descending, like DESC.

SELECT id, total FROM orders WHERE status = 'paid' AND total > 20 ORDER BY total DESC;

db.orders.find({ status: 'paid', total: { $gt: 20 } }, { total: 1 }).sort({ total: -1 })
// [ { _id: 105, total: 40 },
//   { _id: 101, total: 30 },
//   { _id: 103, total: 30 } ]

GROUP BY Becomes a Pipeline

Grouping uses the aggregation pipeline. A pipeline is a list of stages, and each stage hands its output to the next one. The $group stage takes an _id, which is the grouping key, and one or more accumulators such as $sum.

SELECT status, COUNT(*) AS orders FROM orders GROUP BY status ORDER BY status;

db.orders.aggregate([
  { $group: { _id: '$status', orders: { $sum: 1 } } },
  { $sort: { _id: 1 } }
])
// [ { _id: 'cancelled', orders: 1 },
//   { _id: 'paid', orders: 5 },
//   { _id: 'pending', orders: 1 } ]

The dollar sign in ‘$status’ means “the value of the field status”. Without it, MongoDB reads the text as a plain string. Counting uses { $sum: 1 }, which adds one per document, the same job as COUNT(*).

SQL’s HAVING has no stage of its own. It filters groups, and so does a $match placed after $group, because that stage sees the grouped documents. WHERE and HAVING are the same stage in two positions.

JOIN Becomes $lookup

Now the real test: paid revenue per customer, with the name and the city. In SQL it is a join and a group. In MongoDB it is one pipeline. Compare the two side by side.

SELECT c.name, c.city, COUNT(*) AS orders, SUM(o.total) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
GROUP BY c.id, c.name, c.city
ORDER BY revenue DESC;

db.orders.aggregate([
  { $match: { status: 'paid' } },
  { $group: { _id: '$customerId', orders: { $sum: 1 }, revenue: { $sum: '$total' } } },
  { $lookup: { from: 'customers', localField: '_id', foreignField: '_id', as: 'customer' } },
  { $unwind: '$customer' },
  { $project: { _id: 0, name: '$customer.name', city: '$customer.city', orders: 1, revenue: 1 } },
  { $sort: { revenue: -1 } }
])
// [ { orders: 3, revenue: 55, name: 'Asha', city: 'Austin' },
//   { orders: 1, revenue: 40, name: 'Chen', city: 'Austin' },
//   { orders: 1, revenue: 30, name: 'Ben', city: 'Denver' } ]

Read it stage by stage. $match is the WHERE clause. $group builds one result per customer id. $lookup finds the customer with that id and stores the match in an array called customer. $unwind turns that one-item array into a plain object, and $project picks the final fields.

Notice the order. SQL joins first and groups after. This pipeline groups first, so $lookup runs three times, once per customer, instead of five times, once per paid order. Filter and group early, then join the small result.

The $unwind stage also decides the kind of join. A document whose lookup array is empty is dropped, so the result above is an inner join. Dara has no orders and doesn’t appear. For a left join, start from customers and keep the empty arrays.

db.customers.aggregate([
  { $lookup: { from: 'orders', localField: '_id', foreignField: 'customerId', as: 'o' } },
  { $project: { _id: 0, name: 1, orders: { $size: '$o' } } },
  { $sort: { name: 1 } }
])
// [ { name: 'Asha', orders: 3 }, { name: 'Ben', orders: 2 },
//   { name: 'Chen', orders: 2 }, { name: 'Dara', orders: 0 } ]

When Embedding Beats a Join

Look at one order as MongoDB stores it. The items sit inside the order. In SQL they would live in a second table, and every read would need a join. Here one find returns the whole order.

db.orders.findOne({ _id: 103 })
// { _id: 103, customerId: 2, status: 'paid', total: 30,
//   items: [ { name: 'Mango Juice', qty: 4, price: 5 },
//            { name: 'Oat Milk', qty: 2, price: 5 } ] }

Searching inside the array also needs no join. A dotted path reaches into it. When you do need totals per item, $unwind opens the array into one document per item, and $group counts them.

db.orders.find({ 'items.name': 'Tofu Block' }, { customerId: 1 })
// [ { _id: 101, customerId: 1 }, { _id: 104, customerId: 2 },
//   { _id: 107, customerId: 1 } ]
db.orders.aggregate([
  { $unwind: '$items' },
  { $group: { _id: '$items.name', units: { $sum: '$items.qty' } } },
  { $sort: { units: -1, _id: 1 } }
])
// [ { _id: 'Lentils', units: 13 }, { _id: 'Tofu Block', units: 9 },
//   { _id: 'Mango Juice', units: 7 }, { _id: 'Oat Milk', units: 4 } ]

Embedding has a hard limit. A single MongoDB document can’t grow past 16 megabytes. An order with ten items is far below that. A customer document that collects ten years of orders is not. Unbounded lists belong in their own collection.

You could say the customer name should be embedded in every order too, so the join is gone. Fair point, and for a report that never changes it works. But a copy drifts. When a customer moves from Austin to Denver, you must update every old order, or the data disagrees with itself.

That gives a working rule. Embed what belongs to one parent and is read with it, such as order items. Use a separate collection and $lookup when the data is shared or changes on its own, such as customers. Do the same when it grows without limit.

A Short Checklist

When you translate a SQL query, go clause by clause. WHERE becomes $match, or the first argument of find. GROUP BY becomes $group. JOIN becomes $lookup, followed by $unwind when you want an inner join. ORDER BY becomes $sort, and SELECT becomes $project. Put filters and groups as early as you can, so the join sees fewer documents. Then check your work. Run the SQL version on the same data and compare the numbers. A SQL to MongoDB translation is correct when both give the same totals.

When you finish testing, remove the example database.

use sqlToMongoDemo
db.dropDatabase()

Learning MongoDB from SQL is not starting over, it is learning where each clause went.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.