aran Download Varan →
Varan › How to query MongoDB with SQL

How to query MongoDB with SQL

Five minutes, no pipeline, no warehouse. Connect a collection, let the schema be counted rather than guessed, then write ordinary SQL — including JOINs to a relational database.

1. Connect the collection

Add a source, pick MongoDB, and give it a host, port and database. A user is optional — local instances usually run without auth.

2. Let the schema be discovered

Varan reads every document and counts the field set, the type histogram, presence and array element types — including nested paths. It is a census, not a sample, so nothing rare is invisible.

The counting happens on your machine: Varan walks the raw BSON locally rather than asking mongod to parse and aggregate. Measured with MongoDB's own profiler on 192,387 documents, that took the server's cost for a full census from 16,217 ms of CPU down to 195 ms — 83× less — for a byte-identical result across all 950 field paths. The collection is read 1.01× instead of 5.13×, and the read cursor is held open for a sixth as long.

The sidebar labels each collection exact or sampled next to its column count, so an incomplete schema can never be mistaken for a complete one. Connecting reads nothing: a collection is counted the first time a query needs it, and that same read fills the query’s copy.

Each collection becomes a table. Nested fields become dotted columns:

orders
  _id            VARCHAR
  status         VARCHAR      -- enum: new, paid, shipped
  amount         VARCHAR      -- int x4850, string x150 -> needs a decision
  addr.city      VARCHAR
  addr.geo.lat   VARCHAR
  tags           VARCHAR[]    -- native list: unnest(tags) works
  items          VARCHAR      -- array of documents, held as JSON

3. Answer anything genuinely ambiguous

Open schema mapping on the collection. It lists only what could not be settled by counting — typically a handful of fields — sorted so the least certain is first, each showing the evidence:

amount   type → string   destructive

across the whole collection: int ×4850, string ×150 — 3.0% minority, held as text until you decide

Pick the right answer and it is remembered. Re-scan later and your decision survives; only genuinely new fields come back as questions.

Decisions are tiered by what being wrong would cost. Cosmetic ones apply silently when the evidence is overwhelming. Anything that would change the shape of the table, or change what gets written back to your database, always asks.

4. Write SQL — across sources

SELECT c.name,
       c."addr.city"          AS city,
       COUNT(o.id)            AS orders,
       SUM(o.total::DECIMAL)  AS revenue
FROM   mongo_customers c                          -- MongoDB
JOIN   postgres_orders  o ON o.customer_id = c._id  -- PostgreSQL
GROUP BY 1, 2
ORDER BY revenue DESC;

Arrays behave like arrays:

SELECT tag, COUNT(*)
FROM   (SELECT unnest(tags) AS tag FROM mongo_orders)
GROUP BY 1 ORDER BY 2 DESC;

5. Editing, and writing back

A statement on a MongoDB table writes to the collection as it runs, the same way every other database is written — there is nothing to apply afterwards. To see a write before it runs, put PLAN in front of it:

PLAN UPDATE mongo_orders SET status = 'shipped' WHERE paid;

The plan lists every document that would change, old value beside new, and a mongosh script that makes exactly that change or nothing — for whoever has write access. Without PLAN, the statement is written:

A standalone server has no transactions: Varan records the documents, writes, and if the write fails part way puts back what it wrote — as close to all-or-nothing as the server allows. Relational sources — MySQL, MariaDB, PostgreSQL, SQL Server, Oracle and SQLite — are written the same way: only the rows that changed, each guarded, in one transaction.

What this does not do

It is worth being straight about the boundaries:

More detail

Why schema inference is hard, and what is actually solvable · The measured results

Get the beta →