Query JSON files with SQL

Drop a JSON, NDJSON or gzipped log file into DuckLocal and it becomes a table. Nested objects turn into columns you can filter, unnest and join with CSV and Parquet. There's no jq pipeline to write and no script to maintain.

Free and open source for macOS, Windows and Linux. Files are read in place and never uploaded.

From a JSON file to a result

The file stays where it is. DuckDB reads it in place each time you query.

  1. Drop the file in

    events.ndjson becomes a view called events, and logs.json.gz one called logs. DuckDB works out whether the file is an array, one object or one object per line.

  2. See the shape

    The schema sidebar shows nested objects as STRUCT columns and arrays as LIST columns, with their fields. The Overview tab profiles each column of a result.

  3. Query it like a table

    Filter on a nested field with dot notation, expand arrays with unnest, and join the result with any other file you have open.

Nested fields without the boilerplate

An order with an array of line items becomes one row per order with an items list. unnest turns that list into one row per item, and each item's fields are a dot away.

For data whose shape varies from record to record, json_extract_string(col, '$.a.b[0].c') walks a path and returns text, whatever is or isn't there.

JSONL and nested JSON · DuckDB's JSON reference

-- orders.ndjson: one order per line,
-- each with an items array
WITH lines AS (
  SELECT order_id, unnest(items) AS item
  FROM orders
)
SELECT item.sku,
       sum(item.qty) AS units
FROM lines
GROUP BY item.sku
ORDER BY units DESC;

Logs, exports and API dumps

Point DuckLocal at a folder or a pattern such as logs/2026-*/*.json.gz and every match opens as data. Gzipped files need no unpacking.

Some tools wrap their records in one object, like CloudTrail's {"Records": [...]}. ducklocal schema spots that shape and its hint gives the SQL that reads the records as rows.

Folders and patterns · Audit CloudTrail logs locally

  • JSON arrays, single objects and JSON Lines
  • Gzipped files read directly
  • Folders and glob patterns
  • Joins with CSV, Parquet and Excel data
  • Results exported to CSV or Parquet

An NDJSON viewer in Finder

On a Mac, select a .jsonl or .ndjson file and press Space. DuckLocal's Quick Look preview shows its columns and first 50 rows as a table instead of a wall of text. The preview never writes, never scans the whole file and downloads nothing.

Quick Look previews

JSON from the command line

The same reading works headless. ducklocal schema shows a JSON file's columns, including nested ones, and --stats profiles them. ducklocal query runs one statement and prints typed JSON, so an AI agent can query logs without guessing field names.

Read the CLI guide · SQL for AI agents

Terminal
$ ducklocal schema events.jsonl
$ ducklocal query --sql \
  "SELECT json_extract_string(event,
            '$.payload.items[0].sku') AS sku,
          count(*) AS n
   FROM read_ndjson_objects('events.jsonl')
   GROUP BY 1 ORDER BY n DESC"

JSON and SQL questions

Which JSON formats does DuckLocal open?
JSON documents (.json), newline-delimited JSON (.ndjson, .jsonl) and gzipped versions of them such as logs.json.gz. Each file becomes a view named after it.
How are nested objects handled?
DuckDB infers nested objects as STRUCT columns and arrays as LIST columns. Reach into them with dot notation, expand lists with unnest, or use json_extract_string with a path for irregular data.
What if the records are wrapped in an array, like CloudTrail logs?
ducklocal schema notices a file that is one object wrapping a list of records and its hint gives the SQL that reads the records as rows. The CloudTrail example in the docs walks through it.
Is there an NDJSON viewer for the Mac?
Yes. With DuckLocal installed, select a .jsonl or .ndjson file in Finder and press Space: Quick Look shows its columns and first 50 rows without opening the app.
Does DuckLocal upload my JSON files?
No. Files are read where they are by DuckDB inside the app. Nothing is copied to a server, and there is no account or telemetry.

Run SQL on your own JSON

Download DuckLocal, drop in a JSON file or a folder of logs, and ask your first question.