Mapping constructors and operators are used to construct new mapping data types. OceanBase Database currently supports the map() constructor and the {} operator.
map
The map() function is used to construct mapping data. The syntax is as follows:
map(key1, value1, key2, value2, ..., keyN, valueN)
The input parameters are described as follows:
- The number of input parameters must be even. Parameters with odd ordinal numbers form the key array, and parameters with even ordinal numbers form the value array.
- For the data element types of key1~keyN and value1~valueN, see Overview of mapping data types.
- Note that the keys in the input key-value pairs must be unique. If there are duplicate keys, the value that appears later will overwrite the previous one.
The return value is of the mapping data type (MAP).
Here are some examples:
-- Construct a mapping data with one key-value pair, where the key is 1 and the value is 2.
SELECT map(1,2);
+----------+
| map(1,2) |
+----------+
| {1:2} |
+----------+
1 row in set
-- Construct a mapping data with three key-value pairs.
SELECT map(1,"a",2,"b",3,"c");
+------------------------+
| map(1,"a",2,"b",3,"c") |
+------------------------+
| {1:"a",2:"b",3:"c"} |
+------------------------+
1 row in set
-- The data types of the key array are determined by the parameters with odd ordinal numbers, and the data types of the value array are determined by the parameters with even ordinal numbers.
SELECT map(1,"a","b",1.8,"ccc",3);
+-------------------------------+
| map(1,"a","b",1.8,"ccc",3) |
+-------------------------------+
| {"1":"a","b":"1.8","ccc":"3"} |
+-------------------------------+