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