Map
The MAP type stores key-value pairs and is supported in AnalyticDB for MySQL 3.1.1 and later.
Definition
A MAP column maps keys to values:
-
Keys must be a primitive data type:
TINYINT,BOOLEAN,SMALLINT,INT,BIGINT,FLOAT,DOUBLE, orSTRING. -
Values can be a primitive data type, or the MAP or ARRAY type, enabling nested structures.
For example, define map<int, string> for a flat map, or map<int, map<int, string>> for a nested map.
Usage notes
-
Indexes are not supported on MAP or ARRAY columns. Use filter conditions in SQL statements to reduce the amount of data scanned.
-
Keys must be unique within a map.
-
Key insertion order is not preserved. For example, inserting
{"a":1,"b":2,"d":3}may return as{"d":3,"a":1,"b":2}.
Examples
Create a table
CREATE TABLE map_test (
`a` INT,
`b` MAP<INT, STRING>,
`c` MAP<INT, MAP<INT, STRING>>,
PRIMARY KEY (`a`)
) DISTRIBUTED BY HASH(`a`);
Insert data
INSERT INTO map_test VALUES (1, '{1:"a"}', '{1:{11:"a"},2:{22:"b"}}');
Query data
SELECT * FROM map_test;+------+---------+-------------------------+
| a | b | c |
+------+---------+-------------------------+
| 1 | {1:"a"} | {1:{11:"a"},2:{22:"b"}} |
+------+---------+-------------------------+
1 row in set (0.07 sec)
Use element_at to retrieve a value by key. The argument is a key, not a subscript.
SELECT element_at(c, 1), element_at(element_at(c, 1), 11) FROM map_test;+-----------------+--------------------------------+
| element_at(c,1) | element_at(element_at(c,1),11) |
+-----------------+--------------------------------+
| {11:"a"} | a |
+-----------------+--------------------------------+
1 row in set (0.07 sec)
Use map_keys and map_values to extract all keys or values as an array. size returns the number of key-value pairs.
SELECT map_keys(b), map_values(b), size(b), size(map_keys(b)), size(map_values(b)) FROM map_test;+-------------+---------------+---------+-------------------+---------------------+
| map_keys(b) | map_values(b) | size(b) | size(map_keys(b)) | size(map_values(b)) |
+-------------+---------------+---------+-------------------+---------------------+
| [1] | ["a"] | 1 | 1 | 1 |
+-------------+---------------+---------+-------------------+---------------------+
1 row in set (0.08 sec)SELECT map_keys(c), map_values(c), size(c), size(map_keys(c)), size(map_values(c)) FROM map_test;+-------------+---------------------+---------+-------------------+---------------------+
| map_keys(c) | map_values(c) | size(c) | size(map_keys(c)) | size(map_values(c)) |
+-------------+---------------------+---------+-------------------+---------------------+
| [1,2] | [{11:"a"},{22:"b"}] | 2 | 2 | 2 |
+-------------+---------------------+---------+-------------------+---------------------+
1 row in set (0.08 sec)
Supported functions
| Function | Description | Example | Return type |
|---|---|---|---|
element_at |
Returns the value for a given key. | element_at(map(array["a","b"], array[1,2]), a) → 1 |
V |
size |
Returns the number of key-value pairs. | size(map(array["a","b"], array[1,2])) → 2 |
INT |
map_keys |
Returns all keys as an array. | map_keys(map(array["a","b"], array[1,2])) → ["a","b"] |
ARRAY<K> |
map_values |
Returns all values as an array. | map_values(map(array["a","b"], array[1,2])) → [1,2] |
ARRAY<V> |