Map Data Type

MAPs are similar to STRUCTs in that they are an ordered list of “entries” where a key maps to a value. However, MAPs do not need to have the same keys present on each row, and thus open additional use cases. MAPs are useful when the schema is unknown beforehand, and when adding or removing keys in subsequent rows. Their flexibility is a key differentiator.

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 (Ex: a MAP of INTs to VARCHARs). MAPs may also have duplicate keys. This is possible and useful because maps are ordered. MAPs are also more forgiving when extracting values, as they return an empty list if a key is not found rather than throwing an error as structs do.

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

To construct a MAP, use the map function. Provide a list of keys as the first parameter, and a list of values for the second.

Creating Maps

-- A map with integer keys and varchar values. This returns {1=a, 5=e}
select map([1, 5], ['a', 'e']);
-- A map with integer keys and numeric values. This returns {1=42.001, 5=-32.100} 
select map([1, 5], [42.001, -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'], ['c', 'd']], [[1.1, 2.2], [3.3, 4.4]]);
-- Create a table with a map column that has integer keys and double values
CREATE TABLE map_table (map_col MAP(INT,DOUBLE));

Retrieving from Maps

MAPs use bracket notation for retrieving values. This is due to the variety of types that can be used as a MAP’s key. Selecting from a MAP also returns a LIST rather than an individual value.

-- Use bracket notation to retrieve a list containing the value at a key's location. This returns [42]
-- Note that the expression in bracket notation must match the type of the map's key
SELECT map([100, 5], [42, 43])[100];
-- To retrieve the underlying value, use list selection syntax to grab the 0th element.
-- This returns 42
SELECT map([100, 5], [42, 43])[100][0];
-- If the element is not in the map, an empty list will be returned. Returns []
-- Note that the expression in bracket notation must match the type of the map's key else an error is returned
SELECT map([100, 5], [42, 43])[123];
-- The element_at function can also be used to retrieve a map value. This returns [42]
SELECT element_at(map([100, 5], [42, 43]),100);

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 Nested Functions.