How to query a JSON file with SQL

JSON is the format most API exports arrive in and the format least suited to reading directly. Converting it into a table is usually a scripting job — but the shapes that real exports use are predictable enough that the conversion can happen when the file is opened.

The shape that maps cleanly

An array of flat objects is already a table: each object is a row, each key a column. This is what most "export as JSON" buttons produce and it needs no preparation at all.

Keys that appear in only some objects still become columns — rows missing that key simply get a null, the same as a blank cell in a spreadsheet.

SELECT customer_id, plan, monthly_revenue
FROM subscriptions
WHERE monthly_revenue > 100
ORDER BY monthly_revenue DESC;

Wrapper objects from paginated APIs

API responses usually wrap the rows in an envelope alongside metadata — `{ "results": [...], "next": "..." }`. The rows are the array; the rest is bookkeeping.

NoEgress unwraps this automatically when the object holds exactly one array, so a saved API response loads as the table you wanted rather than as a single row of metadata.

What happens to nested values

A table cell has to hold a scalar, so nested objects and arrays are preserved as their JSON text rather than dropped. A `tags: ["a","b"]` field arrives as the string `["a","b"]`, which keeps the information visible and queryable instead of turning into an unreadable placeholder.

That means you can filter on nested content with ordinary string matching, which is often all an exploratory question needs.

SELECT id, tags
FROM events
WHERE tags LIKE '%urgent%';

Extracting a nested field properly

When string matching is not enough, DuckDB can parse the retained JSON text back into structure and pull a specific field out of it.

SELECT
  id,
  json_extract_string(meta, '$.source')  AS source,
  json_extract_string(meta, '$.campaign') AS campaign
FROM events
WHERE json_extract_string(meta, '$.source') = 'email';

Check what actually loaded

JSON has no schema, so the columns you get depend entirely on which keys appeared in the file. Profiling the result before querying it tells you which fields are present often enough to be worth grouping by, and which are sparse.

SELECT
  COUNT(*)                      AS rows,
  COUNT(campaign)               AS has_campaign,
  COUNT(DISTINCT source)        AS distinct_sources
FROM events;

Joining JSON to a spreadsheet

Because every opened file becomes a table in the same session, a JSON export from one system joins directly to a CSV or Excel extract from another — which is usually the actual task when someone asks how to query JSON.

SELECT
  e.id,
  e.source,
  c.company_name
FROM events e
JOIN customers c
  ON e.customer_id = c.customer_id;