---
title: Query JSON and NDJSON Files with SQL | DuckLocal
description: "Run DuckDB SQL on JSON, NDJSON and JSON Lines files, gzipped or not. Nested objects become columns you can filter, unnest and join with CSV and Parquet, on your own computer."
image: https://ducklocal.app/og-image.jpg?v=2
language: en
---

# 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. Files are read in place and never uploaded.

## From a JSON file to a result

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.
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

```sql
-- 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;
```

For data whose shape varies from record to record, `json_extract_string(col, '$.a.b[0].c')` walks a path and returns text.

## 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. When a tool wraps its 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. See [Audit CloudTrail logs locally](https://ducklocal.app/docs/example-cloudtrail).

## 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. It never writes, never scans the whole file and downloads nothing.

## JSON from the command line

```bash
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"
```

## 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.

**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.

**Does DuckLocal upload my JSON files?** No. Files are read where they are by DuckDB inside the app. There is no account or telemetry.

## Links

- [Download DuckLocal](https://github.com/JetSquirrel/DuckLocal/releases/latest)
- [JSONL and nested JSON in the CLI](https://ducklocal.app/docs/cli#jsonl-and-nested-json)
- [Quick Look previews](https://ducklocal.app/docs/data-sources#quick-look)
- [Query CSV with SQL](https://ducklocal.app/use-cases/query-csv-with-sql/)
- [Query Excel with SQL](https://ducklocal.app/use-cases/query-excel-with-sql/)

Source: https://ducklocal.app/use-cases/query-json-with-sql/
