JSON Data Type

As we can see, after inserting paths `e` and `f.g` the limit was reached,
and they got inserted into a shared data structure.

### During merges of data parts in MergeTree table engines[​](#during-merges-of-data-parts-in-mergetree-table-engines "Direct link to During merges of data parts in MergeTree table engines")

During a merge of several data parts in a `MergeTree` table the `JSON` column in the resulting data part can reach the limit of dynamic paths
and won't be able to store all paths from source parts as sub-columns.
In this case, ClickHouse chooses what paths will remain as sub-columns after merge and what paths will be stored in the shared data structure.
In most cases, ClickHouse tries to keep paths that contain
the largest number of non-null values and move the rarest paths to the shared data structure. This does, however, depend on the implementation.

Let's see an example of such a merge.
First, let's create a table with a `JSON` column, set the limit of dynamic paths to `3` and then insert values with `5` different paths:

```
CREATE TABLE test (id UInt64, json JSON(max_dynamic_paths=3)) ENGINE=MergeTree ORDER BY id;
SYSTEM STOP MERGES test;
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as a) FROM numbers(5);
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as b) FROM numbers(4);
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as c) FROM numbers(3);
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as d) FROM numbers(2);
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as e) FROM numbers(1);
```

Each insert will create a separate data part with the `JSON` column containing a single path:

```
SELECT
    count(),
    groupArrayArrayDistinct(JSONDynamicPaths(json)) AS dynamic_paths,
    groupArrayArrayDistinct(JSONSharedDataPaths(json)) AS shared_data_paths,
    _part
FROM test
GROUP BY _part
ORDER BY _part ASC
```

```
┌─count()─┬─dynamic_paths─┬─shared_data_paths─┬─_part─────┐
│       5 │ ['a']         │ []                │ all_1_1_0 │
│       4 │ ['b']         │ []                │ all_2_2_0 │
│       3 │ ['c']         │ []                │ all_3_3_0 │
│       2 │ ['d']         │ []                │ all_4_4_0 │
│       1 │ ['e']         │ []                │ all_5_5_0 │
└─────────┴───────────────┴───────────────────┴───────────┘
```

Now, let's merge all parts into one and see what will happen:

```
SELECT
    count(),
    groupArrayArrayDistinct(JSONDynamicPaths(json)) AS dynamic_paths,
    groupArrayArrayDistinct(JSONSharedDataPaths(json)) AS shared_data_paths,
    _part
FROM test
GROUP BY _part
ORDER BY _part ASC
```

```
┌─count()─┬─dynamic_paths─┬─shared_data_paths─┬─_part─────┐
│      15 │ ['a','b','c'] │ ['d','e']         │ all_1_5_2 │
└─────────┴───────────────┴───────────────────┴───────────┘
```

As we can see, ClickHouse kept the most frequent paths `a`, `b` and `c` and moved paths `d` and `e` to a shared data structure.

## Shared data structure[​](#shared-data-structure "Direct link to Shared data structure")

As was described in the previous section, when the `max_dynamic_paths` limit is reached all new paths are stored in a single shared data structure.
In this section we will look into the details of the shared data structure and how we read paths sub-columns from it.

See section ["introspection functions"](/docs/sql-reference/data-types/newjson#introspection-functions) for details of functions used for inspecting the contents of a JSON column.

### Shared data structure in memory[​](#shared-data-structure-in-memory "Direct link to Shared data structure in memory")

In memory, shared data structure is just a sub-column with type `Map(String, String)` that stores mapping from a flattened JSON path to a binary encoded value.
To extract a path subcolumn from it, we just iterate over all rows in this `Map` column and try to find the requested path and its values.

### Shared data structure in MergeTree parts[​](#shared-data-structure-in-merge-tree-parts "Direct link to Shared data structure in MergeTree parts")

In [MergeTree](/docs/engines/table-engines/mergetree-family/mergetree) tables we store data in data parts that stores everything on disk (local or remote). And data on disk can be stored in a different way compared to memory.
Currently, there are 3 different shared data structure serializations in MergeTree data parts: `map`, `map_with_buckets`
and `advanced`.

The serialization version is controlled by MergeTree
settings [object\_shared\_data\_serialization\_version](/docs/operations/settings/merge-tree-settings#object_shared_data_serialization_version)
and [object\_shared\_data\_serialization\_version\_for\_zero\_level\_parts](/docs/operations/settings/merge-tree-settings#object_shared_data_serialization_version_for_zero_level_parts)
(zero level part is the part created during inserting data into the table, during merges parts have higher level).

Note: changing shared data structure serialization is supported only
for `v3` [object serialization version](/docs/operations/settings/merge-tree-settings#object_serialization_version)

#### Map[​](#shared-data-map "Direct link to Map")

In `map` serialization version shared data is serialized as a single column with type `Map(String, String)` the same as it's stored in
memory. To read path sub-column from this type of serialization ClickHouse reads the whole `Map` column and
extracts the requested path in memory.

This serialization is efficient for writing data and reading the whole `JSON` column, but it's not efficient for reading paths sub-columns.

#### Map with buckets[​](#shared-data-map-with-buckets "Direct link to Map with buckets")

In `map_with_buckets` serialization version shared data is serialized as `N` columns ("buckets") with type `Map(String, String)`.
Each such bucket contains only subset of paths. To read path sub-column from this type of serialization ClickHouse
reads the whole `Map` column from a single bucket and extracts the requested path in memory.

This serialization is less efficient for writing data and reading the whole `JSON` column, but it's more efficient for reading paths sub-columns
because it reads data only from required buckets.

Number of buckets `N` is controlled by MergeTree settings [object\_shared\_data\_buckets\_for\_compact\_part](/docs/operations/settings/merge-tree-settings#object_shared_data_buckets_for_compact_part) (8 by default)
and [object\_shared\_data\_buckets\_for\_wide\_part](/docs/operations/settings/merge-tree-settings#object_shared_data_buckets_for_wide_part) (32 by default).
The maximum allowed value for both settings is 256.

#### Advanced[​](#shared-data-advanced "Direct link to Advanced")

In `advanced` serialization version shared data is serialized in a special data structure that maximizes the performance
of paths sub-columns reading by storing some additional information that allows to read only the data of requested paths.
This serialization also supports buckets, so each bucket contains only sub-set of paths.

This serialization is quite inefficient for writing data (so it's not recommended to use this serialization for zero-level parts), reading the whole `JSON` column is slightly less efficient compared to `map` serialization, but it's very efficient for reading paths sub-columns.

Note: because of storing some additional information inside the data structure, the disk storage size is higher with this serialization compared to
`map` and `map_with_buckets` serializations.

For more detailed overview of the new shared data serializations and implementation details read the [blog post](https://clickhouse.com/blog/json-data-type-gets-even-better).

## Controlling the number of dynamic paths inside JSON in MergeTree parts[​](#controlling-the-number-of-dynamic-paths "Direct link to Controlling the number of dynamic paths inside JSON in MergeTree parts")