If You Know SQL: Queries Side by Side
The SQL you already write beside its MongoDB twin, from SELECT with WHERE, ORDER BY and a row limit to INSERT, UPDATE and DELETE, with a first look at GROUP BY and JOIN in a pipeline.
Apprentice · MongoDB · SQL · Updated
After this lesson you can
- Translate a SELECT with WHERE, ORDER BY and a row limit into find with a filter, a projection, sort and limit
- Translate INSERT, UPDATE and DELETE into insertOne, updateOne and deleteOne, with the WHERE clause as the filter
Key takeaways
- SELECT's column list becomes find's projection, WHERE becomes its filter, ORDER BY becomes sort, and a row limit becomes limit.
- INSERT, UPDATE and DELETE become insertOne, updateOne and deleteOne, and the WHERE clause becomes the filter.
- The rows affected count becomes matchedCount, modifiedCount and deletedCount.
- GROUP BY and JOIN become stages of an aggregation pipeline, $group and $lookup.
Optional: this lesson is for readers who already know SQL. Nothing later in the course depends on it.
When you know SQL, the fastest way into MongoDB's queries is a phrasebook: the sentence you'd write, beside the one MongoDB wants. You'll use it whenever you move a query from one database to the other, or read MongoDB code in a project that also has SQL. The examples use the Riverbend Bike Shop store from If You Know SQL: One Store Designed Both Ways, as tables and as documents. The SQL is standard SQL, and its output comes from SQL Server.
SELECT beside find
The two most expensive bikes, with their names and prices:
SELECT name, price
FROM products
WHERE type = 'bike'
ORDER BY price DESC
OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY;
name price
----------------- -------
Gravel Runner 1199.00
Riverbend Cruiser 649.00
db.products.find({ type: "bike" }, { _id: 0, name: 1, price: 1 }).sort({ price: -1 }).limit(2)
[
{ name: 'Gravel Runner', price: Decimal128('1199.00') },
{ name: 'Riverbend Cruiser', price: Decimal128('649.00') }
]
Clause by clause:
| SQL | MongoDB | Here |
|---|---|---|
WHERE |
the filter, find's first argument |
{ type: "bike" } |
| the column list | the projection, the second argument | { _id: 0, name: 1, price: 1 } |
ORDER BY ... DESC |
sort, with -1 for down |
.sort({ price: -1 }) |
FETCH FIRST 2 ROWS ONLY |
limit |
.limit(2) |
OFFSET |
skip |
(not needed here) |
Two differences to watch. A projection always includes _id unless you turn it off with _id: 0, where SELECT gives only the columns you name. And FETCH FIRST is the standard spelling of a row limit: MySQL, PostgreSQL and SQLite also write LIMIT 2, and SQL Server also writes TOP 2. MongoDB has one way, .limit(2).
INSERT, UPDATE and DELETE
Adding a product is a row in SQL and a document in MongoDB:
INSERT INTO products (product_id, name, type, price, stock)
VALUES (7, 'Commuter Rack', 'rack', 45.00, 6);
(1 rows affected)
db.products.insertOne({ _id: 7, name: "Commuter Rack", type: "rack", price: NumberDecimal("45.00"), stock: 6 })
{ acknowledged: true, insertedId: 7 }
Selling the last Riverbend Cruiser, only if one is left, is the conditional update from Transactions, and Why You'll Need Them Less. The WHERE becomes the filter, and SET stock = stock - 1 becomes $inc with -1:
UPDATE products
SET stock = stock - 1
WHERE product_id = 1 AND stock >= 1;
(1 rows affected)
db.products.updateOne({ _id: 1, stock: { $gte: 1 } }, { $inc: { stock: -1 } })
{
acknowledged: true,
insertedId: null,
matchedCount: 1,
modifiedCount: 1,
upsertedCount: 0
}
Run either one a second time and it changes nothing: SQL reports (0 rows affected), and MongoDB matchedCount: 0. The rows affected count became two numbers: how many documents the filter matched, and how many actually changed. Removing the rack again:
DELETE FROM products WHERE product_id = 7;
(1 rows affected)
db.products.deleteOne({ _id: 7 })
{ acknowledged: true, deletedCount: 1 }
One trap: UPDATE and DELETE without a WHERE change every row, but updateOne and deleteOne change only the first match. The "every match" versions are updateMany and deleteMany, so choose the method on purpose.
GROUP BY and JOIN: a first look at the pipeline
Counting products by type is a GROUP BY:
SELECT type, COUNT(*) AS products
FROM products
GROUP BY type
ORDER BY type;
type products
------ --------
bike 3
care 1
helmet 1
light 1
MongoDB does this in an aggregation pipeline: a list of stages that documents pass through in order. $group (read "group") makes one result per value of "$type" (read "dollar type": each product's type) and adds 1 for each product, and $sort puts the types in order:
db.products.aggregate([ { $group: { _id: "$type", products: { $sum: 1 } } }, { $sort: { _id: 1 } } ])
[
{ _id: 'bike', products: 3 },
{ _id: 'care', products: 1 },
{ _id: 'helmet', products: 1 },
{ _id: 'light', products: 1 }
]
A JOIN has a stage too. Each order with its customer's name:
SELECT o.order_id, c.name AS customer, o.status
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_id;
order_id customer status
-------- -------- -------
1001 Maya placed
1002 Leo shipped
1003 Maya shipped
$lookup (read "look up") brings in the documents from customers whose _id matches each order's customerId, as an array named customer, and $project keeps only the fields to show:
db.orders.aggregate([ { $lookup: { from: "customers", localField: "customerId", foreignField: "_id", as: "customer" } }, { $project: { status: 1, "customer.name": 1 } } ])
[
{ _id: 1001, status: 'placed', customer: [ { name: 'Maya' } ] },
{ _id: 1002, status: 'shipped', customer: [ { name: 'Leo' } ] },
{ _id: 1003, status: 'shipped', customer: [ { name: 'Maya' } ] }
]
You'll reach for $lookup less often than for JOIN, though: an order's lines are already inside the order, so the join SQL needs most often isn't needed at all. Module 4 teaches the pipeline stage by stage, $group and $lookup included.
Check yourself
2 questions to try on the lesson page.
So what
Your SQL still works as a way of thinking: WHERE is a filter, ORDER BY a sort, UPDATE an update with a filter. What changes is the spelling, and how often you need a join at all.