Map type

MAP(K, V) stores key-value pairs. Here’s what K and V represent:

• K: The type of the Map keys. It can be string or integral types (i.e., character varying, smallint, integer, or bigint). It must not be NULL, and must be unique in the map.
• V: The type of the Map values. It can be arbitrary type.

PUBLIC PREVIEW

This feature is currently in public preview, meaning it is nearing the final product but may not yet be fully stable. If you encounter any issues or have feedback, please reach out to us via our Slack channel. Your input is valuable in helping us improve this feature. For more details, see our Public Preview Feature List.

Define a map

To create a map with VARCHAR keys and INTEGER values, use the statement below.

SELECT MAP {'key1': 1, 'key2': 2, 'key3': 3};
----RESULT
{key1:1,key2:2,key3:3}

Alternatively, it can be constructed from two arrays with map_from_key_values or from an array of key-value pairs with map_from_entries.

SELECT map_from_key_values(array['key1', 'key2', 'key3'], array[1,2,3]);
-- OR
SELECT map_from_entries(array[row('key1',1), row('key2',2), row('key3',3)]);

The following statement defines a table x that has a MAP(VARCHAR, INTEGER) column.

CREATE TABLE x (a MAP(VARCHAR, INTEGER));

Access data in a map

Use bracket notation to access the value of a key.

SELECT MAP {'key1': 1, 'key2': 2, 'key3': 3}['key1'];
----RESULT
1

Modify data in a map

Use map_insert to insert or overwrite a key-value pair into a map.

SELECT map_insert(MAP {'key1': 1, 'key2': 2, 'key3': 3}, 'key4', 4);
----RESULT
{key1:1,key2:2,key3:3,key4:4}

SELECT map_insert(MAP {'key1': 1, 'key2': 2, 'key3': 3}, 'key2', 4);
----RESULT
{key1:1,key3:3,key2:4}

Map functions and operators

For the full list of map functions and operators, see Map functions and operators.