Query JSON with DuckDB: SQL Analytics on JSON Files

SQL over a JSON file, with nothing to set up

For years, "analyze this JSON file" meant writing a script, looping, and accumulating totals by hand — or spinning up a database and importing first. DuckDB collapses that to a single query. It's an in-process analytical database (think "SQLite for analytics") that reads JSON, NDJSON, CSV, and Parquet directly from disk or a URL, no server and no import step. By 2026 it's a staple of the data-engineering toolkit precisely because the friction is near zero.

If you've been reaching for jq or ad-hoc scripts to slice JSON, DuckDB gives you the full power of SQL over the same files.

The one-liner: read_json

Point DuckDB at a file and select from it as if it were a table:

SELECT * FROM read_json('events.json');

DuckDB inspects the file and infers the schema and types automatically — it detects columns, numbers, booleans, dates, and nested structures without you declaring anything. For newline-delimited files it's the same call; DuckDB detects the format, and you can be explicit with read_ndjson('events.ndjson'). You can even skip the function entirely for a local file:

SELECT count(*), avg(amount) FROM 'events.json';

Reaching into nested JSON with arrow operators

Real JSON is nested, and DuckDB navigates it with arrow operators. Given records like:

{ "id": 1, "user": { "name": "Ada", "country": "US" }, "amount": 42.50 }

use -> to get a JSON value and ->> to extract it as text:

SELECT
  user ->> 'name'    AS name,
  user ->> 'country' AS country,
  amount
FROM 'events.json'
WHERE user ->> 'country' = 'US';

-> keeps the result as JSON (good for further navigation), while ->> returns a plain string you can compare and group. This is the same access model as Postgres JSONB, so the mental model transfers.

Flattening arrays with UNNEST

The operation that's painful in scripts — exploding an array into rows — is one keyword in DuckDB. Given:

{ "order": 100, "items": ["book", "pen", "mug"] }

UNNEST turns each array element into its own row:

SELECT order, UNNEST(items) AS item
FROM 'orders.json';
-- 100, book
-- 100, pen
-- 100, mug

From there you can GROUP BY item to count occurrences across every order — a one-line answer to "what are the most common items?" that would be a nested loop in application code. This is the SQL equivalent of a JSON flatten.

Querying many files at once

DuckDB accepts glob patterns, so a whole directory of daily exports becomes one table:

SELECT count(*)
FROM read_json('logs/2026-07-*.ndjson');

If the files share a key, you can join across them just like ordinary tables. This turns "I have 300 JSON log files" from a scripting chore into a single aggregation.

From analysis to output

Because it's SQL, the result of any query is itself a table you can transform, export, or convert. Compute an aggregate and write it straight to CSV or Parquet:

COPY (
  SELECT user ->> 'country' AS country, sum(amount) AS revenue
  FROM 'events.json'
  GROUP BY country
  ORDER BY revenue DESC
) TO 'by_country.csv' (HEADER);

For very large inputs, DuckDB streams and processes data in chunks rather than loading everything into memory — complementing the streaming-parser techniques you'd otherwise write by hand, and it uses Apache Arrow columns internally for speed.

When to use DuckDB vs a JSON tool

• Reach for DuckDB when you want to *aggregate, filter, join, or group* JSON data — analytical questions over many records or files.
• Reach for a JSON tool when you want to *inspect, reshape, or validate* a single document — format it, test a JSONPath, or convert JSON to CSV for a quick look.

They're complementary: use the JSON toolkit to understand a payload's shape, then DuckDB to run analytics across thousands of them. For the database angle see JSON in databases and Postgres JSONB.