In August 2024, ClickHouse v24.8 introduced a [powerful **JSON data type** to ClickHouse](https://clickhouse.com/docs/sql-reference/data-types/newjson). Since then, we’ve continuously improved it with new features and optimizations.

In this post, we’ll show how **we’ve just made it even better** in ClickHouse v25.8. 

ClickHouse is already the industry leader in analytical performance, and **the latest changes in v25.8 make ClickHouse the leader in analytics over JSON data**, too.

## How the JSON type worked in v24.8

Let’s start with a quick recap of [how JSON data is stored in MergeTree parts](https://clickhouse.com/blog/a-new-powerful-json-data-type-for-clickhouse) as designed in ClickHouse v24.8.

In the illustration below, we have JSON data with K unique paths with string values. The first **N paths** are stored as subcolumns in *dynamic paths*. All remaining **K – N paths** are stored in a *shared data* structure, which is represented as a `Map(String, String)` column containing both paths and values.

![json_type_update_image1.png](/uploads/json_type_update_image1_9e5f1ce744.png)

For example, if we query the data of the path `key1`, ClickHouse first checks metadata to see whether `key1` is in *dynamic paths* or *shared data*. In this example path `key1` is in *dynamic paths*, so its values are stored in dedicated data files, which can be read directly and efficiently.

![json_type_update_image2.png](/uploads/json_type_update_image2_97f19ab52d.png)

Now, if we query `key_n+1`, the metadata shows that it’s *not* part of *dynamic paths*. Instead, it resides in *shared data*. To extract its values, ClickHouse must read the **entire** `Map(String, String)` column and filter it in memory. This is much less efficient.

![json_type_update_image3.png](/uploads/json_type_update_image3_cee4fc8cc1.png)

By default, the limit on dynamic paths is **1024**. Raising this limit to handle more paths is usually a bad idea, especially when using remote storage like S3, because creating thousands of files per part increases memory usage during merges and complicates reads. 

As a result, workloads with thousands or tens of thousands of unique JSON paths suffer from poor performance.

This is the challenge we set out to solve.

## New serializations for shared data in v25.8

**ClickHouse v25.8** introduces [two new serialization formats for *shared data*](https://github.com/ClickHouse/ClickHouse/pull/83777) that dramatically improve the efficiency of reading specific paths.

1. ### Bucketed shared data

The first new serialization splits *shared data* into **N buckets**. Each bucket contains its own `Map(String, String)` column, with a deterministic assignment of paths to buckets.

![json_type_update_image4.png](/uploads/json_type_update_image4_16ebb253ac.png)

When querying a path like `key_m1`, ClickHouse can immediately determine which bucket contains the requested path. Only that bucket is read, while the rest are skipped.

![json_type_update_image5.png](/uploads/json_type_update_image5_3b8dfc637c.png)

This **reduces the amount of data scanned and improves performance** compared to reading the entire *shared data*. However, increasing the number of buckets too far creates too many files and reintroduces file-system overhead.

2. ### Advanced shared data

The second and more powerful approach is the **advanced serialization format**.

Here, each bucket contains 3 files:

* **`.structure`** – metadata for each granule: number of rows, list of paths in the granule, and offset in the `.paths_marks` file.  
* **`.data`** – the actual path data stored in columnar format per granule.  
* **`.paths_marks`** – offsets pointing to the start of each path’s data in the `.data` file.

![json_type_update_image6.png](/uploads/json_type_update_image6_3da8af84a1.png)

When querying the path `key_m1` ,ClickHouse first checks the `.structure` file to see if the granule contains `key_m1`. If not, the granule is skipped entirely. If it does, ClickHouse looks up the offset in the `.paths_marks` file and jumps directly to the `key_m1` path’s data in the `.data` file and reads it.

![json_type_update_image7.png](/uploads/json_type_update_image7_935031e15e.png)

This **avoids reading the data of unrelated paths into memory and significantly improves query performance**.

### Supporting nested paths

Many JSON documents contain nested structures like arrays of objects. Extracting a nested path from such structures using the above approach still requires reading the entire array, which is inefficient for large payloads.

To address this, **the advanced format was extended with additional files for subcolumn handling**.

Here each bucket has 6 files:

* **`.structure`** – metadata for each granule: number of rows, list of paths in the granule and offsets in files ` .paths_marks` and ` .substreams_metadata`.  
* **`.data`** – the actual path data stored in columnar format per granule, but split into substreams. A single path may have multiple substreams. This structure allows ClickHouse to read only the substreams needed to reconstruct the requested subcolumn, rather than scanning the entire path value.  
* **`.paths_marks`** – offsets pointing to the start of each path’s data in the ` .data` file.
* **`.substreams_marks`** – offsets pointing to the start of each path’s substream in the `.data` file.  
* **`.substreams`** – list of substreams present for each path in a granule. This may differ across granules (e.g., some arrays may contain objects with different nested fields).  
* **`.substreams_metadata`** – for each path, stores offsets in the ` .substreams` and ` .substreams_marks` files, effectively linking a path to its subcolumns and their data locations.

![json_type_update_image8.png](/uploads/json_type_update_image8_0201ec1b33.png)

When querying the subcolumn of path `key_m1`, ClickHouse first reads data from the `.structure` file and checks if this granule contains the requested path `key_m1`. If not, the granule is skipped entirely. If it does, ClickHouse uses the offset stored in the ` .structure` file to read the corresponding entry in `.substreams_metadata` and obtain the offsets for `key_m1`. 

Using the first offset, ClickHouse then reads the list of substreams for this path in the granule. If this list does not include the substreams required for the requested subcolumn, the granule is skipped. If the required substreams are present, ClickHouse uses the second offset to read their positions from `.substreams_marks` and then reads only the data of those substreams from the `.data` file. 

After reconstructing the requested subcolumn, ClickHouse proceeds to the next granule.

![json_type_update_image9.png](/uploads/json_type_update_image9_8ad851edea.png)

This avoids reading the data of unrelated paths and unrelated substreams of the requested path in the granule in memory. This **significantly improves the performance of nested subcolumn reading**.

## Balancing efficiency and compatibility

The **advanced format is** **excellent for selective reads**, but it doesn’t come for free: **reading the entire JSON column or performing merges becomes slower**, because the in-memory representation of the shared data (`Map(String, String)`) is very different from its storage layout, resulting in expensive conversions during entire JSON column reading.

To address this, ClickHouse v25.8 makes a trade-off by **storing a copy of the data in the original format alongside the advanced one**. This effectively doubles the storage requirement, but you get all the benefits of the new format without the sacrifices noted above.

In the below illustration, you can see the copy is stored in three extra files:

* **`.copy.offsets`** – offsets of the original `Map(String, String)` column.  
* **`.copy.indexes`** –  indexes into the combined list of paths across buckets (avoids storing all paths again).  
* **`.copy.values`** - values of the original `Map(String, String)` column.

![json_type_update_image10.png](/uploads/json_type_update_image10_a9fd76daff.png)

This makes **reading full JSON columns and performing merges as fast as before**, while still **keeping the benefits of the advanced format for selective reads**.

## Performance

[We benchmarked the new serialization formats with JSON data containing **10, 100, 1000, and 10,000 unique paths**](https://github.com/ClickHouse/ClickHouse/pull/83777#issuecomment-3073272839).

The new advanced shared data serialization delivers **performance close to storing all paths as dynamic subcolumns** both in query speed and memory usage, **while scaling to tens of thousands of unique paths**.

Below we highlight some of the results for the 10,000 path tests, which demonstrate the improvements from the new serialization formats in v25.8.

### Performance test 1 (selective reads)

In this test, we compare reading a single JSON key from a table of 200k rows, each row containing a JSON document with 10k paths, using wide parts.

Using the **advanced serialization** format dramatically improves both time (~58x) and memory (~3,300x) performance compared to the original JSON serialization.

| Data type | Time (seconds) | Memory usage (MiB) |
| :---- | ----- | ----- |
| JSON no shared data                 | 0.027    | 18.64 MiB    |
| JSON shared data "advanced"         | 0.063    | 3.89 MiB     |
| JSON shared data "map_with_buckets" | 0.087    | 403.55 MiB   |
| String                              | 3.216    | 582.70 MiB   |
| Map                                 | 3.594    | 538.37 MiB   |
| JSON shared data "map"              | 3.63     | 12.53 GiB    |

### Performance test 2 (reading the whole JSON object)

In this test, we compare reading the full JSON document from a table of 200k rows, each row containing a JSON document with 10k paths, using wide parts.

Using the **advanced serialization** is practically equivalent to the original JSON serialization. This shows that the advanced serialization delivers huge gains to selective reads without sacrificing performance when reading the whole document.

| table_type | time_sec | memory_usage |
| :---- | ----- | ----- |
| String                              | 2.734    | 581.15 MiB   |
| Map                                 | 3.869    | 550.14 MiB   |
| JSON shared data "map"              | 4.182    | 618.78 MiB   |
| JSON shared data "advanced"         | 4.774    | 683.27 MiB   |
| JSON shared data "map_with_buckets" | 15.271   | 1.45 GiB     |
| JSON no shared data                 | -        | OOM          |

## Conclusion

The new *shared data* serialization takes JSON support in ClickHouse to the next level. You can now efficiently query JSON documents with tens of thousands of unique paths while maintaining excellent performance for selective reads. 

This enhancement makes ClickHouse an even stronger choice for workloads dealing with semi-structured JSON data at scale.

The latest changes to the JSON data type in v25.8 make ClickHouse the leader in analytics over JSON data.

large_json-data-type-better-banner.jpg

medium_json-data-type-better-banner.jpg

small_json-data-type-better-banner.jpg

thumbnail_json-data-type-better-banner.jpg

json-data-type-better-banner.jpg

Making complex JSON 58x faster, use 3,300x less memory, in ClickHouse

JSON は現代のデータシステムで半構造化および非構造化データを扱うための共通言語となっています。ロギングや[オブザーバビリティ](https://clickhouse.com/blog/the-state-of-sql-based-observability)のシナリオ、リアルタイムデータストリーミング、モバイルアプリのストレージ、機械学習のパイプラインにおいても、JSONの柔軟な構造により、分散システム間でデータをキャプチャし送信するための共通のフォーマットとなっています。

large_JSON blog_Japanese_large copy.png

medium_JSON blog_Japanese_large copy.png

small_JSON blog_Japanese_large copy.png

thumbnail_JSON blog_Japanese_large copy.png

JSON blog_Japanese_large copy.png

ClickHouseの新たな強力なJSONデータ型の開発プロセス

We’re excited to introduce our new and significantly enhanced JSON data type, purpose-built to deliver high-performance handling of JSON data. Our core engineer, Pavel Kruglov, dives into how we built this feature on top of ClickHouse's columnar storage.

large_JSON-header.png

medium_JSON-header.png

small_JSON-header.png

thumbnail_JSON-header.png

JSON-header.png

How we built a new powerful JSON data type for ClickHouse

![racing-cars.jpg](/uploads/large_racing_cars_69dec31eac.jpg)

## Introduction

At ClickHouse, we’re passionate about [benchmarks and performance](https://benchmark.clickhouse.com/). So when I first saw the Hacker News post “[The fastest tool for querying large JSON files is written in Python](https://news.ycombinator.com/item?id=31004563)”, my first thought was - "But clickhouse-local is not written in Python".  Let’s look at this benchmark and demonstrate that clickhouse-local is actually the fastest tool for querying large JSON files.

## clickhouse-local

[clickhouse-local](https://clickhouse.com/docs/en/operations/utilities/clickhouse-local/) is a single binary that enables you to perform fast processing on local and remote files using SQL - effectively database features without a database. As well as supporting the full breadth of [ClickHouse functions](https://clickhouse.com/docs/en/sql-reference/functions), JSON is also one of the many [supported file formats](https://clickhouse.com/docs/en/sql-reference/formats/).  Below we try to visualize the differences between a ClickHouse cluster, a single ClickHouse instance, and clickhouse–local:

![clickhouse-local.png](/uploads/clickhouse_local_8d3789c232.png)

## The Benchmark

Daniel Moura, the creator of SPySQL, posted a [benchmark](https://colab.research.google.com/github/dcmoura/spyql/blob/master/notebooks/json_benchmark.ipynb) as a part of the [SPySQL project](https://github.com/dcmoura/spyql). The benchmark compares several command-line tools with a focus on querying large files that fit into the disk of a standard machine but might not fit into memory.   A 10GB subset of the [Amazon book reviews dataset](http://deepyeti.ucsd.edu/jianmo/amazon) was used as the test dataset. The original list of tools used in the benchmark was SPySQL, jq, trdsql, Miller, OctoSQL, spqrk-sql, Pandas, and DSQ. The benchmark itself focused on 3 challenges:

* Map: a new column is calculated for all rows (batch input and output are large). This represents a common ETL-like task required for data cleansing and enrichment.
* Aggregation/Reduce: the average of all rows of a column is calculated (only the input is large). Useful to users who need a quick analytical answer and want to avoid the steps of loading the data into a data store such as ClickHouse.
* Subset/Filter: the first 100 values of a column matching filter criteria are returned (only a part of the input needs to be processed). The objective of this test is to assess the tool's ability to sample files quickly and represents a task users often perform before iterating on a query.

The first set of results for this benchmark showed SPySQL as the fastest tool for querying large JSON files:

![initial_results.png](/uploads/initial_results_cb29f85d92.png)

But Daniel was unaware of the clickhouse-local. To assist with this task, ClickHouse recently introduced two cool features that make it easy and straightforward to process JSON files and reproduce this benchmark: support for semi-structured data storage and automatic schema inference.  The latter feature allows ClickHouse to infer the types of columns from the data itself so that the user is not required to specify the structure of JSON files and the type of each field: simplifying the syntax and accelerating the getting started experience. 

After contacting  Daniel, he promptly added clickhouse-local to his benchmarks and updated the results. To his surprise, clickhouse-local was faster than all previous tools. One of the developers of OctoSQL (written in Go) also asked to update the benchmark in accordance with the latest improvements with good results. Below we show the updated benchmark results.

### Map challenge

![map.png](/uploads/map_e32a6f03c0.png)

**Results for 1GB of data**

![map-results.png](/uploads/map_results_08327d3dc9.png)

### Aggregation (reduce) challenge

![reduce.png](/uploads/reduce_b0461ed1d5.png)

**Results for 1GB of data**

![reduce-results.png](/uploads/reduce_results_53b0cd1005.png)

### Filter (subset) challenge

![subset.png](/uploads/subset_a61734b754.png)

**Results for 1GB of data**

![subset-results.png](/uploads/subset_results_3203932782.png)

## Results summary

![results.png](/uploads/results_332d851de4.png)

The updated results have been posted [here](https://www.reddit.com/r/programming/comments/u98qtz/the_fastest_commandline_tools_for_querying_large/). As we can see, clickhouse-local is much faster than most other tools for querying large JSON files, whereas OctoSQL excels on smaller files.  

This benchmark is not perfect. Each query is run only once, so fluctuations are possible, and users reproducing the results on local hardware may experience noticeable differences between runs. Results may also vary as a result of hardware and operating system differences. Finally, Daniel was deliberate in his approach to not have ORDER BY clauses in filter queries. Although this might mean results are different between tools, as SQL doesn’t enforce a default order, the objective of the benchmark is users wanting to sample files as quickly as possible and the ability of the tool to avoid a full scan. This test gives an advantage to tools that support early termination once satisfying the LIMIT, and those don’t need to load the entire file into memory.

So next time you need to process large JSON files, you know which tool to use! 

The world’s fastest command-line tool for querying JSON files? clickhouse-local.

Pavel Kruglov

Blog posts by Pavel Kruglov

Making complex JSON 58x faster, use 3,300x less memory, in ClickHouse

ClickHouseの新たな強力なJSONデータ型の開発プロセス

How we built a new powerful JSON data type for ClickHouse

The world’s fastest tool for querying JSON files