---
title: "How to query nested JSON with SQL"
date: "2026-06-08"
author: "Al Brown"
category: "Engineering"
excerpt: "Query deeply nested JSON with SQL from your terminal using clickhouse local — dot access into objects, ARRAY JOIN to explode arrays, and the JSON type for irregular keys, with no server and no import step."
---

# How to query nested JSON with SQL

To query nested JSON with SQL, use [`clickhouse local`](/resources/engineering/what-is-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`](https://clickhouse.com/docs/install):


```bash
curl https://clickhouse.com/cli | sh   # install clickhousectl
clickhousectl local use latest         # download ClickHouse and put it on your PATH
```

Then reach into a nested object with dot notation:


```bash
clickhouse local -q "
SELECT event_id, user.name, user.geo.country, user.geo.city
FROM file('events.jsonl')
FORMAT PrettyCompact"
```

```response
   ┌─event_id─┬─user.name─┬─user.geo.country─┬─user.geo.city─┐
1. │        1 │ Ada       │ GB               │ London        │
2. │        2 │ Lin       │ US               │ NYC           │
3. │        3 │ Sam       │ DE               │ Berlin        │
4. │        4 │ Mei       │ FR               │ Paris         │
5. │        5 │ Omar      │ IN               │ Delhi         │
   └──────────┴───────────┴──────────────────┴───────────────┘
```

`user.geo.country` is a real column expression. The file is read in place with no import step: ClickHouse infers nested objects as typed `Tuple` columns, so the dot path resolves at the SQL layer and you can filter, group, and join on it like any other column.

## The data: objects inside arrays inside objects

The sample file is line-delimited JSON ([JSONL](/resources/engineering/run-sql-on-jsonl-file)). Each line nests a `user` object (with a `geo` object inside it), an `items` array of objects, and a free-form `props` object whose keys differ by event type:


```response
{"event_id":1,"ts":"2026-06-01T01:00:00Z","event_type":"login","user":{"id":1000,"name":"Ada","geo":{"country":"GB","city":"London"}},"items":[{"sku":"SKU-A","qty":1,"price":10.99}],"props":{"method":"sso","mfa":true}}
{"event_id":2,"ts":"2026-06-01T01:10:11Z","event_type":"purchase","user":{"id":1001,"name":"Lin","geo":{"country":"US","city":"NYC"}},"items":[{"sku":"SKU-B","qty":1,"price":11.99},{"sku":"SKU-C","qty":2,"price":12.99}],"props":{"coupon":"SUMMER","gateway":"stripe","installments":3}}
```

## See the nested schema without declaring one

`DESCRIBE` shows how the structure was inferred. Objects become `Tuple`s, arrays of objects become `Array(Tuple(...))`, and nesting is preserved all the way down:


```bash
clickhouse local -q "DESCRIBE file('events.jsonl')"
```

```text
event_id: Nullable(Int64)
ts: Nullable(DateTime)
event_type: Nullable(String)
user: Tuple(geo Tuple(city Nullable(String), country Nullable(String)), id Nullable(Int64), name Nullable(String))
items: Array(Tuple(price Nullable(Float64), qty Nullable(Int64), sku Nullable(String)))
props: Tuple(approved_by Nullable(String), coupon Nullable(String), gateway Nullable(String), installments Nullable(Int64), method Nullable(String), mfa Nullable(Bool), reason Nullable(String), referrer Nullable(String))
```

Two things to notice. `user` and `items` are typed exactly as they appear. But `props` has been flattened into one wide tuple holding the union of every key seen across the sampled lines, each `Nullable`. That is the central fact about irregular JSON, and we deal with it below.

## Dot access into nested objects

Read a nested scalar by walking the path with dots. No unnesting, no JSON functions:


```bash
clickhouse local -q "
SELECT event_id, user.name, user.geo.country, user.geo.city
FROM file('events.jsonl')
FORMAT PrettyCompact"
```

```response
   ┌─event_id─┬─user.name─┬─user.geo.country─┬─user.geo.city─┐
1. │        1 │ Ada       │ GB               │ London        │
2. │        2 │ Lin       │ US               │ NYC           │
3. │        3 │ Sam       │ DE               │ Berlin        │
4. │        4 │ Mei       │ FR               │ Paris         │
5. │        5 │ Omar      │ IN               │ Delhi         │
   └──────────┴───────────┴──────────────────┴───────────────┘
```

`user.geo.country` is a real column expression. You can `GROUP BY` it, filter on it, or join on it like any other.

## Explode a nested array with ARRAY JOIN

`items` is an array of objects, so one event holds several line items. `ARRAY JOIN` turns each element into its own row, after which the elements' fields are addressable with the same dot notation:


```bash
clickhouse local -q "
SELECT event_id, item.sku AS sku, item.qty AS qty, item.price AS price
FROM file('events.jsonl')
ARRAY JOIN items AS item
FORMAT PrettyCompact"
```

```response
   ┌─event_id─┬─sku───┬─qty─┬─price─┐
1. │        1 │ SKU-A │   1 │ 10.99 │
2. │        2 │ SKU-B │   1 │ 11.99 │
3. │        2 │ SKU-C │   2 │ 12.99 │
4. │        3 │ SKU-C │   1 │ 12.99 │
5. │        3 │ SKU-D │   2 │ 13.99 │
6. │        3 │ SKU-A │   3 │ 14.99 │
7. │        4 │ SKU-D │   1 │ 13.99 │
8. │        5 │ SKU-A │   1 │ 14.99 │
9. │        5 │ SKU-B │   2 │ 15.99 │
   └──────────┴───────┴─────┴───────┘
```

Event 3 had three items, so it became three rows. Once the array is flat, aggregate over it like any table. Revenue per SKU across the whole file:


```bash
clickhouse local -q "
SELECT item.sku AS sku, sum(item.qty) AS units, round(sum(item.qty * item.price), 2) AS revenue
FROM file('events.jsonl')
ARRAY JOIN items AS item
GROUP BY sku
ORDER BY revenue DESC
FORMAT PrettyCompact"
```

```response
   ┌─sku───┬─units─┬─revenue─┐
1. │ SKU-A │     5 │   70.95 │
2. │ SKU-B │     3 │   43.97 │
3. │ SKU-D │     3 │   41.97 │
4. │ SKU-C │     3 │   38.97 │
   └───────┴───────┴─────────┘
```

## Irregular keys: the JSON type and JSONExtract

Dot access works when every record shares a shape. The `props` object does not: a login carries `method` and `mfa`, a purchase carries `gateway` and `installments`, a refund carries `reason`. Folding all of that into one wide tuple is lossy and brittle.

You have two clean options. The first keeps the column as raw JSON text and pulls keys on demand with the `JSONExtract*` family. Override just that column's type to `String` so it is not flattened:


```bash
clickhouse local -q "
SELECT
  event_type,
  JSONExtractString(props, 'gateway') AS gateway,
  JSONExtractString(props, 'reason')  AS refund_reason,
  JSONExtractBool(props, 'mfa')       AS mfa
FROM file('events.jsonl', 'JSONEachRow',
  'event_id UInt32, event_type String, props String')
FORMAT PrettyCompact"
```

```response
   ┌─event_type─┬─gateway─┬─refund_reason─┬─mfa─┐
1. │ login      │         │               │   1 │
2. │ purchase   │ stripe  │               │   0 │
3. │ view       │         │               │   0 │
4. │ refund     │         │ damaged       │   0 │
5. │ login      │         │               │   1 │
   └────────────┴─────────┴───────────────┴─────┘
```

`JSONExtractString`, `JSONExtractInt`, `JSONExtractBool`, `JSONExtractArrayRaw` and friends each take a path and return the typed value, with a default when the key is missing. Good for one-off pulls from messy text.

The second option is the [`JSON` type](https://clickhouse.com/docs/sql-reference/data-types/newjson). Declare the column as `JSON` and dynamic keys become first-class paths: present keys return their value, absent keys return `NULL`, all without listing the schema up front:


```bash
clickhouse local -q "
SELECT
  event_type,
  props.gateway      AS gateway,
  props.installments AS installments,
  props.reason       AS refund_reason
FROM file('events.jsonl', 'JSONEachRow',
  'event_id UInt32, event_type String, props JSON')
SETTINGS enable_json_type = 1
FORMAT PrettyCompact"
```

```response
   ┌─event_type─┬─gateway─┬─installments─┬─refund_reason─┐
1. │ login      │ ᴺᵁᴸᴸ    │ ᴺᵁᴸᴸ         │ ᴺᵁᴸᴸ          │
2. │ purchase   │ stripe  │ 3            │ ᴺᵁᴸᴸ          │
3. │ view       │ ᴺᵁᴸᴸ    │ ᴺᵁᴸᴸ         │ ᴺᵁᴸᴸ          │
4. │ refund     │ ᴺᵁᴸᴸ    │ ᴺᵁᴸᴸ         │ damaged       │
5. │ login      │ ᴺᵁᴸᴸ    │ ᴺᵁᴸᴸ         │ ᴺᵁᴸᴸ          │
   └────────────┴─────────┴──────────────┴───────────────┘
```

The `JSON` type stores each path separately and tracks its type, so reads stay columnar even when the keys are sparse. Reach for `JSONExtract*` for quick text probing; reach for the `JSON` type when irregular keys are the rule and you want to keep querying them as columns.

You can also treat an entire document as one `JSON` value with the `JSONAsObject` input format, then dot your way in from a single column:


```bash
clickhouse local -q "
SELECT json.event_type, json.user.geo.country AS country, json.user.name AS name
FROM file('events.jsonl', JSONAsObject, 'json JSON')
SETTINGS enable_json_type = 1
FORMAT PrettyCompact"
```

```response
   ┌─json.event_type─┬─country─┬─name─┐
1. │ login           │ GB      │ Ada  │
2. │ purchase        │ US      │ Lin  │
3. │ view            │ DE      │ Sam  │
4. │ refund          │ FR      │ Mei  │
5. │ login           │ IN      │ Omar │
   └─────────────────┴─────────┴──────┘
```

## Gzipped JSON works too

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


```bash
clickhouse local -q "
SELECT user.geo.country AS country, count() AS events
FROM file('events.jsonl.gz', JSONEachRow)
GROUP BY country ORDER BY country
FORMAT PrettyCompact"
```

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

## How fast is it?

The work in nested JSON is parsing text and walking paths. On a 500,000-row, ~137 MB JSONL file (`events_large.jsonl`), exploding the `items` array with `ARRAY JOIN` and grouping by `user.geo.country` and SKU runs in:


```bash
clickhouse local -q "
SELECT user.geo.country AS country, item.sku AS sku,
       sum(item.qty) AS units, round(sum(item.qty * item.price), 2) AS revenue
FROM file('events_large.jsonl')
ARRAY JOIN items AS item
GROUP BY country, sku ORDER BY revenue DESC LIMIT 5"
```

**~0.50 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 time includes parsing every nested object from scratch and the process startup; there is no cached table. `clickhouse local` runs the same SQL unchanged across dozens of formats and remote sources.

## The same SQL scales unchanged

The dot access, `ARRAY JOIN`, and `JSON` type you just used on a laptop file are the same SQL you would run on a ClickHouse server or in [ClickHouse Cloud](https://clickhouse.com/cloud). Swap `file('events.jsonl')` for a table name and the nested-JSON logic stays put. You prototype against a file on your machine and ship the identical query to production.

## Run it yourself

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

**[github.com/ClickHouse/examples → local-analytics/clickhouse-local-nested-json](https://github.com/ClickHouse/examples/tree/main/local-analytics/clickhouse-local-nested-json)**


```bash
git clone https://github.com/ClickHouse/examples
cd examples/local-analytics/clickhouse-local-nested-json
./generate.sh && ./run.sh
```

Prefer Python? → [Flatten nested JSON in Python with chDB](/resources/engineering/flatten-nested-json-python)

## Related

- [What is clickhouse-local?](/resources/engineering/what-is-clickhouse-local)
- [Run SQL on a JSONL file](/resources/engineering/run-sql-on-jsonl-file)
- [Run SQL on a JSON file](/resources/engineering/run-sql-on-json-file)
- [What is NDJSON?](/resources/engineering/what-is-ndjson)