Designing your schema

2 rows in set. Elapsed: 0.004 sec.
```

#### Optimizing performance with type hints[​](#optimizing-performance-with-type-hints "Direct link to Optimizing performance with type hints")

Type hints offer more than just a way to avoid unnecessary type inference - they eliminate storage and processing indirection entirely, as well as allowing [optimal primitive types](/docs/data-modeling/schema-design#optimizing-types) to be specified. JSON paths with type hints are always stored just like traditional columns, bypassing the need for [**discriminator columns**](https://clickhouse.com/blog/a-new-powerful-json-data-type-for-clickhouse#storage-extension-for-dynamically-changing-data) or dynamic resolution during query time.

This means that with well-defined type hints, nested JSON keys achieve the same performance and efficiency as if they were modeled as top-level columns from the outset.

As a result, for datasets that are mostly consistent but still benefit from the flexibility of JSON, type hints provide a convenient way to preserve performance without needing to restructure your schema or ingest pipeline.

### Configuring dynamic paths[​](#configuring-dynamic-paths "Direct link to Configuring dynamic paths")

ClickHouse stores each JSON path as a subcolumn in a true columnar layout, enabling the same performance benefits seen with traditional columns—such as compression, SIMD-accelerated processing, and minimal disk I/O. Each unique path and type combination in your JSON data can become its own column file on disk.

For example, when two JSON paths are inserted with differing types, ClickHouse stores the values of each [concrete type in distinct sub-columns](https://clickhouse.com/blog/a-new-powerful-json-data-type-for-clickhouse#storage-extension-for-dynamically-changing-data). These sub-columns can be accessed independently, minimizing unnecessary I/O. Note that when querying a column with multiple types, its values are still returned as a single columnar response.

Additionally, by leveraging offsets, ClickHouse ensures that these sub-columns remain dense, with no default values stored for absent JSON paths. This approach maximizes compression and further reduces I/O.

However, in scenarios with high-cardinality or highly variable JSON structures—such as telemetry pipelines, logs, or machine-learning feature stores - this behavior can lead to an explosion of column files. Each new unique JSON path results in a new column file, and each type variant under that path results in an additional column file. While this is optimal for read performance, it introduces operational challenges: file descriptor exhaustion, increased memory usage, and slower merges due to a high number of small files.

To mitigate this, ClickHouse introduces the concept of an overflow subcolumn: once the number of distinct JSON paths exceeds a threshold, additional paths are stored in a single shared file using a compact encoded format. This file is still queryable but doesn't benefit from the same performance characteristics as dedicated subcolumns.

This threshold is controlled by the [`max_dynamic_paths`](/docs/sql-reference/data-types/newjson#reaching-the-limit-of-dynamic-paths-inside-json) parameter in the JSON type declaration.

```
CREATE TABLE logs
(
    payload JSON(max_dynamic_paths = 500)
)
ENGINE = MergeTree
ORDER BY tuple();
```

**Avoid setting this parameter too high** - large values increase resource consumption and reduce efficiency. As a rule of thumb, keep it below 10,000. For workloads with highly dynamic structures, use type hints and `SKIP` parameters to restrict what's stored.

For users curious about the implementation of this new column type, we recommend reading our detailed blog post ["A New Powerful JSON Data Type for ClickHouse"](https://clickhouse.com/blog/a-new-powerful-json-data-type-for-clickhouse).

- [Static vs dynamic JSON](#static-vs-dynamic-json)- [Handling static structures](#handling-static-structures)
    - [Handling default values](#handling-default-values)- [Handling new columns](#handling-new-columns)- [Handling semi-structured/dynamic structures](#handling-semi-structured-dynamic-structures)
      - [Single JSON column](#single-json-column)- [Targeted JSON column](#targeted-json-column)- [Using type hints and skipping paths](#using-type-hints-and-skipping-paths)- [Configuring dynamic paths](#configuring-dynamic-paths)

Was this page helpful?