Skip to main content

Map(key, value)

Map(key, value) data type stores key:value pairs.

Parameters

To get the value from an a Map('key', 'value') column, use a['key'] syntax. This lookup works now with a linear complexity.

Examples

Consider the table:

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

Select all key2 values:

SELECT a['key2'] FROM table_map;

Result:

β”Œβ”€arrayElement(a, 'key2')─┐
β”‚ 10 β”‚
β”‚ 20 β”‚
β”‚ 30 β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

If there's no such key in the Map() column, the query returns zeros for numerical values, empty strings or empty arrays.

INSERT INTO table_map VALUES ({'key3':100}), ({});
SELECT a['key3'] FROM table_map;

Result:

β”Œβ”€arrayElement(a, 'key3')─┐
β”‚ 100 β”‚
β”‚ 0 β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
β”Œβ”€arrayElement(a, 'key3')─┐
β”‚ 0 β”‚
β”‚ 0 β”‚
β”‚ 0 β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Convert Tuple to Map Type​

You can cast Tuple() as Map() using CAST function:

SELECT CAST(([1, 2, 3], ['Ready', 'Steady', 'Go']), 'Map(UInt8, String)') AS map;
β”Œβ”€map───────────────────────────┐
β”‚ {1:'Ready',2:'Steady',3:'Go'} β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Map.keys and Map.values Subcolumns​

To optimize Map column processing, in some cases you can use the keys and values subcolumns instead of reading the whole column.

Example

Query:

CREATE TABLE t_map (`a` Map(String, UInt64)) ENGINE = Memory;

INSERT INTO t_map VALUES (map('key1', 1, 'key2', 2, 'key3', 3));

SELECT a.keys FROM t_map;

SELECT a.values FROM t_map;

Result:

β”Œβ”€a.keys─────────────────┐
β”‚ ['key1','key2','key3'] β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

β”Œβ”€a.values─┐
β”‚ [1,2,3] β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

See Also

Original article