Mapping operation functions perform basic operations on input mapping data, such as extracting Key or Value elements, and return the processed mapping data. OceanBase Database currently supports the map_keys() and map_values() functions.
map_keys
The map_keys() function returns the list of keys in the target mapping. Syntax:
map_keys(map)
The input parameter must be mapping data.
The return value is of the 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 list of values in the target mapping. Syntax:
map_values(map)
The input parameter must be mapping data.
The return value is of the array type.
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
-- For the statements for creating and inserting data into the t1 table, see the example for map_keys.
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