Map(K, V)

The Map(K, V) data type allows you to store collections of key-value pairs. Each key in the map is of type K, and its corresponding value is of type V.

Unlike some other database systems, Tinybird's Map type does not enforce unique keys. This means a single map can contain multiple entries with the same key. Internally, maps are represented as an Array(Tuple(K, V)).

Parameters

  • K: The data type for the keys in the map. This can be any type except for Nullable or LowCardinality types nested with Nullable.
  • V: The data type for the values in the map. This can be any arbitrary type.

Accessing Map Values

You can retrieve the value associated with a specific key using the m[k] syntax, where m is your map and k is the key you are looking for. This operation scans the map, so its performance is linear with the size of the map.

If the requested key k is not present in the map, m[k] will return the default value for the map's value type (V). For example, it returns 0 for integer types or an empty string '' for string types.

Example: Accessing a key

SELECT map('key1', 10, 'key2', 20, 'key3', 30)['key2'] AS value_for_key2,
       map('item_id', 101, 'item_name', 'Widget')['non_existent_key'] AS non_existent_value

Result:

┌─value_for_key2─┬─non_existent_value─┐
│             20 │                    │
└────────────────┴────────────────────┘

Convert tuple to map

You can convert a Tuple into a Map using the CAST function. The tuple should contain two arrays: one for the keys and one for the values.

Example: Casting a tuple to a map

SELECT CAST(([1, 2, 3], ['Ready', 'Steady', 'Go']), 'Map(UInt8, String)') AS my_map

Result:

┌─my_map────────────────────────┐
│ {1:'Ready',2:'Steady',3:'Go'} │
└───────────────────────────────┘

Reading subcolumns of map

To optimize queries and avoid processing the entire map, you can directly access the keys and values subcolumns of a Map type. This can be useful when you only need to work with the keys or values as separate arrays.

Example: Accessing map subcolumns

SELECT map('city', 'London', 'country', 'UK', 'population', 9000000).keys AS map_keys,
       map('city', 'London', 'country', 'UK', 'population', 9000000).values AS map_values

Result:

┌─map_keys───────────────────────┬─map_values──────────────────────────┐
│ ['city','country','population'] │ ['London','UK','9000000']           │
└────────────────────────────────┴─────────────────────────────────────┘
Updated