ClickHouse JSON Queries

ClickHouse gives you two ways to work with JSON, and the right choice depends on how predictable your data is. You can store JSON as a String and pull values out at query time with the JSONExtract* family, or you can use the native JSON data type (production-ready since v24.8) that parses and columnarizes paths on insert. This guide covers both, when each fits, and the gotchas.

Sample Data

CREATE TABLE logs
(
    id       UInt64,
    payload  String
)
ENGINE = MergeTree
ORDER BY id;
 
INSERT INTO logs VALUES
    (1, '{"user": {"id": 42, "name": "Ada"}, "tags": ["a", "b"], "active": true}'),
    (2, '{"user": {"id": 43, "name": "Linus"}, "tags": ["c"], "active": false}');

Approach 1: String Column + JSONExtract

If JSON is stored in a String column, the JSONExtract* functions parse it on every read. Each function is typed, so you tell ClickHouse what you expect back.

SELECT
    JSONExtractUInt(payload, 'user', 'id')      AS user_id,
    JSONExtractString(payload, 'user', 'name')  AS user_name,
    JSONExtractBool(payload, 'active')           AS active
FROM logs;

Path arguments are passed as a sequence of keys, not a dotted string. JSONExtractUInt(payload, 'user', 'id') walks into user then id. For array indices, pass an integer (1-based; negative counts from the end):

SELECT JSONExtractString(payload, 'tags', 1) AS first_tag
FROM logs;

Other useful extractors:

• JSONExtractInt, JSONExtractFloat, JSONExtractRaw (returns the raw JSON fragment as a string)
• JSONExtract(payload, 'tags', 'Array(String)') parses into a typed ClickHouse value, including arrays and tuples
• JSONExtractKeys(payload, 'user') returns the keys of an object
• JSONHas(payload, 'user', 'id') checks for existence
• JSONType(payload, 'tags') returns the JSON type of a value (Array, Object, String, etc.)
• isValidJSON(payload) validates without extracting

SELECT
    JSONExtract(payload, 'tags', 'Array(String)') AS tags,
    length(JSONExtract(payload, 'tags', 'Array(String)')) AS tag_count
FROM logs;

The cost: parsing runs at query time on the full string, for every matching row. If you repeatedly query the same path, this is wasted work. There is also the simpler simpleJSONExtract* family, which is faster but only handles flat, well-formed JSON without nested escaping. Reach for it only when you know the shape is trivial.

Approach 2: The Native JSON Type

Since v24.8, ClickHouse has a native JSON type that stores each distinct path as its own subcolumn under the hood. Paths are parsed once on insert, so reads do not re-parse the whole document, and queries touch only the subcolumns they reference.

CREATE TABLE logs_json
(
    id       UInt64,
    payload  JSON
)
ENGINE = MergeTree
ORDER BY id;
 
INSERT INTO logs_json VALUES
    (1, '{"user": {"id": 42, "name": "Ada"}, "active": true}'),
    (2, '{"user": {"id": 43, "name": "Linus"}, "active": false}');

Read paths with dotted access. Because a path can hold mixed types across rows, values come back wrapped in Dynamic, so cast when you need a concrete type:

SELECT
    payload.user.id::UInt32   AS user_id,
    payload.user.name::String AS user_name
FROM logs_json;

Inspect what paths exist with JSONAllPaths:

SELECT JSONAllPaths(payload) FROM logs_json;

The type has a max_dynamic_paths parameter (default 1024). Paths beyond that limit are not given their own subcolumn; they fall back to a shared Map-like structure that is still queryable but slower. If your JSON has thousands of distinct keys (for example, high-cardinality event properties), tune this or model the hot paths as real columns.

Choosing Between Them

Use the native JSON type when the same payloads are queried repeatedly and you care about scan performance, or when the schema drifts over time but a handful of paths dominate your queries. Use String + JSONExtract when JSON is rarely queried (cold storage, occasional debugging), when documents are huge and you only ever touch one or two fields, or when you are on a version older than v24.8.

A common pattern is to keep the raw String for fidelity and materialize the hot paths into typed columns with a materialized column or view, giving you both the original document and fast columnar reads.

Common Mistakes

• Dotted path strings. JSONExtractString(payload, 'user.name') looks for a top-level key literally named user.name. Pass keys as separate arguments instead.
• Wrong extractor type. JSONExtractUInt on a value that is actually a string returns 0, not an error. Check with JSONType if results look empty.
• Forgetting array indices are 1-based. JSONExtractString(payload, 'tags', 0) returns nothing; the first element is index 1.
• Assuming the native type re-parses cheaply at any cardinality. Past max_dynamic_paths, extra paths lose their dedicated subcolumn and query speed drops.

If you are exploring an unfamiliar JSON column, Mako's AI autocomplete can suggest the right JSONExtract* variant and path arguments as you type, which saves a few round-trips through the docs.

Related Guides

• ClickHouse Aggregate Functions
• ClickHouse Window Functions
• Import JSON to ClickHouse

Mako connects to ClickHouse with AI-powered autocomplete for queries like these. Try it free at mako.ai.