Map operation functions perform some basic operations on input map data, such as extracting Key or Value elements, and return the modified map data. Currently, OceanBase Database supports the map_keys() and map_values() functions.
map_keys
The map_keys() function returns a list of keys in the target mapping. The syntax is as follows:
map_keys(map)
Input parameters must be mapped data.
The return value is an array.
Examples:
SELECT map_keys(map(1,"apple",2,"banana"));
+-------------------------------------+
| map_keys(map(1,"apple",2,"banana")) |
+-------------------------------------+
| [1,2] |
+-------------------------------------+
1 row in set
CREATE TABLE t1 (
id INT NOT NULL AUTO_INCREMENT,
m MAP(INT, INT)
);
INSERT INTO t1 (m) VALUES ('{1:1,2:2,3:3}');
INSERT INTO t1 (m) VALUES ('{"1":1, 2 : 2 , "3 " : 3}');
INSERT INTO t1 (m) VALUES ('{1.5:2, 1.6:"3"}');
SELECT m, map_keys(m) FROM t1;
+---------------+-------------+
| m | map_keys(m) |
+---------------+-------------+
| {1:1,2:2,3:3} | [1,2,3] |
| {1:1,2:2,3:3} | [1,2,3] |
| {2:3} | [2] |
+---------------+-------------+
3 rows in set
map_values
The map_values() function returns the list of values from the destination map. The syntax is as follows:
map_values(map)
The input parameters must be mapped data.
The return value is an array type.
For example:
SELECT map_values(map(1,"apple",2,"banana"));
+-------------------------------------+
| map_values(map(1,"apple",2,"banana")) |
+-------------------------------------+
| ["apple","banana"] |
+-------------------------------------+
1 row in set
-- Refer to the map_keys example for the table creation and insert statements.
SELECT m, map_values(m) FROM t1;
+---------------+---------------+
| m | map_values(m) |
+---------------+---------------+
| {1:1,2:2,3:3} | [1,2,3] |
| {1:1,2:2,3:3} | [1,2,3] |
| {2:3} | [3] |
+---------------+---------------+
3 rows in set