title_wip

Let's see what happens when the limit is reached in different scenarios.

### Reaching the limit during data parsing[​](#reaching-the-limit-during-data-parsing "Direct link to Reaching the limit during data parsing")

During parsing of `Dynamic` values from the data, when the limit is reached for current block of data, all new values will be inserted into shared data structure:

```
SELECT d, dynamicType(d), isDynamicElementInSharedData(d) FROM format(JSONEachRow, 'd Dynamic(max_types=3)', '
{"d" : 42}
{"d" : [1, 2, 3]}
{"d" : "Hello, World!"}
{"d" : "2020-01-01"}
{"d" : ["str1", "str2", "str3"]}
{"d" : {"a" : 1, "b" : [1, 2, 3]}}
')
```

```
┌─d──────────────────────┬─dynamicType(d)─────────────────┬─isDynamicElementInSharedData(d)─┐
│ 42                     │ Int64                          │ false                           │
│ [1,2,3]                │ Array(Int64)                   │ false                           │
│ Hello, World!          │ String                         │ false                           │
│ 2020-01-01             │ Date                           │ true                            │
│ ['str1','str2','str3'] │ Array(String)                  │ true                            │
│ (1,[1,2,3])            │ Tuple(a Int64, b Array(Int64)) │ true                            │
└────────────────────────┴────────────────────────────────┴─────────────────────────────────┘
```

As we can see, after inserting 3 different data types `Int64`, `Array(Int64)` and `String` all new types were inserted into special 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 merge of several data parts in MergeTree table the `Dynamic` column in the resulting data part can reach the limit of different data types that can be stored in separate subcolumns inside and won't be able to store all types as subcolumns from source parts.
In this case ClickHouse chooses what types will remain as separate subcolumns after merge and what types will be inserted into shared data structure. In most cases ClickHouse tries to keep the most frequent types and store the rarest types in shared data structure, but it depends on the implementation.

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

```
CREATE TABLE test (id UInt64, d Dynamic(max_types=3)) ENGINE=MergeTree ORDER BY id;
SYSTEM STOP MERGES test;
INSERT INTO test SELECT number, number FROM numbers(5);
INSERT INTO test SELECT number, range(number) FROM numbers(4);
INSERT INTO test SELECT number, toDate(number) FROM numbers(3);
INSERT INTO test SELECT number, map(number, number) FROM numbers(2);
INSERT INTO test SELECT number, 'str_' || toString(number) FROM numbers(1);
```

Each insert will create a separate data pert with `Dynamic` column containing single type:

```
SELECT count(), dynamicType(d), isDynamicElementInSharedData(d), _part FROM test GROUP BY _part, dynamicType(d), isDynamicElementInSharedData(d) ORDER BY _part, count();
```

```
┌─count()─┬─dynamicType(d)──────┬─isDynamicElementInSharedData(d)─┬─_part─────┐
│       5 │ UInt64              │ false                           │ all_1_1_0 │
│       4 │ Array(UInt64)       │ false                           │ all_2_2_0 │
│       3 │ Date                │ false                           │ all_3_3_0 │
│       2 │ Map(UInt64, UInt64) │ false                           │ all_4_4_0 │
│       1 │ String              │ false                           │ all_5_5_0 │
└─────────┴─────────────────────┴─────────────────────────────────┴───────────┘
```

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

```
SYSTEM START MERGES test;
OPTIMIZE TABLE test FINAL;
SELECT count(), dynamicType(d), isDynamicElementInSharedData(d), _part FROM test GROUP BY _part, dynamicType(d), isDynamicElementInSharedData(d) ORDER BY _part, count() desc;
```

```
┌─count()─┬─dynamicType(d)──────┬─isDynamicElementInSharedData(d)─┬─_part─────┐
│       5 │ UInt64              │ false                           │ all_1_5_2 │
│       4 │ Array(UInt64)       │ false                           │ all_1_5_2 │
│       3 │ Date                │ false                           │ all_1_5_2 │
│       2 │ Map(UInt64, UInt64) │ true                            │ all_1_5_2 │
│       1 │ String              │ true                            │ all_1_5_2 │
└─────────┴─────────────────────┴─────────────────────────────────┴───────────┘
```

