Map Data Type in ClickHouse

Map data type in ClickHouse is used to store key-value pairs in table columns. A key-value pair consists of two data elements that are related. Let us look at an example for creating a table with Map data type, inserting data into the table, and accessing the data.

CREATE TABLE map_example
(
map_column Map(String, Int32),
ID UInt32
) 
ENGINE=Log;

We can use the INSERT statement to insert values.

INSERT INTO map_example VALUES
({'Apple':1}, 1),
({'Banana':2}, 2);

To retrieve the values from the map column, we can use column_name[‘key’] syntax.

SELECT map_column['Apple'] FROM map_example;

We can use the map() function to convert key value pairs in to map data type.

SELECT map('key1', 1, 'key2', 2) as map_datatype;

References

https://clickhouse.com/docs/en/sql-reference/data-types/map

https://clickhouse.com/docs/en/sql-reference/functions/tuple-map-functions#map