Map(K, V)

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/data-types/map.md)

Data type `Map(K, V)` stores key-value pairs.

Unlike other databases, maps are not unique in ClickHouse, i.e. a map can contain two elements with the same key.
(The reason for that is that maps are internally implemented as `Array(Tuple(K, V))`.)

You can use use syntax `m[k]` to obtain the value for key `k` in map `m`.
Also, `m[k]` scans the map, i.e. the runtime of the operation is linear in the size of the map.

**Parameters**

- `K` — The type of the Map keys. Arbitrary type except [Nullable](/docs/sql-reference/data-types/nullable) and [LowCardinality](/docs/sql-reference/data-types/lowcardinality) nested with [Nullable](/docs/sql-reference/data-types/nullable) types.
- `V` — The type of the Map values. Arbitrary type.

**Examples**

Create a table with a column of type map:

```
CREATE TABLE tab (m Map(String, UInt64)) ENGINE=Memory;
INSERT INTO tab VALUES ({'key1':1, 'key2':10}), ({'key1':2,'key2':20}), ({'key1':3,'key2':30});
```

To select `key2` values:

```
SELECT m['key2'] FROM tab;
```

Result:

```
┌─arrayElement(m, 'key2')─┐
│                      10 │
│                      20 │
│                      30 │
└─────────────────────────┘
```