As we can see, ClickHouse kept the most frequent types `UInt64` and `Array(UInt64)` as subcolumns and inserted all other types into shared data.

## JSONExtract functions with Dynamic[​](#jsonextract-functions-with-dynamic "Direct link to JSONExtract functions with Dynamic")

All `JSONExtract*` functions support `Dynamic` type:

```
SELECT JSONExtract('{"a" : [1, 2, 3]}', 'a', 'Dynamic') AS dynamic, dynamicType(dynamic) AS dynamic_type;
```

```
┌─dynamic─┬─dynamic_type───────────┐
│ [1,2,3] │ Array(Nullable(Int64)) │
└─────────┴────────────────────────┘
```

```
SELECT JSONExtract('{"obj" : {"a" : 42, "b" : "Hello", "c" : [1,2,3]}}', 'obj', 'Map(String, Dynamic)') AS map_of_dynamics, mapApply((k, v) -> (k, dynamicType(v)), map_of_dynamics) AS map_of_dynamic_types
```

```
┌─map_of_dynamics──────────────────┬─map_of_dynamic_types────────────────────────────────────┐
│ {'a':42,'b':'Hello','c':[1,2,3]} │ {'a':'Int64','b':'String','c':'Array(Nullable(Int64))'} │
└──────────────────────────────────┴─────────────────────────────────────────────────────────┘
```

```
SELECT JSONExtractKeysAndValues('{"a" : 42, "b" : "Hello", "c" : [1,2,3]}', 'Dynamic') AS dynamics, arrayMap(x -> (x.1, dynamicType(x.2)), dynamics) AS dynamic_types```
```

```
┌─dynamics───────────────────────────────┬─dynamic_types─────────────────────────────────────────────────┐
│ [('a',42),('b','Hello'),('c',[1,2,3])] │ [('a','Int64'),('b','String'),('c','Array(Nullable(Int64))')] │
└────────────────────────────────────────┴───────────────────────────────────────────────────────────────┘
```

### Binary output format[​](#binary-output-format "Direct link to Binary output format")

In RowBinary format values of `Dynamic` type are serialized in the following format:

```
<binary_encoded_data_type><value_in_binary_format_according_to_the_data_type>
```

- [Creating Dynamic](#creating-dynamic)- [Reading Dynamic nested types as subcolumns](#reading-dynamic-nested-types-as-subcolumns)- [Conversion between Dynamic column and other columns](#conversion-between-dynamic-column-and-other-columns)
      - [Converting an ordinary column to a Dynamic column](#converting-an-ordinary-column-to-a-dynamic-column)- [Converting a String column to a Dynamic column through parsing](#converting-a-string-column-to-a-dynamic-column-through-parsing)- [Converting a Dynamic column to an ordinary column](#converting-a-dynamic-column-to-an-ordinary-column)- [Converting a Variant column to Dynamic column](#converting-a-variant-column-to-dynamic-column)- [Converting a Dynamic(max\_types=N) column to another Dynamic(max\_types=K)](#converting-a-dynamicmax_typesn-column-to-another-dynamicmax_typesk)- [Reading Dynamic type from the data](#reading-dynamic-type-from-the-data)- [Using Dynamic type in functions](#using-dynamic-type-in-functions)- [Using Dynamic type in ORDER BY and GROUP BY](#using-dynamic-type-in-order-by-and-group-by)- [Reaching the limit in number of different data types stored inside Dynamic](#reaching-the-limit-in-number-of-different-data-types-stored-inside-dynamic)
              - [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)- [JSONExtract functions with Dynamic](#jsonextract-functions-with-dynamic)
                - [Binary output format](#binary-output-format)

Was this page helpful?