aran Download Varan →

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.

• Credentials stay on your machine. Nothing is sent anywhere.
• Atlas and other hosted providers work over TLS with the SSL toggle.

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. You can also ask per query: prefix it with EXACT to force a census, or FAST to skip one.

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:

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

Edits to a MongoDB table stay local until you say otherwise. Fixing a collection is iterative — you correct a column, look at it, correct it again — so pushing every intermediate statement would be a trap. When you are ready:

APPLY mongo_orders;

The write goes back as a per-document patch keyed on _id — only the fields you actually changed, as $set on individual paths. That matters: because the column list comes from what was discovered, replacing whole documents would erase anything not modelled. Patching cannot.

• Values go back with their original BSON types, taken from the census — a number stays a number.
• If a path holds more than one type, Varan refuses rather than guesses, shows the counts, and gives you the exact clause that resolves it: APPLY mongo_orders TYPES (amount AS STRING). Across 492,000 real documents, exactly one column needed a human answer.
• Add DRY RUN to see precisely what would be written without writing it.
• If someone changed the collection while you worked, Varan compares three states — as loaded, your edits, and live — and still writes only your changes. Where both moved, yours wins and the displacement is reported rather than silently dropped.
• Every change is snapshotted first, so it can be rolled back.

Relational sources behave differently on purpose: their schemas are declared, so their types are not in question. An UPDATE against a MySQL, MariaDB or PostgreSQL table writes straight through as it runs. SQL Server, Oracle and SQLite do not have a write-back path yet — there, Varan changes the local copy and says so explicitly, naming the table it did not write to.

What this does not do

It is worth being straight about the boundaries:

• It is not streaming change capture. Drift is detected by comparing against the source when you ask, not by tailing the oplog.
• Arrays of documents are not turned into child tables automatically — that changes row counts, so it is your call.
• Exact scans descend six levels; deeper structures fall back to sampling, and the UI says so.

Get the beta →