How to query a JSON file with SQL
Synth loads a JSON file directly into a real SQLite database running in your browser tab — no import script, no MongoDB, nothing to install. Upload a JSON array or a newline-delimited JSON file and start writing SQL against it immediately, or just ask in plain English if you'd rather skip SQL.
The two JSON shapes Synth understands
Real JSON data comes in two shapes in practice — an API response, and a log or export dump — and Synth reads both directly:
A single array of objects
[
{ "id": 1, "customer": "Acme Corp", "total": 482.50 },
{ "id": 2, "customer": "Globex Inc", "total": 129.00 }
]Newline-delimited JSON (NDJSON)
One JSON object per line — the shape most API export tools, log pipelines, and streaming dumps actually use:
{ "id": 1, "customer": "Acme Corp", "total": 482.50 }
{ "id": 2, "customer": "Globex Inc", "total": 129.00 }Both load into the same kind of table. Synth checks whether the file parses as one JSON document first, and falls back to reading it line-by-line as NDJSON if it doesn't — you don't have to tell it which shape you're uploading.
What happens to nested objects and arrays
JSON gets to nest data in ways a flat table can't represent directly, so Synth makes an explicit, consistent choice for each case:
- Nested objects flatten into dot-notation columns. A record like
{"address": {"city": "NYC", "zip": "10001"}}becomes two columns,address.cityandaddress.zip— queryable directly, no unpacking required. - Arrays store as JSON text in a single column. A record like
{"tags": ["urgent", "billing"]}keepstagsas one column holding the text["urgent","billing"]. Turning it into extra rows or columns would explode the schema for no benefit — instead, pull values out of it with SQLite's own JSON functions:
SELECT id, json_extract(tags, '$[0]') AS first_tag
FROM orders
WHERE tags != '';Where JSON like this actually comes from
This isn't a hypothetical format choice — it's the shape real exports already arrive in:
- REST API responses (most return a JSON array of records)
- Log aggregation and event-stream exports (almost always NDJSON, one event per line)
- MongoDB or Firestore collection exports
- Webhook payload dumps saved for later analysis
Normally, analyzing any of these means writing a script to flatten and load them into a database first. Synth skips that step entirely.
How it works
- Upload a
.json,.ndjson, or.jsonlfile — click to browse. No account required. - It becomes a real SQLite database, instantly, running in your browser tab.
- Write SQL yourself, or ask the AI assistant in plain English — then sort, filter, and export your results.
Frequently asked questions
Can I query a JSON file with SQL?
Yes. Synth loads a JSON array of objects, or a newline-delimited JSON (NDJSON) file, directly into a real SQLite database that runs in your browser tab, so you can write standard SQL against it immediately.
What JSON shapes does Synth support?
Two shapes: a single JSON array of objects (the common shape for API responses), and newline-delimited JSON, one object per line, which is how most log exports and streaming data dumps are formatted. A single bare JSON object is also accepted as a one-row table.
What happens to nested objects in my JSON?
Nested objects flatten into columns using dot notation — a record like {"address": {"city": "NYC"}} becomes a column named address.city. This keeps every value queryable with plain SQL instead of hiding it inside a JSON blob.
What happens to arrays inside my JSON records?
An array value is stored as its JSON text in a single column, rather than being split into extra columns or rows. You can pull values out of it with SQLite's own json_extract() and json_each() functions in your SQL.
Do I need MongoDB or a NoSQL database to query JSON?
No. Synth loads the JSON directly into a real SQL database (SQLite) running in your browser tab, so you get standard SQL — joins, GROUP BY, window functions — without installing or hosting any database at all.