Skip to main content

ClickHouse native JSON type

The current native JSON type - the one that replaced the experimental Object('json') in ClickHouse 25.x. The two behave very differently: Object('json') unified every path to a single least-common-type and used col['key'] map access, while the native type stores each path as its own typed sub-column with dot access. A lot of older docs, blog posts and answers still describe the old one, so double-check anything you carry over. Facts here are from the live ClickHouse docs; re-verify the version-specific numbers against your server before relying on them.

The JSON type stores semi-structured data column-wise. Every JSON path is inferred and stored as its own typed sub-column on disk, so you query a field by its path and get back a real typed value, not a slice of serialized text. This is what makes WHERE json.response_code = 200 a typed integer comparison instead of a string match against a blob. It replaces the deprecated experimental Object('json') / old JSON implementation entirely.

Current as of ClickHouse 25.3+, the release that marked the type GA.

Why it exists (vs the old Object('json'))​

The old experimental Object('json') unified every value seen for a path into a single least-common-type for the whole column. A path that saw both 42 and "hello" collapsed to String; a new incompatible value could force a whole-column rewrite. It had known bugs that will not be fixed.

The native JSON type instead stores each path as its own sub-column, and paths without a type hint are backed by the Dynamic type, which allows values with different data types for the same path without unification into a least common type. One path can hold an Int64 in one row and a String in another, each stored natively and read back typed.

Version timeline:

  • New JSON type introduced experimental (behind allow_experimental_json_type), then beta.
  • Production-ready / GA in 25.3 (OSS): "JSON data type is marked as production ready in version 25.3. It's not recommended to use this type in production in previous versions."
  • Object('json') fully removed in v25.11 (backward-incompatible). Any table or query referencing Object('json') must be migrated (ALTER TABLE ... MODIFY COLUMN ... JSON) before upgrading past 25.11. Confirm the exact removal release against the target changelog.

Declaration syntax​

<column> JSON(
    max_dynamic_paths=N,
    max_dynamic_types=M,
    some.path TypeName,          -- typed path hint
    SKIP path.to.skip,           -- drop this path on parse
    SKIP REGEXP 'paths_regexp'   -- drop paths matching the regex
)
OptionPurposeDefault
max_dynamic_pathsHow many paths are stored as separate sub-columns (per block). Paths beyond this spill into a shared Map(String,String), slower to read.1024
max_dynamic_typesHow many distinct data types a single Dynamic path column keeps separate.32
some.path TypeNameForce a path to a fixed type (e.g. user_id UInt64). Typed hints read back as that exact type, not Dynamic.-
SKIP pathSkip a path entirely during parsing.-
SKIP REGEXP 'rx'Skip all paths matching the regex.-

Docs guidance: do not set max_dynamic_paths above ~10,000 on local filesystems, or 1024 on remote/object storage.

Selecting a sub-column (typed access)​

Access a path with dot notation:

SELECT json.a.b, json.c FROM test;

Type rules:

  • A path with a type hint returns that exact type: json.user_id -> UInt32.
  • An undeclared path returns Dynamic.
  • A missing path returns NULL (JSON null and absent are equivalent here).

Paths are flattened: {"a":{"b":42}} and {"a.b":42} both store as path a.b. They collide unless json_type_escape_dots_in_keys=1 (25.8+), which escapes literal dots as %2E (access then looks like json.`a%2Eb`).

Reading a nested sub-object - the ^ operator​

SELECT json.^a.b, json.^d.e.f FROM test;   -- returns JSON objects, not scalars

Casting a Dynamic path to a concrete type​

SELECT json.a.g.:Float64, json.d.:Date FROM test;   -- typed sub-column read
SELECT json.a.g::UInt64 AS uint FROM test;           -- CAST equivalent

An exception is thrown if the Dynamic value cannot be cast to the requested type.

Arrays of objects​

A JSON array of objects parses as Array(JSON); use [] to descend:

SELECT json.a.b[], json.a.b[].c.:Int64 FROM test;

Filtering on sub-columns - TYPED, not string matching​

-- RIGHT: typed integer comparison on the sub-column
SELECT count() FROM logs  WHERE json.response_code = 200;
SELECT *       FROM events WHERE json.user_id > 100;   -- typed-hint path

