How to query MongoDB with SQL
MongoDB has no built-in SQL. Here are the real ways to query it with SQL, and a tested SQL-to-MongoDB translation table with the places results differ.
Quick Answer
MongoDB itself doesn't run SQL: not the server, and not mongosh. You have three routes:
- A tool that translates SQL for you, such as a GUI with a SQL mode, or MongoDB's SQL Interface on Atlas.
- Translate the SQL by hand into
find()or an aggregation pipeline:
// SELECT name, city FROM customers WHERE age > 30 ORDER BY name LIMIT 10
db.customers.find({ age: { $gt: 30 } }, { name: 1, city: 1, _id: 0 })
.sort({ name: 1 })
.limit(10)
- Copy the data into a SQL database when you need full SQL, such as reporting with complex joins.
Whichever you pick, a few SQL habits give different answers on MongoDB. The table below shows them.
Your options, honestly compared
| Option | Where it works | Good for | Watch out for |
|---|---|---|---|
| MongoDB SQL Interface | Atlas (MongoDB 5.0+) and self-managed Enterprise Advanced (6.0+), per MongoDB's docs | BI tools such as Tableau or Power BI over JDBC/ODBC | Not available for the free Community server. Read-only. |
| MongoDB BI Connector | Atlas and Enterprise | Existing BI dashboards | End of life after September 2026, per MongoDB. Don't start new work on it. |
| A GUI with a SQL mode | Any server, including Community | Ad-hoc queries and learning the MongoDB syntax | Each tool supports a different subset of SQL. Check joins, subqueries and writes. |
| Translate by hand | Anywhere | Application code, scripts, performance work | The semantic differences below |
| Sync to a SQL database | Anywhere | Heavy reporting, complex joins | A second system to keep in sync |
GUI tools with a SQL mode include VisuaLeaf (SQL Mode), Studio 3T (SQL Query) and DataGrip. They all translate a SELECT into find() or an aggregation pipeline and run that, so the semantic differences below apply to them too.
In VisuaLeaf
Run SQL against MongoDB in VisuaLeaf SQL Mode
SQL Mode translates a SELECT into a find() or an aggregation pipeline and runs it on your own server, with no connector, BI service or Atlas-only interface in between.
- Open SQL Mode from the toolbar, or right-click a database and choose Open SQL Mode.
- Write a query such as
SELECT status, COUNT(*) FROM orders GROUP BY statusand press F5. - Open the MongoDB Query tab to read the translation. Check it whenever a result surprises you: that is where SQL and MongoDB semantics part ways, as with fields that are missing rather than null.
- Press F1 for syntax help with examples and their MongoDB translations.
SQL Mode does not run INSERT, UPDATE or DELETE; edit data in the collection view instead. See SQL Mode.
SQL to MongoDB cheat sheet
Every MongoDB query in this table was run on MongoDB 7.0 against a small customers / orders dataset.
| SQL | MongoDB |
|---|---|
SELECT name FROM customers WHERE city = 'Lyon' | db.customers.find({ city: 'Lyon' }, { name: 1, _id: 0 }) |
WHERE age > 30 AND age <= 40 | { age: { $gt: 30, $lte: 40 } } |
WHERE age BETWEEN 30 AND 40 | { age: { $gte: 30, $lte: 40 } } |
WHERE city IN ('Lyon', 'Paris') | { city: { $in: ['Lyon', 'Paris'] } } |
WHERE name LIKE 'Al%' | { name: /^Al/ } (case-sensitive; /^al/i to ignore case) |
WHERE email IS NULL | { email: null } (also matches documents with no email field) |
WHERE email IS NOT NULL | { email: { $ne: null } } |
ORDER BY name DESC LIMIT 10 OFFSET 20 | .sort({ name: -1 }).skip(20).limit(10) |
SELECT DISTINCT city FROM customers | db.customers.distinct('city') |
SELECT COUNT(*) FROM customers | db.customers.countDocuments({}) |
SELECT city, COUNT(*) FROM customers GROUP BY city | aggregate([{ $group: { _id: '$city', n: { $sum: 1 } } }]) |
... GROUP BY city HAVING COUNT(*) > 1 | Add { $match: { n: { $gt: 1 } } } after the $group |
SELECT SUM(total) FROM orders | aggregate([{ $group: { _id: null, s: { $sum: '$total' } } }]) |
orders LEFT JOIN customers ON orders.cust = customers._id | { $lookup: { from: 'customers', localField: 'cust', foreignField: '_id', as: 'c' } } |
orders INNER JOIN customers ... | The same $lookup, then { $unwind: '$c' } |
For more on the pieces: GROUP BY with $group, joins with $lookup, LIKE queries and null vs missing fields.
What we found testing this
We loaded four customers (one with email: null, two with no email field, and one whose age is the string "39") and four orders (one with total: "15" as a string, one pointing at a customer that doesn't exist). Then we compared what SQL would return with what MongoDB returned.
- The obvious
COUNT(email)translation is wrong. In a pipeline,{ $sum: { $cond: [{ $ne: ['$email', null] }, 1, 0] } }returned 3, because a missing field isn't equal tonullinside an expression. SQL'sCOUNT(email)would return 1.{ $gt: ['$email', null] }returned the correct 1. SUMsilently skips strings. The four order totals were 50, 20,"15"and 5.$sumreturned 75 with no error. SQL would return 90 if the column were numeric, or refuse to run. Bad data types don't fail loudly in MongoDB; check with{ total: { $not: { $type: 'number' } } }.- Comparisons don't cross types. The customer whose age was
"39"was left out of bothage > 30andBETWEEN 30 AND 40. In a sort it came after every number, as28, 34, 41, "39". - String matching is case-sensitive.
city = 'paris'matched onlyparis, notParis, anddistinctreturned both as separate values. GROUP BY split them the same way. MySQL's default collation would treat them as one. A collation with{ locale: 'en', strength: 2 }matched both. - NULLs sort first. Sorting by email ascending put
nulland the two missing values beforea@x.io. PostgreSQL puts NULLs last in an ascending sort.
| SQL | SQL result | MongoDB result |
|---|---|---|
WHERE email IS NULL | 3 rows | 3 ({ email: null } matches null and missing) |
COUNT(email) | 1 | 3 with $ne, 1 with $gt |
SUM(total) | 90 (or an error) | 75, the string is skipped |
WHERE age > 30 | Includes 39 if the column is numeric | Skips "39" |
WHERE city = 'paris' | 1 or 2 rows, depending on collation | 1 |
LEFT JOIN with no match | One row with NULL columns | One document with an empty array |
That last row matters too: $lookup is already a LEFT JOIN. The order for a customer that doesn't exist came back with c: [], and adding $unwind removed it, which is what turns it into an INNER JOIN.
When SQL on MongoDB is the wrong tool
- Writes. SQL modes in GUI tools and MongoDB's SQL Interface are read-only. INSERT, UPDATE and DELETE need MongoDB's own commands.
- Many joins. Each
$lookupruns a query against the other collection for every input document, and needs an index on theforeignFieldto be fast. A query with several joins is a sign the data might belong embedded in one document, or in a relational database. - Arrays and nested documents. SQL has no natural way to say "an element of this array matches". Translators add their own functions (such as UNWIND), which aren't portable between tools.
Frequently Asked Questions
Can you use SQL in MongoDB?
Not natively. The server and mongosh only understand MongoDB queries. You can use MongoDB's SQL Interface on Atlas or Enterprise, a GUI tool that translates SQL, or translate the query by hand.
Is the MongoDB BI Connector still supported?
MongoDB lists the BI Connector as reaching end of life after September 2026 and recommends the MongoDB SQL Interface instead.
How do I write a SQL JOIN in MongoDB?
Use the $lookup aggregation stage. On its own it behaves like a LEFT JOIN, returning matches as an array. Add $unwind after it to get INNER JOIN behavior.
What is the MongoDB equivalent of WHERE column IS NULL?
{ field: null }, which matches documents where the field is null or missing. Use { field: { $type: "null" } } to match only an explicit null.
Does SQL on MongoDB use indexes?
Yes, if the translated query can. The SQL becomes a normal find() or aggregation, so the same indexing rules apply. Check it with explain().