How to query a JSON file with SQL

Most API exports come as JSON, and JSON is miserable to read by eye. Turning it into a table usually means writing a script. Real exports come in a few predictable shapes, though, so the conversion can happen the moment you open the file.

The shape that just works

An array of flat objects is already a table. Each object is a row and each key is a column. That’s what most "Export as JSON" buttons produce, and it needs no prep.

If a key only shows up in some objects, it still becomes a column. Rows without it get a null, like 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 APIs

API responses usually wrap the rows in an envelope with some metadata, like `{ "results": [...], "next": "..." }`. The array is your data. The rest is paging info.

When the object holds exactly one array, NoEgress unwraps it for you. A saved API response loads as the table you wanted, not as one row of metadata.

Nested values

A table cell holds a single value, so nested objects and arrays are kept as JSON text. A field like `tags: ["a","b"]` arrives as the string `["a","b"]`. You can still see it and query it.

For a quick question, plain string matching is often all you need.

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

Pull out a nested field

When string matching isn’t enough, DuckDB can parse that JSON text and extract the field you want.

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. The columns you get depend on which keys appear in the file. Profile the table before you query it, so you know which fields are filled in often enough to group by and which are mostly empty.

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

Join JSON to a spreadsheet

Every file you open lands in the same session. A JSON export from one system joins straight to a CSV or Excel file from another. That’s usually the real job behind "how do I query this JSON?"

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