Get Started Free

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.

Updated November 12, 2026 ยท Tested on MongoDB 7.0.45, mongosh 2.9.2

Quick Answer

MongoDB itself doesn't run SQL: not the server, and not mongosh. You have three routes:

  1. A tool that translates SQL for you, such as a GUI with a SQL mode, or MongoDB's SQL Interface on Atlas.
  2. 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)
  1. 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

OptionWhere it worksGood forWatch out for
MongoDB SQL InterfaceAtlas (MongoDB 5.0+) and self-managed Enterprise Advanced (6.0+), per MongoDB's docsBI tools such as Tableau or Power BI over JDBC/ODBCNot available for the free Community server. Read-only.
MongoDB BI ConnectorAtlas and EnterpriseExisting BI dashboardsEnd of life after September 2026, per MongoDB. Don't start new work on it.
A GUI with a SQL modeAny server, including CommunityAd-hoc queries and learning the MongoDB syntaxEach tool supports a different subset of SQL. Check joins, subqueries and writes.
Translate by handAnywhereApplication code, scripts, performance workThe semantic differences below
Sync to a SQL databaseAnywhereHeavy reporting, complex joinsA 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.

  1. Open SQL Mode from the toolbar, or right-click a database and choose Open SQL Mode.
  2. Write a query such as SELECT status, COUNT(*) FROM orders GROUP BY status and press F5.
  3. 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.
  4. 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.

SQLMongoDB
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 customersdb.customers.distinct('city')
SELECT COUNT(*) FROM customersdb.customers.countDocuments({})
SELECT city, COUNT(*) FROM customers GROUP BY cityaggregate([{ $group: { _id: '$city', n: { $sum: 1 } } }])
... GROUP BY city HAVING COUNT(*) > 1Add { $match: { n: { $gt: 1 } } } after the $group
SELECT SUM(total) FROM ordersaggregate([{ $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 to null inside an expression. SQL's COUNT(email) would return 1. { $gt: ['$email', null] } returned the correct 1.
  • SUM silently skips strings. The four order totals were 50, 20, "15" and 5. $sum returned 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 both age > 30 and BETWEEN 30 AND 40. In a sort it came after every number, as 28, 34, 41, "39".
  • String matching is case-sensitive. city = 'paris' matched only paris, not Paris, and distinct returned 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 null and the two missing values before a@x.io. PostgreSQL puts NULLs last in an ascending sort.
SQLSQL resultMongoDB result
WHERE email IS NULL3 rows3 ({ email: null } matches null and missing)
COUNT(email)13 with $ne, 1 with $gt
SUM(total)90 (or an error)75, the string is skipped
WHERE age > 30Includes 39 if the column is numericSkips "39"
WHERE city = 'paris'1 or 2 rows, depending on collation1
LEFT JOIN with no matchOne row with NULL columnsOne 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 $lookup runs a query against the other collection for every input document, and needs an index on the foreignField to 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().

VisuaLeaf

Write SQL against MongoDB and see the query it becomes

VisuaLeaf's SQL Mode runs SELECT, WHERE, GROUP BY and JOIN against your collections and shows the find() or aggregation pipeline next to your SQL, so you learn MongoDB's syntax as you go.