(opens in a new tab)

SQL to MongoDB Query Cheat Sheet with Examples

September 5, 2026 by Chat2DB Team

Coming to MongoDB from SQL, the syntax is the easy part. The genuinely confusing bits are the places where the mapping is not one-to-one: what _id does to your projections, why HAVING becomes a second $match, why a LEFT JOIN needs two stages, and which SQL constructs simply have no MongoDB equivalent at all. This cheat sheet covers all of it with runnable examples.

Throughout, assume two collections — orders and users — corresponding to two SQL tables of the same names.

The vocabulary

The one with real consequences is _id. MongoDB adds it to every document and includes it in every query result unless you explicitly exclude it. SQL has no equivalent, so almost every translated projection needs _id: 0.

SELECT and projection

SELECT * FROM users;

db.users.find({})

SELECT name, email FROM users;

db.users.find({}, { name: 1, email: 1, _id: 0 })

Without _id: 0 you get three fields back, not two. In a projection you either list fields to include (with 1) or fields to exclude (with 0) — you cannot mix, with the single exception of excluding _id alongside inclusions.

Aliasing a column requires an expression rather than a flag:

SELECT name AS full_name FROM users;

db.users.find({}, { full_name: "$name", _id: 0 })

SELECT DISTINCT has a dedicated helper for one field and needs $group for several:

SELECT DISTINCT country FROM users;
SELECT DISTINCT country, status FROM users;

db.users.distinct("country")
 
db.users.aggregate([
  { $group: { _id: { country: "$country", status: "$status" } } },
  { $project: { _id: 0, country: "$_id.country", status: "$_id.status" } }
])

WHERE

Equality is just a field-value pair. Everything else is an operator document.

WHERE status = 'active'
WHERE age >= 21
WHERE age BETWEEN 21 AND 65
WHERE status <> 'cancelled'
WHERE country IN ('US', 'GB', 'DE')
WHERE country NOT IN ('US', 'GB')
WHERE deleted_at IS NULL
WHERE deleted_at IS NOT NULL

{ status: "active" }
{ age: { $gte: 21 } }
{ age: { $gte: 21, $lte: 65 } }
{ status: { $ne: "cancelled" } }
{ country: { $in: ["US", "GB", "DE"] } }
{ country: { $nin: ["US", "GB"] } }
{ deleted_at: null }
{ deleted_at: { $ne: null } }

IS NULL is subtly different. { deleted_at: null } matches documents where the field is null and documents where the field is absent entirely. SQL has no "absent column" concept. To distinguish them:

// Field exists and is null
{ deleted_at: { $type: "null" } }
 
// Field does not exist at all
{ deleted_at: { $exists: false } }

Combining conditions: multiple fields in one document are an implicit AND.

WHERE status = 'active' AND age >= 21
WHERE status = 'active' OR vip = true
WHERE NOT (status = 'banned')
WHERE (a = 1 OR b = 2) AND c = 3

{ status: "active", age: { $gte: 21 } }
{ $or: [ { status: "active" }, { vip: true } ] }
{ $nor: [ { status: "banned" } ] }
{ $and: [ { $or: [ { a: 1 }, { b: 2 } ] }, { c: 3 } ] }

You need the explicit $and only when the same field appears twice or when you are nesting $or:

// Two conditions on the same field that cannot be merged
{ $and: [ { tags: "urgent" }, { tags: "open" } ] }

LIKE and pattern matching

LIKE becomes a regular expression. Translate % to .* and _ to ., and anchor whichever end the pattern anchors:

WHERE name LIKE 'The%'      -- starts with
WHERE name LIKE '%ing'      -- ends with
WHERE name LIKE '%data%'    -- contains
WHERE name LIKE 'a_c'       -- single-character wildcard
WHERE name ILIKE 'the%'     -- case-insensitive (PostgreSQL)

{ name: /^The/ }
{ name: /ing$/ }
{ name: /data/ }
{ name: /^a.c$/ }
{ name: /^the/i }

Only the anchored-prefix form (/^The/) can use a B-tree index efficiently. /data/ and /ing$/ force a collection scan, exactly as LIKE '%data%' forces a sequential scan in SQL. For genuine full-text search, use a text index instead:

db.articles.createIndex({ title: "text", body: "text" })
db.articles.find({ $text: { $search: "postgres replication" } })

ORDER BY, LIMIT, OFFSET

ORDER BY created_at DESC
ORDER BY country ASC, amount DESC
LIMIT 20
LIMIT 20 OFFSET 40

