Get Started Free
Home Features SQL Mode for MongoDB

Query MongoDB Collections with SQL

SQL Mode is a translation layer for MongoDB. Write SELECT, WHERE, ORDER BY, GROUP BY and JOIN, and VisuaLeaf turns it into a native find() call or aggregation pipeline that runs on your MongoDB server. The MongoDB Query tab shows exactly what your SQL became. Connecting to PostgreSQL, MySQL or SQL Server instead? That is a different tool, the SQL Editor, covered further down this page.

VisuaLeaf SQL Mode running a GROUP BY query on a MongoDB payments collection, with results in a table and a MongoDB Query tab

SQL Syntax for MongoDB

Write SQL SELECT statements against your collections: WHERE, ORDER BY, LIMIT and OFFSET, DISTINCT, aliases, and aggregate functions such as COUNT, SUM, AVG, MIN and MAX. The editor autocompletes SQL keywords, collection names and field names, marks errors inline, and shows results in Tree, Table or BSON view.

SQL Mode editor querying a MongoDB collection with results in a table

MongoDB Translation View

Simple SELECTs become db.collection.find() calls; GROUP BY, JOIN and functions become aggregation pipelines. Switch to the MongoDB Query tab to read the generated filter or pipeline next to your SQL. It is a practical way to learn MongoDB syntax from queries you already know how to write.

MongoDB Query tab showing the aggregation pipeline generated from a SQL query

SQL Helper & Examples

Press F1 for the SQL Helper: tabs for basics, operators, functions, aggregation, JOINs, MongoDB types and advanced queries. Each example shows the SQL, the MongoDB operation it produces and a short explanation, with buttons to copy it or insert it straight into the editor.

SQL Mode only runs against MongoDB. If your data lives in a relational database, VisuaLeaf connects to it directly as a SQL client: try it as a MySQL GUI for Linux, a PostgreSQL GUI, a SQL Server GUI for Mac and Linux, a MariaDB GUI, or a SQLite GUI for Mac.

SQL Helper modal with examples and translations

What Your SQL Turns Into

VisuaLeaf parses the SQL you write and runs the MongoDB equivalent. These pairs come from the SQL Mode documentation.

Filter and projection

SELECT ... WHERE becomes find()

SELECT name, email FROM users WHERE age >= 18
db.users.find({ age: { $gte: 18 } }, { name: 1, email: 1 })

WHERE becomes the filter document, the column list becomes the projection, ORDER BY becomes the sort, and LIMIT and OFFSET map to limit and skip.

Grouping

GROUP BY becomes $group

SELECT status, COUNT(*) AS order_count FROM orders GROUP BY status
db.orders.aggregate([ { $group: { _id: "$status", count: { $sum: 1 } } } ])

Aggregate functions, GROUP BY and HAVING are translated into aggregation pipeline stages.

Joins

JOIN becomes $lookup

SELECT users.name, orders.total, orders.status FROM users INNER JOIN orders ON users._id = orders.user_id WHERE orders.status = 'pending'

INNER JOIN and LEFT JOIN are translated to $lookup stages, and you can chain several joins. Because $lookup can be expensive on large collections, index the join fields, for example orders.user_id.

What SQL Mode Supports

SQL Mode covers the read side of SQL, adapted to MongoDB's document model. Knowing the limits up front saves time.

Supported

  • SELECT with field lists, aliases and DISTINCT
  • WHERE with comparison operators, AND, OR, NOT, BETWEEN, IN, LIKE, IS NULL
  • ORDER BY on one or more fields, LIMIT and OFFSET
  • COUNT, SUM, AVG, MIN, MAX, FIRST, LAST, GROUP BY and HAVING
  • INNER JOIN and LEFT JOIN, including several joins in one query
  • String, math, date and conditional functions such as CONCAT, ROUND, DATE_SUB, COALESCE and CASE
  • MongoDB-specific array functions such as UNWIND and SIZE_OF_ARRAY
  • MongoDB types in literals: ObjectId, ISODate, NumberLong, NumberDecimal, UUID and regular expressions

Not supported

  • INSERT, UPDATE and DELETE (edit data in the collection view instead)
  • CREATE TABLE, ALTER TABLE and DROP TABLE
  • BEGIN, COMMIT and ROLLBACK
  • RIGHT JOIN and FULL OUTER JOIN
  • Subqueries in WHERE are only partly supported

