JSON Data Type

### Limitations[​](#lazy-type-hints-limitations "Direct link to Limitations")

- This feature is experimental and may change in future versions
- Query-time type conversion can have significant performance overhead compared to pre-materialized types, especially for large JSON objects
- The feature only applies when modifying `typed_paths` (type hints); other JSON parameters like `max_dynamic_paths`, `SKIP`, or `SKIP REGEXP` still require mutations

## Comparison between values of the JSON type[​](#comparison-between-values-of-the-json-type "Direct link to Comparison between values of the JSON type")

JSON objects are compared similarly to Maps.

For example:

```
CREATE TABLE test (json1 JSON, json2 JSON) ENGINE=Memory;
INSERT INTO test FORMAT JSONEachRow
{"json1" : {}, "json2" : {}}
{"json1" : {"a" : 42}, "json2" : {}}
{"json1" : {"a" : 42}, "json2" : {"a" : 41}}
{"json1" : {"a" : 42}, "json2" : {"a" : 42}}
{"json1" : {"a" : 42}, "json2" : {"a" : [1, 2, 3]}}
{"json1" : {"a" : 42}, "json2" : {"a" : "Hello"}}
{"json1" : {"a" : 42}, "json2" : {"b" : 42}}
{"json1" : {"a" : 42}, "json2" : {"a" : 42, "b" : 42}}
{"json1" : {"a" : 42}, "json2" : {"a" : 41, "b" : 42}}

SELECT json1, json2, json1 < json2, json1 = json2, json1 > json2 FROM test;
```

```
┌─json1──────┬─json2───────────────┬─less(json1, json2)─┬─equals(json1, json2)─┬─greater(json1, json2)─┐
│ {}         │ {}                  │                  0 │                    1 │                     0 │
│ {"a":"42"} │ {}                  │                  0 │                    0 │                     1 │
│ {"a":"42"} │ {"a":"41"}          │                  0 │                    0 │                     1 │
│ {"a":"42"} │ {"a":"42"}          │                  0 │                    1 │                     0 │
│ {"a":"42"} │ {"a":["1","2","3"]} │                  0 │                    0 │                     1 │
│ {"a":"42"} │ {"a":"Hello"}       │                  1 │                    0 │                     0 │
│ {"a":"42"} │ {"b":"42"}          │                  1 │                    0 │                     0 │
│ {"a":"42"} │ {"a":"42","b":"42"} │                  1 │                    0 │                     0 │
│ {"a":"42"} │ {"a":"41","b":"42"} │                  0 │                    0 │                     1 │
└────────────┴─────────────────────┴────────────────────┴──────────────────────┴───────────────────────┘
```