.sort({ created_at: -1 })
.sort({ country: 1, amount: -1 })
.limit(20)
.skip(40).limit(20)

1 is ascending, -1 descending. The order of keys in the sort document matters, exactly as column order matters in ORDER BY.

skip has the same problem as SQL OFFSET: the server must walk and discard every skipped document, so page 500 is far more expensive than page 1. Use a range condition on the sort key instead:

// Instead of .skip(10000).limit(20)
db.orders.find({ created_at: { $lt: lastSeenCreatedAt } })
         .sort({ created_at: -1 })
         .limit(20)

Aggregation: GROUP BY, COUNT, HAVING

Anything with grouping moves from find() to aggregate().

SELECT country, COUNT(*) AS total, AVG(amount) AS avg_amount
FROM   orders
WHERE  status <> 'cancelled'
GROUP  BY country
HAVING COUNT(*) > 10
ORDER  BY total DESC
LIMIT  5;

db.orders.aggregate([
  { $match: { status: { $ne: "cancelled" } } },
  { $group: {
      _id: "$country",
      total: { $sum: 1 },
      avg_amount: { $avg: "$amount" }
  } },
  { $match: { total: { $gt: 10 } } },
  { $project: { _id: 0, country: "$_id", total: 1, avg_amount: 1 } },
  { $sort: { total: -1 } },
  { $limit: 5 }
])

Three things to notice.

The grouping key goes in _id. For multiple columns it becomes a document, and you lift it back out in $project:

{ $group: { _id: { country: "$country", status: "$status" }, total: { $sum: 1 } } },
{ $project: { _id: 0, country: "$_id.country", status: "$_id.status", total: 1 } }

HAVING is a $match after $group. The pipeline is ordered, so a $match before $group filters input rows (WHERE) and one after filters groups (HAVING). Putting the filter in the wrong place is the single most common translation bug.

Stage order is a performance decision. $match and $sort placed before $group can use indexes; placed after, they operate on in-memory intermediate results. Always filter as early as the semantics allow.

The aggregate function mapping:

COUNT(*) versus COUNT(col) matters for the same reason it does in SQL: the second skips nulls.

A count with no grouping uses _id: null:

SELECT COUNT(*) FROM orders WHERE status = 'paid';

db.orders.countDocuments({ status: "paid" })
// or, in a pipeline:
db.orders.aggregate([
  { $match: { status: "paid" } },
  { $count: "total" }
])

JOIN

$lookup is the join. It puts matched documents into an array field, which is why $unwind almost always follows.

SELECT o.id, o.amount, u.name
FROM   orders o
JOIN   users u ON o.user_id = u.id
WHERE  o.amount > 100;

db.orders.aggregate([
  { $match: { amount: { $gt: 100 } } },
  { $lookup: {
      from: "users",
      localField: "user_id",
      foreignField: "_id",
      as: "user"
  } },
  { $unwind: "$user" },
  { $project: { _id: 0, id: "$_id", amount: 1, name: "$user.name" } }
])

$unwind: "$user" turns the one-element array into a plain subdocument and — importantly — drops orders with no matching user. That is an INNER JOIN.

For a LEFT JOIN, keep the unmatched rows:

{ $unwind: { path: "$user", preserveNullAndEmptyArrays: true } }

For non-equality joins, use the pipeline form with let and $expr:

SELECT o.id, p.name
FROM   orders o
JOIN   promotions p
  ON   o.created_at BETWEEN p.starts_at AND p.ends_at
 AND   o.amount >= p.min_amount;

db.orders.aggregate([
  { $lookup: {
      from: "promotions",
      let: { created: "$created_at", amt: "$amount" },
      pipeline: [
        { $match: { $expr: { $and: [
            { $lte: ["$starts_at", "$$created"] },
            { $gte: ["$ends_at",   "$$created"] },
            { $gte: ["$$amt",      "$min_amount"] }
        ] } } },
        { $project: { _id: 0, name: 1 } }
      ],
      as: "promo"
  } },
  { $unwind: "$promo" }
])

Note the $$ prefix for variables defined in let, versus the single $ for fields of the joined collection. Mixing these up produces an empty result set with no error, which is a miserable debugging experience.

$lookup benefits enormously from an index on foreignField. Without one, each input document triggers a collection scan of the joined collection.

INSERT, UPDATE, DELETE

INSERT INTO users (name, email) VALUES ('Ada', 'ada@example.com');
 
INSERT INTO users (name, email)
VALUES ('Ada', 'a@x.com'), ('Grace', 'g@x.com');
 
UPDATE users SET status = 'active' WHERE id = 5;
 
