MAP Data Type in AnalyticDB for MySQL - AnalyticDB for MySQL

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, or STRING.
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 total count of keys and values combined.

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"]         |       2 |                 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"}] |       4 |                 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 total number of keys and values.	`size(map(array["a","b"], array[1,2]))` → `4`	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>