JSON ​

JSON stores a validated JSON document. Plain JSON accepts objects, arrays and scalar documents. A JSON type with declared paths requires an object and validates those paths against their declared native types.

Use typed paths for fields with a known contract while retaining the other fields of the document.

Creating JSON ​

JSON
JSON(path1 T1, path2 T2, ...)

Type inference for paths ​

Path names are case-sensitive. Dots describe nested object paths, such as details.price. Paths cannot be repeated or overlap a declared parent path.

Missing paths and explicit JSON nulls receive the declared type's default. A Nullable path retains NULL. A scalar value where a parent object is required is rejected. Undeclared members remain in the document.

Typed values are checked on inserts, updates, casts, defaults and generated values. JSON storage options such as SKIP and dynamic-subcolumn limits are not supported.

Reading JSON paths as sub-columns ​

Use dotted column access, such as doc.user_id or doc.details.price, or get_subcolumn(doc, 'details.price').

Declared paths return their declared native types. Undeclared paths return Dynamic values, and missing undeclared paths return NULL.

Examples ​

Creating a JSON column with typed paths ​

CREATE TABLE events
(
    id UInt32,
    doc JSON(user_id UInt64, details.price Decimal(18, 2))
);
INSERT INTO events VALUES
    (1, '{"user_id":42,"details":{"price":"12.50"},"action":"purchase"}');

SELECT doc.user_id, doc.details.price,
       get_subcolumn(doc, 'details.price') AS price
FROM events
FORMAT csv;

42,12.50,12.50

Defaults for missing paths ​

CREATE TABLE profiles(doc JSON(id UInt32, nickname Nullable(String)));
INSERT INTO profiles VALUES ('{}');

SELECT doc.id, doc.nickname
FROM profiles
FORMAT csv;

0,\N

Plain JSON arrays and scalars ​

SELECT CAST('[1,true,null]' AS JSON), CAST('42' AS JSON)
FORMAT csv;

"[1,true,null]","42"

See JSON functions for extraction and transformation, and JSON data formats for importing and exporting documents.