Map(key, value)

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

Parameters
- key — The key part of the pair. String or Integer.
- value — The value part of the pair. String, Integer or Array.

Warning

Currently Map data type is an experimental feature. To work with it you must set allow_experimental_map_type = 1.

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'} │
└───────────────────────────────┘

See Also

• map() function
• CAST() function

Original article