How to Convert SQL Queries to MongoDB: find(), aggregate() and $lookup

To convert SQL queries to MongoDB you need two mental models: find() for simple filtered reads, and the aggregation pipeline for anything that groups, joins or reshapes data. Each SQL clause maps to a specific argument or pipeline stage, and a few SQL habits (NULL logic, case-insensitive LIKE, string dates) behave differently once the data lives in documents. This guide walks through the mapping clause by clause with real output from the SQL to MongoDB converter, then shows what you still need to write by hand.

Convert SQL queries to MongoDB clause by clause

MongoDB also has shortcuts for two common patterns: SELECT COUNT(*) becomes countDocuments() and SELECT DISTINCT col becomes distinct(). The official SQL to aggregation mapping chart is a useful companion.

SELECT, WHERE, ORDER BY and LIMIT with find()

A filtered, sorted, paged query converts to a single find() call. This input:

SELECT name, email FROM customers
WHERE deleted_at IS NULL AND (plan = 'pro' OR seats > 10)
ORDER BY name LIMIT 20 OFFSET 40;

produces:

db.customers.find({
  deleted_at: null,
  $or: [
    { plan: 'pro' },
    { seats: { $gt: 10 } }
  ]
}, { name: 1, email: 1, _id: 0 }).sort({ name: 1 }).skip(40).limit(20);

Conditions joined with AND become keys of one filter object, and OR becomes an $or array. The projection lists the selected columns and excludes _id, which MongoDB would otherwise return automatically. The order of .sort(), .skip() and .limit() in the chain does not matter: the server always sorts first, then skips, then limits, which matches SQL. For deep pages, a range filter on the sort key (for example name: { $gt: lastSeenName }) scales better than a large skip(), exactly as keyset pagination does in SQL.

Watch the types in literals. The converter keeps '2026-01-01' as a string, so created_at >= '2026-01-01' becomes created_at: { $gte: '2026-01-01' }. If your documents store real dates, change it to { $gte: ISODate('2026-01-01T00:00:00Z') }; otherwise the comparison matches nothing.

LIKE to $regex

SQL wildcards translate to regular expressions: % becomes .*, _ becomes ., and the pattern is anchored unless it starts or ends with %. Real conversions of SELECT * queries with these conditions:

-- name LIKE 'Jo_n%'
db.customers.find({ name: { $regex: '^Jo.n' } });

-- email LIKE '%@example.com'
db.customers.find({ email: { $regex: '@example\\.com$' } });

-- name NOT LIKE 'Test%'
db.customers.find({ name: { $not: { $regex: '^Test' } } });

Two details need attention:

• Case sensitivity. MySQL's default collations make LIKE case-insensitive, while $regex is case-sensitive. Add $options: 'i' when you need the MySQL behaviour, for example { email: { $regex: '@example\\.com$', $options: 'i' } }. With the PostgreSQL dialect selected, ILIKE is converted with that option already set.
• Indexes. A case-sensitive pattern anchored with ^ can use an index on the field; a leading wildcard such as '%@example.com' has to examine every indexed value, just as it would in SQL.

Try the generated patterns in the regex tester before running them, and see regex basics for developers if anchors and escapes are new to you.

NULL handling

SQL has three-valued logic; MongoDB has documents where a field may be null or missing. The converter follows MongoDB conventions:

The last row differs from SQL, where NOT IN and <> never match NULL rows. To keep SQL semantics, add the null check yourself: { country: { $nin: ['US', 'CA'], $ne: null } }. Use { phone: { $exists: false } } when you only want documents that lack the field entirely.

GROUP BY and HAVING with aggregate()

Any aggregate function or GROUP BY switches the output to a pipeline:

SELECT customer_id, COUNT(*) AS orders, SUM(total) AS spent
FROM orders WHERE status = 'paid'
GROUP BY customer_id HAVING spent > 500
ORDER BY spent DESC LIMIT 10;

db.orders.aggregate([
  { $match: { status: 'paid' } },
  {
    $group: {
      _id: '$customer_id',
      orders: { $sum: 1 },
      spent: { $sum: '$total' }
    }
  },
  { $match: { spent: { $gt: 500 } } },
  { $project: { _id: 0, customer_id: '$_id', orders: 1, spent: 1 } },
  { $sort: { spent: -1 } },
  { $limit: 10 }
]);

The stage order mirrors SQL's logical order: WHERE filters before grouping, the group key goes into _id, HAVING becomes a second $match, and $project renames _id back to the column name. Grouping by several columns produces a compound key such as _id: { country: '$country', city: '$city' }.

HAVING can use the alias (HAVING spent > 500) or repeat the aggregate call, as in HAVING COUNT(*) > 10; the converter resolves the call to the field it has after $group. Also note that COUNT(col) is not COUNT(*): SQL skips NULLs there, so COUNT(phone) is emitted as { $sum: { $cond: [{ $gt: ['$phone', null] }, 1, 0] } }, which counts only documents where the field is present and not null, while COUNT(*) stays { $sum: 1 }.

JOINs: writing $lookup by hand

For any JOIN, the converter stops with a clear message instead of guessing:

JOINs are not converted automatically; MongoDB uses $lookup — start from the single-table query.

That is deliberate: in MongoDB the right answer might be an embedded array rather than a join. When a join is appropriate, SELECT o.total, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'paid' becomes:

db.orders.aggregate([
  { $match: { status: 'paid' } },
  { $lookup: {
      from: 'customers',
      localField: 'customer_id',
      foreignField: '_id',
      as: 'customer'
  } },
  { $unwind: '$customer' },
  { $project: { _id: 0, total: 1, name: '$customer.name' } }
]);

$lookup always returns an array. $unwind without options drops orders with no matching customer, which gives INNER JOIN behaviour; use { $unwind: { path: '$customer', preserveNullAndEmptyArrays: true } } for a LEFT JOIN. Put $match first so fewer documents are joined, and index foreignField (here _id already is). The $lookup reference covers the pipeline form for multi-condition joins.

What converts automatically and what needs manual work

In a filter, replace the { $date: '$$NOW' } placeholder with new Date() or a fixed ISODate(…) value. In INSERT and UPDATE the converter already writes new Date(); $currentDate: { updated_at: true } is the alternative when the server clock should set the value. Column names are copied as-is, so an SQL id usually needs to become _id; the ObjectId decoder helps when those values are ObjectIds.

A practical migration workflow

1. Tidy the query with the SQL formatter and check it with the SQL validator so parse errors are not mistaken for conversion limits.
2. Run it through the SQL to MongoDB converter with the matching SQL dialect selected, and read the output line by line against the table above.
3. Fix types (dates, numeric strings), NULL semantics and _id names, then add $lookup stages by hand.
4. Compare results against the SQL database on a sample, and check the plan with .explain('executionStats').

If you are designing the target collections at the same time, JSON to Mongoose schema turns a sample document into a typed schema to start from.