Map operation functions perform basic operations on input map data, such as extracting keys or values, and return the processed map data. The map operation functions currently supported by the OceanBase database include map_keys() and map_values().
map_keys
The map_keys() function is used to return the list of keys from a target map. The syntax is as follows:
map_keys(map)
The input parameter must be map data.
The return value is of array type.
Here is an example:
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 value list of a mapping. The syntax is as follows:
map_values(map)
The input parameter must be mapping data.
The return value is an array.
Here is an example:
SELECT map_values(map(1,"apple",2,"banana"));
+-------------------------------------+
| map_values(map(1,"apple",2,"banana")) |
+-------------------------------------+
| ["apple","banana"] |
+-------------------------------------+
1 row in set
-- The statement for creating table t1 and inserting data is the same as that in the map_keys example.
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