AnalyticDB for MySQL version 3.1.1 and later support the ARRAY and MAP types. This topic describes the definition, precautions, and examples of the MAP type.

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 as byte/boolean/short/int/long/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

Function Description Return type
element_at Returns a value based on a key. Example: element_at(map(array["a","b"],array[1,2]), a) ==> 1. V
size Returns the total number of keys and values. int
map_keys Returns the list of all the keys. array<K>
map_values Returns the list of all the values. array<V>