Working with JSON in ClickHouse

JSON in ClickHouse is really three separate problems: getting JSON in as rows, storing a document whose shape varies, and getting JSON out in the shape a client wants. They have separate tools.

1. Loading: JSONEachRow maps keys to columns

The workhorse format is JSONEachRow: one JSON object per line, one row per object, each key matched to a column by name.

INSERT INTO events FORMAT JSONEachRow
{"event_time": "2026-01-01 00:00:00", "user_id": 7, "event_type": "view"}
{"event_time": "2026-01-01 00:00:01", "user_id": 8, "event_type": "buy"}

Missing keys take the column default; unknown keys are ignored when input_format_skip_unknown_fields = 1.

Nested objects are the sticking point of this format. A nested object must land in a column that can hold it, such as a Tuple, a Map or a JSON column:

CREATE TABLE flat (a UInt8, n Tuple(x UInt8, y String)) ENGINE = Memory;

INSERT INTO flat SELECT *
FROM format(JSONEachRow, 'a UInt8, n Tuple(x UInt8, y String)',
            $${"a": 1, "n": {"x": 2, "y": "z"}}$$);

SELECT * FROM flat;     -- 1  (2,'z')

(The format table function is explained in its own snippet; $$...$$ is a string literal that needs no escaping.) If you would rather spread nested keys over separate columns named n.x, there is the input_format_import_nested_json setting.

The old advice was “the JSON must be flat”. That is no longer the whole story, because there is now a column type for documents.

2. Storing: the JSON type

When the fields differ per row, or you do not know them all up front, use a JSON column. ClickHouse splits the document into paths, stores each path as its own subcolumn with its own inferred type, and reads only the paths you ask for.

CREATE TABLE logs (id UInt32, doc JSON) ENGINE = MergeTree ORDER BY id;

INSERT INTO logs SELECT *
FROM format(JSONEachRow, 'id UInt32, doc JSON',
$${"id": 1, "doc": {"user": {"name": "ann", "age": 31}, "tags": ["a", "b"], "ok": true}}
{"id": 2, "doc": {"user": {"name": "bob"}, "tags": [], "extra": 1.5}}$$);

Query paths with dots:

SELECT id, doc.user.name, doc.user.age, doc.tags, doc.extra
FROM logs ORDER BY id;

1  ann  31    ['a','b']  NULL
2  bob  NULL  []         1.5

A path that a row does not have is NULL. Path values are of type Dynamic, which remembers the concrete type each value had:

SELECT dynamicType(doc.user.age) FROM logs ORDER BY id;     -- Int64, None

So filter and aggregate by casting to what you need. Use the .:Type shortcut or :::

SELECT id FROM logs WHERE doc.user.age > 30;                  -- 1
SELECT id, doc.user.age.:Int64 AS age FROM logs ORDER BY id;  -- 1 31 / 2 NULL
SELECT doc.user.age::UInt8 FROM logs;

Inspect which paths exist, and turn a document back into text:

SELECT id, JSONAllPaths(doc) FROM logs ORDER BY id;
-- 1  ['ok','tags','user.age','user.name']
-- 2  ['extra','tags','user.name']

SELECT toJSONString(doc) FROM logs ORDER BY id;
-- {"ok":true,"tags":["a","b"],"user":{"age":31,"name":"ann"}}

Notice that keys come back in sorted order, not the order you inserted them; the type stores paths, not text.

Hint the type of important paths

Paths you know will be queried often should have a fixed type, so they are stored as an ordinary column:

CREATE TABLE logs_typed
(
    id  UInt32,
    doc JSON(user.name String, SKIP debug)
)
ENGINE = MergeTree ORDER BY id;

user.name String is a typed path (fast, never Dynamic), and SKIP debug drops a noisy key at insert time.

The alternative: keep a String and extract

If you only occasionally read a few fields, store the raw text in a String and use the JSONExtract* family at query time:

SELECT JSONExtractString('{"a":{"b":"x"}}', 'a', 'b') AS s,
       JSONExtract('{"a":[1,2]}', 'a', 'Array(UInt8)') AS arr;
-- x  [1,2]

That parses the text on every query, so it is the slow option. The JSON type pays the parsing cost once, at insert.

A decision rule: stable, known keys go in real columns (best compression and speed); long-tail keys go in a JSON column; never-queried payload stays a String.

3. Exporting: column-oriented JSON formats

JSONEachRow and JSON are row-oriented. There are also three formats that emit one array per column, which is smaller and faster to parse for big, wide results or for plotting libraries:

SELECT number AS a, toString(number) AS b FROM numbers(3) FORMAT JSONColumns;

{
	"a": [0, 1, 2],
	"b": ["0", "1", "2"]
}

• JSONColumns: an object of column arrays, as above.
• JSONCompactColumns: just an array of arrays ([[0,1,2],["0","1","2"]]), no column names.
• JSONColumnsWithMetadata: like JSONColumns plus a meta section with names and types, a rows count and statistics, the column-oriented sibling of the standard JSON format.

They can also be read: SELECT * FROM format(JSONColumns, '{"a":[1,2],"b":["x","y"]}') returns two rows.