Storing JSON in ClickHouse and Querying Its Fields Fast

ClickHouse is built for analytics over wide tables, and JSON arrives in it as events: logs, clickstreams, API payloads. There are two ways to hold the payload. You can store it as a String and extract fields with the JSONExtract family at query time, or you can use the native JSON type, which splits the document into its paths and stores each as a column. The difference in size and speed is large enough on 200,000 small documents to settle most decisions. This article measures both, explains how to read values back from the JSON type, and shows the loading options that decide what happens to a bad line.

As a String

The simplest model needs no special type:

CREATE TABLE ev_str (id UInt32, doc String) ENGINE = MergeTree ORDER BY id;

The documents loaded as 29.2 MiB of text, which compressed to 5.68 MiB for the doc column (6.45 MiB for the table including the id). You read fields with functions that parse the string:

SELECT JSONExtractString(doc, 'brand')              AS brand,
       JSONExtractFloat(doc, 'price')               AS price,
       JSONExtractBool(doc, 'in_stock')             AS in_stock,
       JSONExtractInt(doc, 'specs', 'weight_g')     AS weight,
       JSONExtractString(doc, 'tags', 1)            AS first_tag
FROM ev_str WHERE id = 1;

For the first row that returned Globex, 74.37, 1, 101 and sale. The arguments after the document are the path, one key (or a 1-based array index) per argument. The function name sets the type, and each has a typed cousin: JSONExtractString, JSONExtractInt, JSONExtractUInt, JSONExtractFloat, JSONExtractBool, JSONExtractRaw (the sub-document as text), JSONExtractKeys, JSONExtractArrayRaw, and the general JSONExtract(doc, path..., 'Type') with a ClickHouse type as the last argument:

SELECT JSONExtract(doc, 'tags', 'Array(String)'),
       JSONExtract(doc, 'specs', 'Tuple(weight_g UInt32, color String)')
FROM ev_str WHERE id = 1;

returned ['sale','eco'] and (101,'blue'). The helpers JSONHas(doc, 'a', 'b') (is the path there), JSONLength(doc, 'tags') (2) and JSONType(doc, 'price') (Double) round it out. isValidJSON(text) returns 1 or 0.

Behaviors to know, all observed on 26.10:

• A missing path or a wrong type does not raise an error. JSONExtractInt(doc, 'nope') returned 0, JSONExtractFloat(doc, 'brand') returned 0 for a text value, and JSONExtractString(doc, 'nope') returned an empty string. That is useful for dirty data and dangerous for correctness: a zero from a missing field looks like a real zero. Use JSONHas when the difference matters.

• **JSONExtractString on a number returned its text**: JSONExtractString(doc, 'price') gave 299.05 for one row. Older versions returned an empty string here, so check your version.

• Invalid text gives empty or zero results rather than an error: JSONExtractString('not json', 'a') returned an empty string. isValidJSON('not json') is 0.

Speed: a filter on a nested key, JSONExtractString(doc, 'specs', 'dims', 'w') = '20', took 33 ms over 200,000 rows, because every query parses every document. A group-by on brand with the average price took 57 ms. Not slow, since ClickHouse is fast, and the work scales with the text size.

As the JSON type

The native type stores each path as its own column:

CREATE TABLE ev_json (id UInt32, doc JSON) ENGINE = MergeTree ORDER BY id;
INSERT INTO ev_json SELECT id, doc FROM ev_str;

The same documents took 1.89 MiB for the doc column (2.66 MiB for the table), against 5.68 MiB as a string, a third of the size. JSONAllPaths(doc) on the first row listed the nine leaf paths: brand, in_stock, price, sku, specs.color, specs.dims.h, specs.dims.w, specs.weight_g and tags, with their types: String, Bool, Float64, Int64 and Array(Nullable(String)). Repeated paths across rows compress as columns do, which is why the saving is large.

Speed followed: the nested-key filter, now written doc.specs.dims.w = 20, took 3 ms against 33 ms, and the grouped average took 6 ms (37 ms on one repeat) against 57 ms. The filter reads one subcolumn, not the whole document.

Reading from it

You address paths with dots, and what comes back has the type Dynamic, a value that remembers its own type per row:

SELECT doc.brand, doc.price, doc.in_stock, doc.tags, doc.specs.dims.w, doc.nope
FROM ev_json WHERE id = 1;

returned Globex, 74.37, true, ['sale','eco'], 11 and NULL. Note the missing path gives NULL, unlike the string functions' zero. Cast when you need a concrete type: doc.price::Float64, doc.brand::String. A few things behaved in ways worth writing down:

• **A sub-object is not doc.specs.** That returned NULL. To get a whole sub-object, use the ^ syntax: doc.^specs returned {"color":"blue","dims":{"h":6,"w":11},"weight_g":101}.

• Mixed types in one path are allowed, and that is the hazard. In a small table where a was 1, "two" and 2.5 in three rows, dynamicType(doc.a) said Int64, String and Float64. A cast to Int64 of the whole column raised Cannot parse string 'two' as Int64, and doc.a = 1 raised There is no supertype for types String, UInt8. The typed accessor doc.a.:Int64 returns the value where the row holds an Int64 and NULL otherwise, which is the safe way: it matched 1 row. toInt64OrNull(doc.a::String) also works.

• Keys are sorted: stored and printed with the keys in alphabetical order, so {"brand":...,"in_stock":...} regardless of input order. Duplicates and exact formatting are not preserved; if you need the original text, keep a String column too.

Hints and limits

The type takes parameters. A column declared JSON(max_dynamic_paths=2, sku String, price Float64) fixed the types of sku and price, so a document with "price":"12" stored the number 12 as a Float64; it limited the number of paths kept as true subcolumns to two, and sent the rest to shared storage (JSONDynamicPaths listed x and y, JSONSharedDataPaths listed w.k and z). Paths in shared storage still read correctly, but not as fast as subcolumns. Documents with thousands of distinct keys, such as maps keyed by id, are the case where the limits matter. Hint the paths you filter and aggregate on, and leave the rest dynamic.

Inserting text that is not JSON into a JSON column fails (Cannot parse JSON object here: not json). Use isValidJSON if you load from a String column.

Typed columns

The third option is no JSON at all. I extracted six fields into a table with LowCardinality(String), Float64, Bool, Array(String) and integer columns:

INSERT INTO typed SELECT id, JSONExtractString(doc, 'brand'), JSONExtractFloat(doc, 'price'),
       JSONExtractBool(doc, 'in_stock'), JSONExtract(doc, 'tags', 'Array(String)'),
       JSONExtractUInt(doc, 'specs', 'weight_g'), JSONExtractString(doc, 'specs', 'color')
FROM ev_str;

The table took 1.70 MiB on disk, less than the JSON type's 2.66 MiB (which still carries every path), and the group-by took 4 to 7 ms. The ranking is the one every database in this series shows: String is flexible and slow, the JSON type is flexible and fast, typed columns are fastest and need a schema. In ClickHouse the JSON type narrows the gap more than most. It is a good default for events whose shape drifts, with the fields you filter on all day promoted into real columns or MATERIALIZED columns that extract them at insert time.

Loading

The usual input is newline-delimited JSON, loaded with FORMAT JSONEachRow:

{"id":1,"brand":"Acme","extra":{"a":1}}
{"id":2,"brand":"Globex"}
not json
{"id":3,"brand":"Hooli"}

Piped into INSERT INTO je FORMAT JSONEachRow for a table with id and brand, this failed with Cannot parse input: expected '{' before: 'not json' at row 3, and no rows were inserted: the block is atomic. Adding --input_format_skip_unknown_fields=1 (to ignore the extra field) and --input_format_allow_errors_num=1 (to tolerate one bad line) loaded the three good rows. Decide per pipeline whether a bad line should stop the load or be skipped; skipped lines vanish unless you log them. Land bad input in a String column in a staging table if you need to look at it. For the file format itself, see JSON Lines for data pipelines and log files.

When to use typed columns instead

The shape is stable. Declare the columns; you get the smallest and fastest table.

You filter on a few fields constantly. Make them columns (or MATERIALIZED expressions over the JSON column) and put them in the ORDER BY key, where ClickHouse's primary index can use them.

Types drift between rows. A path that is sometimes a number and sometimes a string forces Dynamic handling, casts and .:Type accessors in every query. Normalize on the way in.

You need the exact original text. Keep a compressed String column beside the parsed one.

You are doing updates. ClickHouse is append-oriented; mutations on large JSON columns are heavy. Insert new versions and read the latest.

The reference is ClickHouse's documentation of the JSON data type and of the JSON functions. The comparison with the document stores and other warehouses is in choosing between a JSON column and a document database, and the DuckDB equivalent of this idea, reading JSON as columns directly, is in querying JSON files with DuckDB. A sample document can be pretty printed in the JSON formatter.