If the requested key `k` is not contained in the map, `m[k]` returns the value type's default value, e.g. `0` for integer types and `''` for string types.
To check whether a key exists in a map, you can use function [mapContains](/docs/sql-reference/functions/tuple-map-functions#mapContainsKey).

```
CREATE TABLE tab (m Map(String, UInt64)) ENGINE=Memory;
INSERT INTO tab VALUES ({'key1':100}), ({});
SELECT m['key1'] FROM tab;
```

Result:

```
┌─arrayElement(m, 'key1')─┐
│                     100 │
│                       0 │
└─────────────────────────┘
```

## Converting Tuple to Map[​](#converting-tuple-to-map "Direct link to Converting Tuple to Map")

Values of type `Tuple()` can be cast to values of type `Map()` using function [CAST](/docs/sql-reference/functions/type-conversion-functions#CAST):

**Example**

Query:

```
SELECT CAST(([1, 2, 3], ['Ready', 'Steady', 'Go']), 'Map(UInt8, String)') AS map;
```

Result:

```
┌─map───────────────────────────┐
│ {1:'Ready',2:'Steady',3:'Go'} │
└───────────────────────────────┘
```

## Reading subcolumns of Map[​](#reading-subcolumns-of-map "Direct link to Reading subcolumns of Map")

To avoid reading the entire map, you can use subcolumns `keys` and `values` in some cases.

**Example**

Query:

```
CREATE TABLE tab (m Map(String, UInt64)) ENGINE = Memory;
INSERT INTO tab VALUES (map('key1', 1, 'key2', 2, 'key3', 3));

SELECT m.keys FROM tab; --   same as mapKeys(m)
SELECT m.values FROM tab; -- same as mapValues(m)
```

Result:

```
┌─m.keys─────────────────┐
│ ['key1','key2','key3'] │
└────────────────────────┘

┌─m.values─┐
│ [1,2,3]  │
└──────────┘
```

## Bucketed Map Serialization in MergeTree[​](#bucketed-map-serialization "Direct link to Bucketed Map Serialization in MergeTree")

By default, a `Map` column in MergeTree is stored as a single `Array(Tuple(K, V))` stream.
Reading a single key with `m['key']` requires scanning the entire column — every key-value pair for every row — even if only one key is needed.
For maps with many distinct keys this becomes a bottleneck.

Bucketed serialization (`with_buckets`) splits the key-value pairs into multiple independent substreams (buckets) by hashing the key.
When a query accesses `m['key']`, only the bucket that contains that key is read from disk, skipping all other buckets.

### Enabling Bucketed Serialization[​](#enabling-bucketed-serialization "Direct link to Enabling Bucketed Serialization")

```
CREATE TABLE tab (id UInt64, m Map(String, UInt64))
ENGINE = MergeTree ORDER BY id
SETTINGS
    map_serialization_version = 'with_buckets',
    max_buckets_in_map = 32,
    map_buckets_strategy = 'sqrt';
```

To avoid slowing down inserts, you can keep `basic` serialization for zero-level parts (created during `INSERT`) and only use `with_buckets` for merged parts:

```
CREATE TABLE tab (id UInt64, m Map(String, UInt64))
ENGINE = MergeTree ORDER BY id
SETTINGS
    map_serialization_version = 'with_buckets',
    map_serialization_version_for_zero_level_parts = 'basic',
    max_buckets_in_map = 32,
    map_buckets_strategy = 'sqrt';
```

### How It Works[​](#how-it-works "Direct link to How It Works")

When a data part is written with `with_buckets` serialization:

1. The average number of keys per row is computed from the block statistics.
2. The number of buckets is determined by the configured strategy (see [Settings](#bucketed-map-settings)).
3. Each key-value pair is assigned to a bucket by hashing the key: `bucket = hash(key) % num_buckets`.
4. Each bucket is stored as an independent substream with its own keys, values, and offsets.
5. A `buckets_info` metadata stream records the bucket count and statistics.

When a query reads a specific key (`m['key']`), the optimizer rewrites the expression to a key subcolumn (`m.key_<serialized_key>`).
The serialization layer computes which bucket the requested key belongs to and reads only that single bucket from disk.

When the full map is read (e.g., `SELECT m`), all buckets are read and reassembled into the original map. This is slower than `basic` serialization due to the overhead of reading and merging multiple substreams.

Note

The order of keys within a map value may differ from the original insertion order when using `with_buckets` serialization. Keys are distributed across buckets by hash and are reassembled in bucket order, not insertion order. With `basic` serialization, the key order from inserted maps is preserved.

The bucket count can vary between parts. When parts with different bucket counts are merged, the new part's bucket count is recalculated from the merged statistics. Parts with `basic` and `with_buckets` serialization can coexist in the same table and are merged transparently.

### Settings[​](#bucketed-map-settings "Direct link to Settings")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Setting Default Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `map_serialization_version` `basic` Serialization format for `Map` columns. `basic` stores as a single array stream. `with_buckets` splits keys into buckets for faster single-key reads.| `map_serialization_version_for_zero_level_parts` `basic` Serialization format for zero-level parts (created by `INSERT`). Allows keeping `basic` for inserts to avoid write overhead, while merged parts use `with_buckets`.| `max_buckets_in_map` `32` Upper bound on the number of buckets. The actual count depends on `map_buckets_strategy`. The maximum allowed value is 256.| `map_buckets_strategy` `sqrt` Strategy for computing bucket count from average map size: `constant` — always use `max_buckets_in_map`; `sqrt` — use `round(coefficient * sqrt(avg_size))`; `linear` — use `round(coefficient * avg_size)`. Result is clamped to `[1, max_buckets_in_map]`.| `map_buckets_coefficient` `1.0` Multiplier for `sqrt` and `linear` strategies. Ignored when strategy is `constant`.| `map_buckets_min_avg_size` `32` Minimum average keys per row to enable bucketing. If the average is below this threshold, a single bucket is used regardless of other settings. Set to `0` to disable the threshold. | | | | | | | | | | | | | | | | | | | | |

### Performance Trade-offs[​](#performance-trade-offs "Direct link to Performance Trade-offs")

The following table summarizes the performance impact of `with_buckets` compared to `basic` serialization at various map sizes (10 to 10,000 keys per row). The bucket count was determined by the `sqrt` strategy capped at 32. The exact numbers depend on key/value types, data distribution, and hardware.