AnalyticDB for MySQL:Map

Definition

A map is used to store key-value pairs, which is similar to a map in Java. A key must be of a native data type, such astinyint,boolean,smallint,int,bigint,float,double,string. A value can be of a native data type or the MAP or ARRAY type. For example, you can define map<int, string> and map<int, map<int, string>> for a column.

Precautions

• You cannot create indexes for columns of the ARRAY or MAP type. We recommend that you combine search conditions with SQL statements to filter data instead of directly filtering data. Minimize the amount of data to be scanned.
• Keys must be unique in a map.
• The write order of keys cannot be guaranteed. For example, {"a":1,"b":2,"d":3} is written, and the returned query result is {"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`)
) DISTRIBUTE BY HASH(`a`);

Write data

For example, you can insert a row of data where b is set to {1:"a"} and c is set to {1:{11:"a"},2:{22:"b"}}.

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)

Notice

• You can use the element_at(b, 1) function to obtain values based on keys. 1 indicates a key instead of a subscript.
• The size function returns the total number of keys and values.
• The map_keys and map_values functions return arrays.

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)

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