JSON Data Type

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

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

#### How it works[​](#json-indexes-jsonallpaths-how-it-works "Direct link to How it works")

The `JSONAllPaths(json_column)` expression produces an `Array(String)` containing all paths present in a JSON value.
The skip index stores these path strings in its data structure (bloom filter or inverted index).
When a query filters on `json.some.path`, the index checks whether the string `"some.path"` is present in the index for each granule and skips granules where it is absent.

#### Safety with missing paths[​](#json-indexes-jsonallpaths-safety-with-missing-paths "Direct link to Safety with missing paths")

When a JSON path is absent from a granule, the subcolumn evaluates to:

- `NULL` for `Dynamic` type (e.g., `json.path`) and `Nullable` typed subcolumns (e.g., `json.path.:Int64`) — comparisons with `NULL` always return false, so skipping is safe.
- The type's default value for non-`Nullable` CAST expressions (e.g., `json.path::Int64` produces `0` when the path is missing) — skipping is safe only when the compared value differs from the default. The index automatically handles this distinction.

### Full-text search with JSONAllValues[​](#json-indexes-jsonallvalues "Direct link to Full-text search with JSONAllValues")

[Text indexes](/docs/engines/table-engines/mergetree-family/textindexes) can be used to accelerate full-text search on JSON columns via the [`JSONAllValues`](/docs/sql-reference/functions/json-functions#JSONAllValues) function.
`JSONAllValues` returns all values from a JSON column as `Array(String)`, which can be indexed by a text index.
A single index on `JSONAllValues(json_column)` covers all JSON paths, enabling full-text search on any subcolumn without creating separate indexes for each path.

See [Value-based indexes with JSONAllValues](/docs/engines/table-engines/mergetree-family/textindexes#json-indexes-jsonallvalues) in the text indexes documentation for details and examples.

## Tips for better usage of the JSON type[​](#tips-for-better-usage-of-the-json-type "Direct link to Tips for better usage of the JSON type")

Before creating `JSON` column and loading data into it, consider the following tips:

- Investigate your data and specify as many path hints with types as you can. It will make storage and reading much more efficient.
- Think about what paths you will need and what paths you will never need. Specify paths that you won't need in the `SKIP` section, and `SKIP REGEXP` section if needed. This will improve the storage.
- Don't set the `max_dynamic_paths` parameter to very high values, as it can make storage and reading less efficient.
  While highly dependent on system parameters such as memory, CPU, etc., a general rule of thumb would be to not set `max_dynamic_paths` greater than 10 000 for the local filesystem storage and 1024 for the remote filesystem storage.

## Further Reading[​](#further-reading "Direct link to Further Reading")

- [How we built a new powerful JSON data type for ClickHouse](https://clickhouse.com/blog/a-new-powerful-json-data-type-for-clickhouse)
- [The billion docs JSON Challenge: ClickHouse vs. MongoDB, Elasticsearch, and more](https://clickhouse.com/blog/json-bench-clickhouse-vs-mongodb-elasticsearch-duckdb-postgresql)

- [When to use the `JSON` Type](#when-to-use-json-type)
  - [Use the `JSON` type when:](#use-json-type)- [Use a `String` column (or structured types) when:](#use-string-type)- [Creating `JSON`](#creating-json)
    - [Using `JSON` in a table column definition](#using-json-in-a-table-column-definition)- [Using CAST with `::JSON`](#using-cast-with-json)- [Reading JSON paths as sub-columns](#reading-json-paths-as-sub-columns)- [Reading JSON sub-objects as sub-columns](#reading-json-sub-objects-as-sub-columns)- [Reading JSON combined sub-columns](#reading-json-combined-sub-columns)- [Type inference for paths](#type-inference-for-paths)- [Handling arrays of JSON objects](#handling-arrays-of-json-objects)- [Handling JSON keys with NULL](#handling-json-keys-with-nulls)- [Handling JSON keys with dots](#handling-json-keys-with-dots)- [Reading JSON type from data](#reading-json-type-from-data)- [Reaching the limit of dynamic paths inside JSON](#reaching-the-limit-of-dynamic-paths-inside-json)
                      - [Reaching the limit during data parsing](#reaching-the-limit-during-data-parsing)- [During merges of data parts in MergeTree table engines](#during-merges-of-data-parts-in-mergetree-table-engines)- [Shared data structure](#shared-data-structure)
                        - [Shared data structure in memory](#shared-data-structure-in-memory)- [Shared data structure in MergeTree parts](#shared-data-structure-in-merge-tree-parts)- [Controlling the number of dynamic paths inside JSON in MergeTree parts](#controlling-the-number-of-dynamic-paths)- [Introspection functions](#introspection-functions)- [ALTER MODIFY COLUMN to JSON type](#alter-modify-column-to-json-type)- [Lazy Type Hints (Experimental)](#lazy-type-hints)
                                - [Enabling Lazy Type Hints](#enabling-lazy-type-hints)- [Example](#lazy-type-hints-example)- [Verifying No Mutation Occurred](#verifying-no-mutation-occurred)- [Materializing Type Hints](#materializing-type-hints)- [Limitations](#lazy-type-hints-limitations)- [Comparison between values of the JSON type](#comparison-between-values-of-the-json-type)- [Data skipping indexes for JSON](#data-skipping-indexes-for-json)
                                    - [Indexes on specific subcolumns](#json-indexes-on-subcolumns)- [Path-based indexes with JSONAllPaths](#json-indexes-jsonallpaths)- [Full-text search with JSONAllValues](#json-indexes-jsonallvalues)- [Tips for better usage of the JSON type](#tips-for-better-usage-of-the-json-type)- [Further Reading](#further-reading)

Was this page helpful?