UPDATE users SET login_count = login_count + 1 WHERE id = 5;
 
UPDATE orders SET status = 'shipped' WHERE status = 'paid';
 
DELETE FROM users WHERE status = 'banned';

db.users.insertOne({ name: "Ada", email: "ada@example.com" })
 
db.users.insertMany([
  { name: "Ada",   email: "a@x.com" },
  { name: "Grace", email: "g@x.com" }
])
 
db.users.updateOne({ _id: 5 }, { $set: { status: "active" } })
 
db.users.updateOne({ _id: 5 }, { $inc: { login_count: 1 } })
 
db.orders.updateMany({ status: "paid" }, { $set: { status: "shipped" } })
 
db.users.deleteMany({ status: "banned" })

updateOne versus updateMany is not optional. updateOne modifies exactly one document even if the filter matches a thousand. SQL UPDATE always updates everything matching, so a mechanical translation that reaches for updateOne silently under-updates.

Forgetting $set replaces the whole document. db.users.updateOne({_id: 5}, {status: "active"}) is an error in modern drivers, but replaceOne will happily discard every other field.

UPSERT maps to the upsert option:

INSERT INTO counters (name, value) VALUES ('hits', 1)
ON CONFLICT (name) DO UPDATE SET value = counters.value + 1;

db.counters.updateOne(
  { name: "hits" },
  { $inc: { value: 1 }, $setOnInsert: { name: "hits" } },
  { upsert: true }
)

What has no equivalent

Be honest about these rather than contorting a pipeline:

Correlated subqueries and EXISTS. No direct translation. Model with $lookup followed by a $match on the array size, or restructure the data.

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

db.users.aggregate([
  { $lookup: { from: "orders", localField: "_id", foreignField: "user_id", as: "o" } },
  { $match: { "o.0": { $exists: true } } },
  { $project: { o: 0 } }
])

UNION / UNION ALL. $unionWith covers UNION ALL; deduplicating for a true UNION needs a $group afterwards.

INTERSECT and EXCEPT. Nothing built in.

Recursive CTEs. $graphLookup handles hierarchy traversal within one collection, which covers the common tree/ancestor case but is not general recursion.

Window functions. $setWindowFields (MongoDB 5.0+) covers ROW_NUMBER, RANK, SUM() OVER and moving averages — a genuine equivalent for most uses, though the syntax is quite different:

db.orders.aggregate([
  { $setWindowFields: {
      partitionBy: "$country",
      sortBy: { created_at: 1 },
      output: {
        running_total: { $sum: "$amount", window: { documents: ["unbounded", "current"] } },
        rank: { $rank: {} }
      }
  } }
])

Foreign key constraints and ON DELETE CASCADE. No equivalent. Referential integrity is the application's responsibility, or you embed the data instead of referencing it.

Rewriting versus translating

The last point is the one that matters most. A mechanically translated SQL query with three $lookup stages is a sign the schema was not designed for MongoDB. In a document database, data that is always read together usually belongs in the same document:

// Instead of orders + order_items + users joined at read time
{
  _id: ObjectId("..."),
  user: { _id: 5, name: "Ada", email: "ada@example.com" },  // denormalised
  items: [
    { sku: "A-1", qty: 2, price: 19.99 },
    { sku: "B-7", qty: 1, price: 45.00 }
  ],
  total: 84.98,
  created_at: ISODate("2026-09-05T10:00:00Z")
}

That document answers "show me this order" with a single index lookup and no joins at all. The cost is that a user's name is now stored in every order, so renaming a user means updating many documents — which is the correct trade to consider, and the reason a straight table-per-collection port usually disappoints.

If you are working across both engines during a migration, being able to run SQL and MongoDB queries side by side helps a lot. Chat2DB (opens in a new tab) connects to PostgreSQL, MySQL and MongoDB in the same window and can generate or explain queries for any of them with AI, or you can use the web version (opens in a new tab) without installing anything.

Summary

Most SQL translates cleanly: WHERE to a filter document or $match, GROUP BY to $group with the key in _id, HAVING to a second $match after it, ORDER BY to $sort, LIMIT/OFFSET to $limit/$skip, LIKE to an anchored regex, and JOIN to $lookup plus $unwind — with preserveNullAndEmptyArrays: true for a LEFT JOIN. Watch for _id appearing in projections, updateOne versus updateMany, and the difference between a null field and a missing one. Correlated subqueries, set operations and referential integrity have no equivalent; when a translation needs several of those, the answer is usually a different document model rather than a cleverer pipeline.