Varan Join the beta →

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

On connect, Varan runs a small number of server-side aggregations that return the exact field set and type histogram for the collection — including nested paths — without transferring documents. It then draws an unbiased sample for the things that genuinely need examples, and fetches specific documents for any field the scan proved exists but the sample missed.

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 go back to MongoDB as a per-document patch keyed on _id — only the fields you actually changed. That matters: because the column list comes from what was discovered, replacing whole documents would erase anything not modelled. Patching cannot.

• Values are written back with their original BSON types, so a number stays a number.
• If someone else changed the same data while you worked, the write is refused — even when the row count is unchanged.
• Every change is snapshotted first, so it can be rolled back.

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 →