How to query a JSON Lines file

To query a JSON Lines file, 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 query the file directly:

The .jsonl extension maps to ClickHouse's JSONEachRow format, which reads each line's keys as column names and infers the types from the values. The file is queried in place with no import step.

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

See the schema without declaring one #

Each line is a self-describing JSON object, so you never write CREATE TABLE. DESCRIBE prints the column names taken from the keys and the types ClickHouse inferred from the values:

The keys gave the names; the values gave the types. Columns are Nullable because inference allows for missing keys: if a line omits a field, that row reads back as null instead of failing the parse.

Filter, group, and aggregate directly on the file #

Because the file is a table, the full SQL surface works on it (WHERE, GROUP BY, aggregate functions, window functions, joins) with no load step in between:

This is the everyday workflow: open a terminal, run one query against the file, read the answer.

JSONL, NDJSON, and JSON Lines are the same format #

These three names describe the same thing: one JSON object per line, newline-delimited. JSONL is the common file extension, NDJSON ("newline-delimited JSON") is the spec name, and "JSON Lines" is the human-readable label. ClickHouse reads all of them with JSONEachRow, and the .ndjson extension maps to it exactly like .jsonl:

So if your tool emits .ndjson, query it the same way. (See what is NDJSON for the format background, and the NDJSON and JSONL versions of this guide, which are the same recipe under their own names.)

This is the one place JSON Lines trips people up versus a plain .json file. A .json file usually holds a single JSON value: one big array or one object spanning many lines. JSON Lines is the opposite: many independent objects, one per line, and no enclosing brackets or commas between them. That difference is why JSON Lines streams and appends cleanly (you can tail -f it or cat two files together) while a single JSON array does not. If your data is actually one array or one nested document, see run SQL on a JSON file and query nested JSON with SQL instead.

Name the format for odd extensions #

The .jsonl and .ndjson extensions are auto-detected. If your file ends in something else (.txt, .log, no extension at all) ClickHouse can't guess, so name the format as the second argument to file():

JSONEachRow also reads a top-level array #

JSONEachRow will also parse a file that is one top-level JSON array, treating each element as a row. So an API response saved as [{...},{...}] queries the same way, no reshaping first:

One format reads both the line-delimited form and the single-array form, which covers most JSON you'll be handed.

Gzipped JSON Lines work too #

You don't need to 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, which matters for JSON Lines specifically because it is verbose text and compresses well, so log dumps usually arrive gzipped.

How fast is it? #

Small files are instant in anything. The difference shows up at scale. On a 3,000,000-row, ~360 MB JSON Lines file (events_large.jsonl), a full GROUP BY country with sum and avg over every row completes in:

~0.52 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 from scratch on every run; there's no cached table. JSON Lines is heavier to parse than a columnar format, so if you'll query the same data repeatedly, convert it once to Parquet with convert JSONL to Parquet and the scans get faster again.

clickhouse local runs the same SQL unchanged across dozens of file formats and remote sources.

The same SQL scales unchanged #

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

Run it yourself #

The complete, runnable example lives here. It has generate.sh (builds the demo file, a gzipped copy, and the ~360 MB perf file), run.sh (every command above), and expected_output.txt:

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

Related #

• What is NDJSON?
• Query an NDJSON file
• Run SQL on a JSONL file
• Run SQL on a JSON file
• Query nested JSON with SQL
• Run SQL on a CSV file