Oxys DB Studio

Query MongoDB with SQL

Write SELECT statements against MongoDB collections: what SQL Query supports, how it translates to find and aggregate, and what it refuses.

SQL Query lets you write SELECT statements against MongoDB collections. Each statement is translated, literally and in the order you wrote it, into a MongoDB find or aggregation pipeline, then run. You can see the translation, explain it, or move it to the Aggregation Editor.

SQL Query only reads. It never changes data.

A SQL Query tab grouping the orders of acme_store by status

Run a SQL statement

  1. Select a database or a collection in the connection tree.

  2. Press Shift+Ctrl+L, click SQL in the toolbar, or right-click and choose Open SQL. Opened on a collection, the editor holds select * from <collection>;.

  3. Write your statement, for example:

    select status, count(*) as orders, sum(total) as revenue
from orders
group by status
order by revenue desc;

  4. Put the cursor in the statement and press F5 (Run statement).

Only the statement at the cursor runs. Separate statements with ;; with the cursor between two statements, the one before it runs. The result opens in the Result sub-tab with the usual Table, Tree and JSON views and paging.

What you can write

SQLBecomes
SELECT * FROM c with WHERE, ORDER BY, LIMIT n [OFFSET m] or LIMIT m, nfind() with a filter, sort, skip, limit
SELECT a, b AS x FROM cA projection; _id is left out unless you select it
Plain, dotted (address.city) or quoted field names (`name` or [name])Field paths; array positions need brackets: [items.0.sku]
=, <>, <, <=, >, >=, AND, OR, NOT, parenthesesQuery operators
LIKE, NOT LIKE with % and _A case-insensitive regular expression
IN, NOT IN, BETWEEN, NOT BETWEEN, IS NULL, IS NOT NULL, true, false$in, $nin, ranges, null tests
Comparing two fields (WHERE a.qty > a.min)$expr
A JSON document in WHERE (WHERE { status: "pending" }, WHERE total > { … })Used as written
MongoDB constructors: ObjectId(…), NumberInt, NumberLong, NumberDecimal, ISODate, UUID, Timestamp, BinData and othersThe typed value
date('2026-01-05'), with or without a time and zoneA date (no zone means UTC)
SELECT DISTINCT$group then $project
GROUP BY, HAVING, COUNT(*), COUNT(f), SUM, AVG, MIN, MAX$group, $match, $sort, $skip, $limit, $project
[INNER] JOIN … ON a.x = b.y, LEFT [OUTER] JOIN, CROSS JOIN, FROM a, b$lookup and $unwind; each collection’s document nests under its alias

Rules to keep in mind:

  • With joins, qualify every field (o.total, c.name); collA.* is refused; a self-join needs aliases.
  • With GROUP BY, every selected field must be grouped or aggregated. Output fields are named after the alias, or after the function (COUNT(*)).
  • With DISTINCT, ORDER BY may only use selected fields.

What is not supported

These are refused with a message naming the construct and its position:

Not supportedInstead
INSERT, UPDATE, DELETE, DROP and anything but SELECTEdit documents in the result, or use IntelliShell
UNION, INTERSECT, EXCEPTRun the statements separately
Subqueries (in FROM, IN (SELECT …), EXISTS)The Aggregation Editor ($lookup with a pipeline)
WITH (common table expressions)The Aggregation Editor
Window functions, DISTINCT ON, COUNT(DISTINCT …)The Aggregation Editor
Arithmetic in values (total * 2)The Aggregation Editor
USING and NATURAL joins, RIGHT and FULL joinsRewrite with ON and INNER or LEFT JOIN
Other SQL functionsThe Aggregation Editor

Limits: 1 MiB of SQL text, 64 levels of nesting, 10,000 values or conditions.

The SQL Query toolbar

ButtonShortcutWhat it does
Run statementF5Translates and runs the statement at the cursor
Explain statementOpens Visual Explain on the translation; Run full explain is safe, as translations never write
Open in Aggregation EditorOpens the translation’s stages in a new Aggregation Editor tab
Load queryCtrl+OOpens a .sql file
Save queryCtrl+SSaves the SQL to the Query Manager
Save as queryShift+Ctrl+SWrites a .sql file (Save SQL Query as…)
Query historyEvery SQL statement you ran, newest first; Load puts one back in the editor
SQL Query Auto-suggestCtrl+Space opens suggestionsSuggestions as you type, on or off

The Query Code sub-tab shows the translation of the statement at the cursor in every Query Code language.

Editing the result

A plain SELECT * FROM c (or a select that keeps _id and every field under its own name) gives an editable result, like a Collection Tab. GROUP BY, DISTINCT, aggregate functions, joins and aliases give a read-only result, since those rows are not documents of the collection. On a read-only connection results are always read-only.

Troubleshooting

MessageWhat to do
SQL Query runs SELECT statements onlyOnly SELECT runs. To change data, edit the result documents or use IntelliShell.
… is not supported by SQL QuerySee the table above for an alternative.
An error with a line and columnA syntax error; the position is in the whole editor text.
A field neither grouped nor aggregatedAdd it to GROUP BY or wrap it in an aggregate function.
No rows, but you expected someLIKE and = compare types: '5' does not match the number 5. Dates need date('…') or ISODate(…).