Casting rules for filters:

  • Type-hinted paths compare natively - no cast.
  • Dynamic (undeclared) paths compare against the value inside the Dynamic; for a guaranteed concrete type (range compare, index, mixed-type path) read it typed: WHERE json.status.:UInt16 = 200 or WHERE json.status::UInt16 = 200.
  • Use toString(json.path) / CAST only when you genuinely want the string form, not as the default way to filter.

The wrong / old-knowledge way - matching the serialized document (WHERE toString(json) LIKE '%200%', or JSONExtractInt over a String column) - defeats columnar sub-column storage, scans the whole blob, and is exactly what the native type exists to avoid.

Introspection - discovering the paths (path enumeration)​

This is how you DISCOVER what sub-columns a JSON column holds (for a UI that shows them / builds filters from them):

  • JSONAllPaths(json) - every path in the row.
  • JSONAllPathsWithTypes(json) - paths with inferred types.
  • JSONDynamicPaths(json) - paths stored as separate dynamic sub-columns.
  • JSONSharedDataPaths(json) - paths spilled into the shared structure (over max_dynamic_paths).
  • JSONAllValues(json) - all values.

The JSONExtract* family (JSONExtract, JSONExtractString, JSONExtractInt, ...) operate on JSON strings and navigate by index/key. On a native JSON column you use dot sub-column access (json.path, json.^path, json.path.:Type) instead - the columnar, typed path.

Gotchas​

  • No bracket / map access. col['key'] and arrayElement on a JSON column are rejected - dot notation (col.key) only. (Change from Object('json').)

  • Undeclared paths are Dynamic. toTypeName(json.action) returns Dynamic, not the leaf type. Cast when a downstream op needs a fixed type, or give the path a type hint in the DDL.

  • toString() / CAST needed when reading a mixed-type Dynamic path as one concrete type, forcing a comparison type, or serializing a nested object to text.

  • max_dynamic_paths overflow silently moves extra paths into a slower shared Map(String,String). Size the hint for high-cardinality path sets.

  • Indexing / ORDER BY on sub-columns is supported - a JSON sub-column can go in a data-skipping index or (via a typed hint) a primary/partition key:

    CREATE TABLE sensor_data (
        data JSON(sensor_id UInt32),
        INDEX idx_sensor data.sensor_id TYPE minmax GRANULARITY 1
    ) ENGINE = MergeTree ORDER BY tuple();

    Also index by structure with INDEX idx JSONAllPaths(data) TYPE bloom_filter.

  • Perf trade-offs the docs call out: slower INSERTs (path splitting + type inference), slower when reading entire objects vs a plain String, storage overhead from many sub-columns. Use JSON when you query/filter/aggregate on specific paths; keep String for an opaque blob you never field-query.

Worked example​

CREATE TABLE events
(
    id   UInt64,
    json JSON(user_id UInt32, timestamp DateTime, SKIP internal.debug)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO events VALUES
  (1, '{"user_id": 123, "timestamp": "2024-01-01 10:00:00", "action": "login",  "response_code": 200}'),
  (2, '{"user_id": 456, "timestamp": "2024-01-01 10:05:00", "action": "logout", "response_code": 500}');

SELECT
    json.user_id,                          -- UInt32 (type hint)
    toTypeName(json.user_id)   AS uid_t,   -- 'UInt32'
    json.action,                           -- Dynamic (inferred)
    toTypeName(json.action)    AS act_t    -- 'Dynamic'
FROM events
WHERE json.user_id > 100                   -- typed integer compare, no cast
  AND json.response_code = 200;            -- filter on an inferred path, typed

Converting an existing String column of JSON text:

ALTER TABLE test MODIFY COLUMN json JSON;
SELECT json.a, json.b FROM test;   -- now typed sub-columns

Verify before quoting as gospel​

  • The json.@some.path "combined" operator (scalar-or-nested) appeared in the newjson page but was not independently confirmed - verify against the live page.
  • max_dynamic_paths=1024 / max_dynamic_types=32 defaults and the local/remote sizing guidance shift across releases - check the server version.
  • The exact Object('json') removal release (v25.11) came from release-call material, not the data-type page - confirm against the upgrade target's changelog.

Sources (ClickHouse docs, 2026-08-20)​