**Note:** when 2 paths contain values of different data types, they are compared according to [comparison rule](/docs/sql-reference/data-types/variant#comparing-values-of-variant-data) of `Variant` data type.

## Data skipping indexes for JSON[​](#data-skipping-indexes-for-json "Direct link to Data skipping indexes for JSON")

[Data skipping indexes](/docs/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-data_skipping-indexes) can be used with `JSON` columns in three ways:

1. **Indexes on specific subcolumns** — create a standard skip index on a known JSON path, just like on a regular column. This indexes the *values* at that path.
2. **Path-based indexes with `JSONAllPaths`** — index the *set of paths* present in each granule to skip granules that cannot contain the queried path.
3. **Value-based indexes with `JSONAllValues`** — index *all values* across all JSON paths using a [text index](/docs/engines/table-engines/mergetree-family/textindexes) to accelerate full-text search on any JSON subcolumn with a single index.

### Indexes on specific subcolumns[​](#json-indexes-on-subcolumns "Direct link to Indexes on specific subcolumns")

You can create a skip index on any JSON subcolumn using the same syntax as for regular columns.
Any [supported index type](/docs/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-data_skipping-indexes) works (`minmax`, `set`, `bloom_filter`, `tokenbf_v1`, `ngrambf_v1`, etc.).

There are two ways to reference a JSON subcolumn in an index expression:

- **Typed path** declared in the JSON type hint — access by name directly: `json.a`.
- **Dynamic path** with explicit cast — use the `::` cast syntax: `json.b::String`.

You can also use expressions that combine multiple subcolumns, for example `json.a || json.b::String`.

#### Example[​](#json-indexes-on-subcolumns-example "Direct link to Example")

```
CREATE TABLE sensor_data
(
    data JSON(sensor_id UInt32),
    INDEX idx_sensor data.sensor_id TYPE minmax GRANULARITY 1,
    INDEX idx_location data.location::String TYPE bloom_filter GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 1;

INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', number, 'location', 'room_' || toString(number))) FROM numbers(4);
INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', number, 'location', 'room_' || toString(number))) FROM numbers(4, 4);
```

The `minmax` index on the typed subcolumn `data.sensor_id` narrows the scan to matching granules:

```
EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.sensor_id < 2;
```

```
...
    Indexes:
      Skip
        Name: idx_sensor
        Description: minmax GRANULARITY 1
        Parts: 1/2
        Granules: 2/8
```

The `bloom_filter` index on the cast subcolumn `data.location::String` also works:

```
EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.location::String = 'room_5';
```

```
...
    Indexes:
      Skip
        Name: idx_location
        Description: bloom_filter GRANULARITY 1
        Parts: 1/2
        Granules: 1/8
```

### Path-based indexes with JSONAllPaths[​](#json-indexes-jsonallpaths "Direct link to Path-based indexes with JSONAllPaths")

[Data skipping indexes](/docs/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-data_skipping-indexes) can also be created on `JSON` columns using the [`JSONAllPaths`](/docs/sql-reference/functions/json-functions#JSONAllPaths) function.
This works similarly to creating skip indexes on [`Map`](/docs/sql-reference/data-types/map) columns via `mapKeys` — the index stores the set of JSON paths present in each granule and uses it to skip granules that cannot contain the queried path.

#### Supported index types[​](#json-indexes-jsonallpaths-supported-types "Direct link to Supported index types")

`JSONAllPaths` can be used with the following skip index types:

- [`bloom_filter`](/docs/engines/table-engines/mergetree-family/mergetree#bloom-filter) — supports `equals`, `in`, and `IS NOT NULL`.
- [`tokenbf_v1`](/docs/engines/table-engines/mergetree-family/mergetree#token-bloom-filter) — supports `equals` and `IS NOT NULL`.
- [`ngrambf_v1`](/docs/engines/table-engines/mergetree-family/mergetree#n-gram-bloom-filter) — supports `equals` and `IS NOT NULL`.
- [`text`](/docs/engines/table-engines/mergetree-family/textindexes) (inverted index) — supports `equals`, `in` and `IS NOT NULL`.

#### Example[​](#json-indexes-jsonallpaths-example "Direct link to Example")

```
CREATE TABLE events
(
    data JSON,
    INDEX idx JSONAllPaths(data) TYPE bloom_filter GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO events VALUES ('{"user": {"name": "Alice"}, "action": "login"}');
INSERT INTO events VALUES ('{"metric": {"cpu": 0.95}, "host": "srv1"}');
```

You can use `EXPLAIN indexes = 1` to verify that the skip index is being used. When a path exists only in one part, the index skips the other part:

```
EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name = 'Alice';
```

```
...
    Indexes:
      Skip
        Name: idx
        Description: bloom_filter GRANULARITY 1
        Parts: 1/2
        Granules: 1/2
```

When a path does not exist in any part, all parts and granules are skipped:

```
EXPLAIN indexes = 1 SELECT * FROM events WHERE data.nonexistent = 1;
```

```
...
    Indexes:
      Skip
        Name: idx
        Description: bloom_filter GRANULARITY 1
        Parts: 0/2
        Granules: 0/2
```

`IS NOT NULL` also uses the index — it skips granules where the path is absent (since the value would be `NULL`):

```
EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name IS NOT NULL;
```