Some MongoDB behavior also differs from a relational database: field names are case-sensitive, dotted names such as address.city reach into nested documents, and MongoDB treats a null field and a missing field differently.

SQL Mode or the SQL Editor?

VisuaLeaf has two places to write SQL, and they point at different databases.

MongoDB

SQL Mode

For querying MongoDB collections with SQL syntax. Your SQL is translated to find() or an aggregation pipeline and runs on MongoDB. Read-only, with the translation always visible in the MongoDB Query tab. Results can be turned into a chart with Create Chart or saved to the query library.

SQL Mode documentation

PostgreSQL, MySQL, SQL Server, SQLite...

SQL Editor: the SQL client

For relational databases. The SQL Editor runs native SQL in each database's own dialect, with autocomplete for tables and columns, multiple tabs, and F5 to run everything, F9 to run a selection or Ctrl/Cmd+F5 to run up to the cursor. Results open in Grid, Tree, JSON or Explain view, and a batch or stored procedure that returns several result sets shows each one in its own tab.

Pair it with the visual SQL query builder and ER diagrams. Read the SQL Editor documentation.

PostgreSQLMySQLMariaDBSQL ServerSQLiteOracle

How It Works

  1. Open SQL Query. Connect to MongoDB, then open the SQL Query activity from the toolbar or from a database in the sidebar.
  2. Write SQL. Use collection names in FROM, for example SELECT * FROM customers WHERE status = 'active'. Press F1 any time for the SQL Helper.
  3. Run. Press F5. Results appear in the Result tab in Tree, Table or BSON view.
  4. Check the translation. Open the MongoDB Query tab to see the find() call or pipeline that actually ran.
  5. Reuse it. Save the query to the library, reopen it from query history, or click Create Chart to visualize the result.

Frequently Asked Questions

Does SQL Mode connect to PostgreSQL, MySQL or SQL Server?
No. SQL Mode only queries MongoDB, by translating SQL into MongoDB operations. To work with a relational database, connect to it directly and use the SQL Editor, which runs native SQL on PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite and other SQL databases.
Which SQL statements does SQL Mode support?
SELECT queries with WHERE, ORDER BY, LIMIT, OFFSET, DISTINCT, aggregate functions, GROUP BY, HAVING, INNER JOIN and LEFT JOIN, plus string, math, date and conditional functions. INSERT, UPDATE, DELETE, DDL statements and transactions are not supported.
How are JOINs run on MongoDB?
Each JOIN is translated into a $lookup stage in an aggregation pipeline. INNER and LEFT joins are supported; RIGHT and FULL OUTER joins are not. Index the fields you join on, because $lookup can be slow on large collections.
Can I use ObjectId and dates in SQL Mode queries?
Yes. Write MongoDB types as quoted literals, for example WHERE _id = 'ObjectId("507f1f77bcf86cd799439011")' or WHERE created_at >= 'ISODate("2023-01-01T00:00:00.000Z")'. NumberLong, NumberDecimal, UUID and regular expressions work the same way.
Can I change data with SQL Mode?
No. SQL Mode is read-only. Edit, insert or delete documents in the collection view, or use the MongoDB shell.
View all FAQs

Related Features

Discover more powerful tools that work seamlessly together

VisuaLeaf visual query builder assembling a MongoDB filter from dropdowns

Visual Query Builder

Choose your preferred query style: SQL Mode for familiar SELECT syntax or the Visual Query Builder for MongoDB-native queries. Both produce the same results - pick what feels natural. Great for teams transitioning from SQL databases to MongoDB.
Aggregation pipeline builder with stage palette

Aggregation Pipeline

SQL Mode supports GROUP BY and aggregate functions, but for advanced transformations use the Aggregation Pipeline. Start with SQL for simple aggregates, then graduate to the pipeline builder for $unwind, $lookup, and complex multi-stage operations.

Want to Learn More?

Check out the documentation to explore all the details about SQL Mode and discover more VisuaLeaf features.

Read the Documentation

Ready to query MongoDB with the SQL you know?

Get started free with our Community Edition. Includes a 14-day trial of Pro features.

Start Free No credit card required Download
macOS Windows Linux