Map Operations
A MAP type can be created using the syntax MAP<type, type>
.
For example MAP<VARCHAR, INT>
is a map from strings to integers.
In CREATE TABLE
and CREATE TYPE
declarations there is no way to
specify the nullability of the values of a MAP
. The compiler will
always assume that map keys are not nullable, while values elements
are nullable:
CREATE TABLE T(m MAP<VARCHAR, INT>);
Table T
will have a single column m
whose values are nullable
maps, the map's keys are non-nullable VARCHAR
values, while the
maps's values are nullable INT
values.
Map literals
Map literals have the syntax MAP[
key ,
value ( ,
key ,
value )* ]
.
Here is an example: SELECT MAP['hi',2]
.
Comparison Operations on Maps
Comparison operations (=
, <>
, !=
, >
, <
, >=
, <=
) can be applied to maps and work as follows:
- The keys are first sorted lexicographically.
- If the keys are not equal, the result is determined by their lexicographical order.
- If the keys are equal, their corresponding values are compared next.
- If the values are also equal, the comparison moves to the next key-value pair until a difference is found.
Examples:
-
Given a table
T
with columnsc1 = MAP['v', 11, 'q', 66]
andc2 = MAP['v': 22]
:SELECT c1 FROM T WHERE c1 < c2;
returns:MAP['q', 66, 'v', 11]
-
Given two maps where previously compared key-value pairs are equal, such as:
MAP['f', 1, 'v', 0]
andMAP['f': 1]
- The map with more elements,
MAP['f', 1, 'v', 0]
, is considered larger.
Predefined functions on map values
The UNNEST
Operator
The UNNEST
operator takes a MAP
and returns a table with a row for
each key-value pair in the MAP
. If the input is a
map with 5 key-value pairs, the output is a table with 5 rows, each
row holding one key-value pair of the map.
When UNNEST
operator is used in self-joins as follows, an alias needs
to be used to name the key and value fields (zips(city, zip)
in the example):
CREATE TABLE data(zipcodes MAP<VARCHAR, INT>, COUNTRY VARCHAR);
CREATE VIEW V AS SELECT data.country, city, zip
FROM data CROSS JOIN UNNEST(data.zipcodes) AS zips(city, zip);
UNNEST
applied to a NULL
value returns an empty table.