Map(key, value)
Map(key, value)
data type stores key:value
pairs.
Parameters
key
β The key part of the pair. String, Integer, LowCardinality, or FixedString.value
β The value part of the pair. String, Integer, Array, LowCardinality, or FixedString.
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