Creates a MAP value from a sequence of key-value pairs.
Syntax
map(K, V) map(K <key1>, V <value1>, K <key2>, V <value2>[, ...])Parameters
| Parameter | Required | Description |
|---|---|---|
key |
Yes | The key. All keys must share the same data type after implicit conversion. Only basic data types are supported. |
value |
Yes | The value. All values must share the same data type after implicit conversion. All data types except DECIMAL are supported. |
Return value
Returns a value of the MAP type.
Usage notes
Duplicate keys
When the same key appears more than once in the arguments, the last value wins by default. To change this behavior, set odps.sql.map.key.dedup.policy at the session level:
| Value | Behavior |
|---|---|
last_win |
The last occurrence overwrites earlier ones. This is the default. |
exception |
An error is returned. |
Examples
Example 1: No duplicate keys
Table t_table has columns c1 (BIGINT), c2 (STRING), c3 (STRING), c4 (BIGINT), and c5 (BIGINT):
+------+-----+-----+----+----+
| c1 | c2 | c3 | c4 | c5 |
+------+-----+-----+----+----+
| 1000 | k11 | k21 | 86 | 15 |
| 1001 | k12 | k22 | 97 | 2 |
| 1002 | k13 | k23 | 99 | 1 |
+------+-----+-----+----+----+Build a map using c2→c4 and c3→c5 as key-value pairs:
SELECT map(c2, c4, c3, c5) FROM t_table;Output:
+------------------+
| _c0 |
+------------------+
| {k11:86, k21:15} |
| {k12:97, k22:2} |
| {k13:99, k23:1} |
+------------------+Example 2: Duplicate keys
Table t_table has the same schema. Input data:
1000, 'k11', 'k11', 86, 15
1001, 'k12', 'k22', 97, 2
1002, 'k13', 'k23', 99, 1
1003, 'k13', 'k24', 100, 1
1004, 'k12', 'k25', 95, 1In row 0, c2 and c3 both equal k11, creating a duplicate key within the same map. With the default last_win policy, the last value for each duplicate key overwrites earlier ones:
SELECT map(c2, c4, c3, c5) FROM t_table;Output:
+----------------------+
| _c0 |
+----------------------+
| {'k11':15} |
| {'k12':97, 'k22':2} |
| {'k13':99, 'k23':1} |
| {'k13':100, 'k24':1} |
| {'k12':95, 'k25':1} |
+----------------------+In the first row, k11 appears as a key from both c2 and c3. The value from c3 (15) overwrites the value from c2 (86).
Related functions
MAP is a complex type function. For other functions that work with ARRAY, MAP, STRUCT, and JSON data, see Complex type functions.