Convert a SQL query to MongoDB: find(), aggregate() with $lookup and $group, or a write, for mongosh, Node.js or Python
Paste a SELECT, INSERT, UPDATE or DELETE. Get the MongoDB query that returns the same rows, and a plain note for anything MongoDB treats differently.
How to use it
Paste one or more SQL statements, or open a .sql file. Choose mongosh to paste into the shell or Compass, or Node.js or Python for a program that runs it. Copy the result or download it. Differences from SQL, like $ne matching missing fields, are listed under it.
Examples
A filter, a sort and a limit become find()
SELECT name, email FROM users WHERE email LIKE '%@example.com' AND age BETWEEN 18 AND 30 ORDER BY name LIMIT 20
db.users.find({ email: /@example\.com$/, age: { $gte: 18, $lte: 30 } }, {
name: 1,
email: 1,
_id: 0
}).sort({ name: 1 }).limit(20)LIKE becomes a regular expression, anchored only where the pattern has no %; _id is left out because the query didn’t ask for it.
GROUP BY becomes aggregate()
SELECT status, COUNT(*) AS orders, SUM(total) AS revenue FROM orders GROUP BY status ORDER BY revenue DESC
db.orders.aggregate([
{
$group: { _id: '$status', orders: { $sum: 1 }, revenue: { $sum: '$total' } }
},
{ $project: { _id: 0, status: '$_id', orders: 1, revenue: 1 } },
{ $sort: { revenue: -1 } }
])The group key goes in _id and is renamed back in $project, so the documents have the same fields as the SQL rows.
What MongoDB can’t do the same way
SELECT * FROM orders o RIGHT JOIN customers c ON o.customer_id = c._id
RIGHT JOIN is not supported: MongoDB’s $lookup works from the left collection; swap the tables and use LEFT JOIN
What it converts
SELECT with DISTINCT, aliases and computed columns (arithmetic, ||, CASE, UPPER, SUBSTRING, COALESCE, CAST, YEAR …). WHERE with =, <>, <, >, IN, BETWEEN, LIKE and ILIKE, IS NULL, AND, OR and NOT, as a query filter wherever MongoDB has one, else $expr. INNER and LEFT JOIN … ON a.x = b.y as $lookup and $unwind; GROUP BY with COUNT, COUNT(DISTINCT), SUM, AVG, MIN, MAX; HAVING; ORDER BY, LIMIT, OFFSET and FETCH FIRST. INSERT … VALUES as insertOne or insertMany; UPDATE … SET as updateMany with $set, $inc and $mul (or an update pipeline when a value is computed from fields); DELETE as deleteMany.
What it refuses, and why
Sources: MongoDB: SQL to MongoDB mapping chart, MongoDB: SQL to aggregation mapping chart
Where MongoDB differs from SQL
{ field: null } matches a null value and a missing field; SQL’s IS NULL only the first. $ne and $nin also match documents where the field is null or missing; SQL’s <> and NOT IN don’t. Text comparison is case-sensitive unless the collection has a collation; ILIKE becomes a regular expression with the i option.
Questions
- Does it run my query?
No. It reads the SQL and writes the MongoDB query; nothing connects to a database. Run the result in mongosh, Compass or your program to see documents. - Which SQL dialect does it read?
The common part of PostgreSQL, MySQL, SQL Server and SQLite: double quotes, backticks and [brackets] around names, LIMIT, OFFSET and FETCH FIRST, || and CONCAT, ILIKE, ::type casts and -- and /* */ comments. - Is my query uploaded?
No. Everything runs in your browser, so your query stays on your device. Nothing you paste is uploaded unless you share a result, which is locked with your password first. The conversion runs in a worker in this tab.