How to Query JSON in ClickHouse

ClickHouse doesn't need a native JSON column type to be great at JSON. The pattern used in most production deployments is simpler: store the raw JSON in a String column, and extract fields at query time with built-in functions. You get schemaless ingestion - add fields whenever you want, no migrations - plus all of ClickHouse's aggregation speed.

Here's the practical guide, using an events table shaped the way most event pipelines end up:

CREATE TABLE events (
    collection String,
    event_timestamp DateTime,
    json String
)

Extracting scalar fields#

The JSONExtract family pulls typed values out of the string. The workhorses:

SELECT
    JSONExtractString(json, 'event') AS event,
    JSONExtractString(json, 'user_id') AS user_id,
    JSONExtractFloat(json, 'mrr') AS mrr
FROM events
WHERE collection = 'billing'

There are extractors for every common type: JSONExtractString, JSONExtractInt, JSONExtractUInt, JSONExtractFloat, JSONExtractBool. If the key is missing or has the wrong type, you get the type's default (empty string, 0) rather than an error - which is usually what you want when querying messy real-world payloads. Use JSONHas(json, 'mrr') when you need to distinguish "absent" from "zero".

Nested paths and raw fragments#

Extra arguments to the extractors walk into nested objects:

-- {"user": {"plan": {"tier": "pro"}}}
JSONExtractString(json, 'user', 'plan', 'tier')  -- 'pro'

When you need a whole sub-object or array unparsed, JSONExtractRaw returns it as a string you can extract from again or return to the application.

Arrays#

For JSON arrays, extract as a ClickHouse array and expand rows with arrayJoin:

SELECT arrayJoin(JSONExtractArrayRaw(json, 'items')) AS item
FROM events
WHERE collection = 'orders'

Each array element becomes its own row, so per-item aggregation ("which SKUs sell most, across all orders") is a plain GROUP BY away.

Performance: materialize the hot paths#

Extracting from strings at query time is flexible, but it means parsing JSON on every scan. Once you know which fields you query constantly, promote them to real columns:

ALTER TABLE events
    ADD COLUMN plan String MATERIALIZED JSONExtractString(json, 'plan')

A MATERIALIZED column is computed once at insert time, then stored and compressed like any other column - queries against it never touch the raw JSON, and it can participate in indexes. The standard pattern is: keep the raw JSON column as the flexible source of truth, and materialize the handful of fields that power your dashboards. You keep schemaless ingestion and get columnar speed where it counts.

How GraphJSON uses this#

GraphJSON exposes each collection in the SQL notebook as a table with timestamp and json columns. Nested payloads are flattened by default, so a nested key such as user.plan.tier is extracted by that literal dot-separated name. The SQL for product analytics guide uses GraphJSON’s exact table shape for activation, revenue, latency, funnels, and adoption queries.