Skip to main content

Map(K, V)

Map(K, V) data type stores key:value pairs. The Map datatype is implemented as Array(Tuple(key T1, value T2)), which means that the order of keys in each map does not change, i.e., this data type maintains insertion order.

Parameters

  • key — The key part of the pair. Arbitrary type, except Nullable and LowCardinality nested with Nullable types.
  • value — The value part of the pair. Arbitrary type, including Map and Array.

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

Related content​