Can you query MongoDB with SQL?

Yes, but not directly — MongoDB has no SQL engine. Tools work out a relational shape for a collection and then let you query that. The hard part is the shape: a MongoDB collection has no declared schema, documents may differ from one another, and values nest and contain arrays. Varan copies the collection into a local DuckDB engine and queries it with ordinary SQL, including JOINs to Postgres, MySQL, SQL Server, Oracle, SQLite and CSV files.

Why does sampling a MongoDB collection lose data?

Most tools read the first few hundred documents and treat that as the schema. Any field introduced later, or used rarely, is simply absent from the sample — so it never becomes a column, and its data is invisible with no error shown. Measured against an exhaustive scan of 25,000 real GitHub events, sampling the first 500 documents missed 154 of 346 rare fields.

What is exact schema discovery?

Instead of estimating the schema from a sample, the field names, type distribution, how often each field appears, and the element types inside arrays are counted across the whole collection using server-side aggregations. No documents are transferred to do it. The result is not a better guess — it is a count.

Can schema inference for MongoDB ever be perfect?

No, and any tool claiming otherwise is overselling. Discovery — what fields exist and what types they hold — can be exact. Interpretation cannot: if a field is a number in most documents and a string in a few, no amount of scanning reveals whether that is a bug, a deliberate union type, or a half-finished migration. That information is not in the data. Varan surfaces those cases, ranks them by uncertainty, and asks once.

Does this use AI or machine learning?

No. There is no model, no training and no data leaving your machine. Schema discovery is server-side aggregation (counting) plus classical statistics — Wilson and Clopper-Pearson confidence intervals — for the parts that are genuinely sampled. Every decision shows the counts behind it, so you can verify any of them by hand.

Varan Join the beta →

Varan › Querying MongoDB with SQL

Querying MongoDB with SQL

A MongoDB collection has no schema. To query it with SQL, something has to work one out — and almost every tool does that by reading a sample and guessing. This page explains what can be recovered exactly, what cannot, and why the difference matters more than the accuracy number.

The problem underneath

SQL requires one schema per table. MongoDB permits one schema per document. That gap is the object-relational impedance mismatch, and it is genuinely old — people have been writing about it since the 1980s.

It is usually discussed as one problem. It is actually three, and they have very different answers:

Representation — can we hold a document without losing anything? Yes. A BSON document is a tree; DuckDB has STRUCT, LIST and JSON. The mapping is total.
Discovery — do we know what is actually in the collection? Yes, exactly — see below.
Interpretation — what should a field become? Not solvable. The information required is not in the data.

Why sampling loses data silently

The common approach is to read the first few hundred documents and treat their fields as the schema. Two things go wrong:

Documents come back in insertion order, so the sample is biased toward whatever was written first — often the oldest generation of your app.
A field used by 1% of documents is likely to be absent from any small sample. It never becomes a column, and nothing tells you it existed.

That last failure is the dangerous one: data that exists but is invisible, with no error. Measured against an exhaustive scan of 25,000 real GitHub events, sampling the first 500 documents missed 154 of 346 rare fields.

Counting instead of guessing

Varan asks the server for the answer rather than estimating it. One aggregation per nesting level returns the exact field set and an exact type histogram, without transferring documents:

// exact field names and type counts, computed server-side
[
  { $project: { kv: { $objectToArray: "$$ROOT" } } },
  { $unwind: "$kv" },
  { $group: { _id: { k: "$kv.k", t: { $type: "$kv.v" } }, n: { $sum: 1 } } }
]

From that you get, for the whole collection and not a sample:

every field path, including nested ones like customer.addr.geo.lat
the true type distribution — e.g. int ×4850, string ×150
how many documents carry each field, so optional vs required is a count, not an estimate
the element types inside every array, which decides whether it can become a real SQL list

Anything the scan proves exists but the sample happened to miss is then fetched directly. In a test collection of 2,000 documents with a 50-document sample, a field present in one document was reported at exactly 0.05%.

What this makes possible in SQL

Once a collection lands as a table, it is just a table:

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

Nested fields are ordinary columns — addr.city needs no special syntax.
Arrays of scalars become native lists, so unnest(tags) works.
Arrays of documents stay JSON until you choose otherwise, because turning them into rows changes what a query returns.

The part that cannot be solved

If a field is an integer in 4,850 documents and a string in 150, what should it be?

Nobody can answer that from the data. It could be a bug, a deliberate union type, or a migration that never finished. Reading more documents makes the estimate sharper and the decision no easier — the information lives in the head of whoever wrote the writer, not in the collection.

So Varan does not pretend. Fields like that are held as text, marked as needing a decision, ranked so the least certain come first, and shown with the counts behind them. You answer once; the answer is stored and survives every later re-scan.

On 25,000 real GitHub events — a genuinely polymorphic collection where push, pull-request and issue events share nothing — that came to 17 fields out of 909.

No model, nothing leaves your machine

There is no machine learning here. No training, no weights, no API call. Discovery is counting; the remaining uncertainty is quantified with Wilson and Clopper–Pearson intervals, both of which are ordinary textbook statistics. Every decision displays the counts it was based on, so you can check any of them yourself.

That also means it works offline, costs nothing per query, and cannot hallucinate a column that does not exist.

Try it on your own collection →