How to query a JSON file with SQL

To query a JSON file with SQL, use clickhouse local. It runs SQL directly on files from the command line, with no server to install. It's part of ClickHouse, so the same query scales to billions of rows when you outgrow your laptop.

Install it with clickhousectl:

Then point it at your JSON file with the JSONEachRow format:

JSONEachRow reads one object per line directly from disk, inferring column names and types without a schema declaration or an import step. Nested objects become Tuple columns and arrays become Array columns, so you can query their members in the same SELECT.

Prefer Python? See How to read a JSON file in Python and query it with SQL for the same queries against a pandas DataFrame.

JSONL and a top-level array read the same way #

JSON on disk comes in two common shapes. Line-delimited JSON (also called JSONL or NDJSON) has one object per line:

The other shape is a single top-level array, [{...}, {...}, ...]. Both read with the same JSONEachRow format. ClickHouse detects the leading [ and parses the array element-by-element, so you do not load the whole file into memory and you do not need a different command:

JSONEachRow is the format to reach for in both cases. (ClickHouse also has a JSON format, but that one is for its own metadata-wrapped output, not for reading arbitrary array files.)

See the schema without declaring one #

DESCRIBE shows you what ClickHouse inferred, including how it handled the nested object and the array:

A nested JSON object becomes a Tuple with named fields. A JSON array becomes an Array. Scalars (event_id, event_type, amount) come back as Nullable(Int64), Nullable(String) and Nullable(Float64). No CREATE TABLE, no schema file.

Reach into nested objects with dot access #

Because geo is a Tuple with named fields, you read its members with dot notation. There is no JSON path language to learn and no per-row parsing function to call; the field is a column:

Nested fields group and aggregate like any other column:

For deeper or irregular nesting, see how to query nested JSON with SQL.

Explode an array column with ARRAY JOIN #

The tags column holds an array per row. To count over the individual elements, unfold the array with ARRAY JOIN: it produces one output row per array element, then you aggregate as usual:

This is the JSON workflow that viewers and jq one-liners make awkward: a real GROUP BY over array members. ClickHouse covers the same ground with one binary that also reads CSV, Parquet, ORC, Arrow and many other formats, and runs the same SQL unchanged against a server or ClickHouse Cloud when the data outgrows your machine.

Gzipped JSON works too #

You do not unzip anything first. clickhouse local detects the .gz extension and decompresses on the fly, so a .jsonl.gz file is queried exactly like a .jsonl:

The same applies to .jsonl.zst, .jsonl.xz and other supported codecs. The compression is inferred from the file name.

How fast is it? #

JSON is text, so every query parses it from scratch; there is no cached table. That parse is the work, and it runs across all cores. On a 3,000,000-row, ~314 MB JSONL file (events_large.jsonl), grouping by the nested geo.country field with a sum over every row completes in:

~0.66 seconds, best of three with a warm OS page cache, on an Apple M4 Pro laptop (14 cores, 24 GB RAM; clickhouse local 26.6.1.117). That includes parsing the JSON text and inferring the schema on every run. If you query the same data repeatedly, convert it once to a columnar format and read that instead: see convert JSON to Parquet.

The same SQL scales unchanged #

The query you just ran against a file on your laptop is the same SQL you would run on a ClickHouse server, or in ClickHouse Cloud. Nothing about SELECT ... ARRAY JOIN ... GROUP BY changes. You swap file('events.jsonl', JSONEachRow) for a table name and the rest stays put. You prototype against a file and ship the identical logic to production.

Run it yourself #

The complete, runnable example lives in the ClickHouse examples repo. It has generate.sh (builds the sample JSONL, the top-level-array copy, a gzipped copy, and the ~314 MB perf file), run.sh (every command above), and expected_output.txt:

github.com/ClickHouse/examples → local-analytics/clickhouse-local-json

Related #

• What is NDJSON?
• Query nested JSON with SQL
• Run SQL on a JSONL file
• Run SQL on a CSV file
• Read a JSON file in Python with chDB