# 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

```text
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

```sql
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"}');
```

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

```text
42,12.50,12.50
```

### Defaults for missing paths

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

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

```text
0,\N
```

### Plain JSON arrays and scalars

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

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

See [JSON functions](https://coordinatordb.com/docs/reference/functions/regular-functions/json-functions) for extraction and transformation, and [JSON data formats](https://coordinatordb.com/docs/guides/clickhouse/data-formats/json/formats) for importing and exporting documents.