MAPs are similar to STRUCTs in that they are an ordered list of key-value pairs. However, MAPs do not need to have the same keys present for each row, and thus are suitable for use cases where the schema is unknown beforehand or varies per row.

MAPs must have a single type for all keys, and a single type for all values. Keys and values can be any type, and the type of the keys does not need to match the type of the values (e.g., a MAP of VARCHAR to INT is valid). MAPs may not have duplicate keys. MAPs return NULL if a key is not found rather than throwing an error as structs do.

In contrast, STRUCTs must have string keys, but each value may have a different type. See the data types overview for a comparison between nested data types.

To construct a MAP, use the bracket syntax preceded by the MAP keyword.

Creating Maps

A map with VARCHAR keys and INTEGER values. This returns {key1=10, key2=20, key3=30}:

SELECT MAP {'key1': 10, 'key2': 20, 'key3': 30};

Alternatively use the map_from_entries function. This returns {key1=10, key2=20, key3=30}:

SELECT map_from_entries([('key1', 10), ('key2', 20), ('key3', 30)]);

A map can be also created using two lists: keys and values. This returns {key1=10, key2=20, key3=30}:

SELECT MAP(['key1', 'key2', 'key3'], [10, 20, 30]);

A map can also use INTEGER keys and NUMERIC values. This returns {1=42.001, 5=-32.100}:

SELECT MAP {1: 42.001, 5: -32.1};

Keys and/or values can also be nested types. This returns {[a, b]=[1.1, 2.2], [c, d]=[3.3, 4.4]}:

SELECT MAP {['a', 'b']: [1.1, 2.2], ['c', 'd']: [3.3, 4.4]};

Create a table with a map column that has INTEGER keys and DOUBLE values:

CREATE TABLE tbl (col MAP(INTEGER, DOUBLE));

Retrieving from Maps

MAP values can be retrieved using the map_extract_value function or bracket notation.

SELECT MAP {'key1': 5, 'key2': 43}['key1'];

5

If the key has the wrong type, an error is thrown. If it has the correct type but is merely not contained in the map, a NULL value is returned.

SELECT MAP {'key1': 5, 'key2': 43}['key3'];

NULL

The map_extract function (and its synonym element_at) can be used to retrieve a value wrapped in a list; it returns an empty list if the key is not contained in the map:

SELECT map_extract(MAP {'key1': 5, 'key2': 43}, 'key1');

[5]

SELECT MAP {'key1': 5, 'key2': 43}['key3'];

[]

Comparison Operators

Nested types can be compared using all the comparison operators. These comparisons can be used in logical expressions for both WHERE and HAVING clauses, as well as for creating Boolean values.

The ordering is defined positionally in the same way that words can be ordered in a dictionary. NULL values compare greater than all other values and are considered equal to each other.

At the top level, NULL nested values obey standard SQL NULL comparison rules: comparing a NULL nested value to a non-NULL nested value produces a NULL result. Comparing nested value members, however, uses the internal nested value rules for NULLs, and a NULL nested value member will compare above a non-NULL nested value member.

Functions

See Map Functions.