Map constructors and operators

Map constructors and operators are used to construct new map data types. OceanBase Database currently supports the map() constructor and the {} operator.

map

The map() function is used to construct a map data type. 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.
• The data element types of key1~keyN and value1~valueN are described in Overview of map data types.
• Note that the keys in the input key-value pairs must be unique. If there are duplicate keys, the later input value will overwrite the earlier one.

The return value is a map data type (MAP).

Here are some examples:

-- The constructed mapping data contains only one key-value pair: key is 1 and value is 2.
SELECT map(1,2);
+----------+
| map(1,2) |
+----------+
| {1:2}    |
+----------+
1 row in set

-- The constructed mapping data contains 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 type of the Key array is determined by the odd-numbered parameters, and the data type of the Value array is determined by the even-numbered parameters.
SELECT map(1,"a","b",1.8,"ccc",3);
+-------------------------------+
| map(1,"a","b",1.8,"ccc",3)    |
+-------------------------------+
| {"1":"a","b":"1.8","ccc":"3"} |
+-------------------------------+

{}

{} is used to construct mapping data as a string. When you write mapping data as a string in the {key1:value1, key2:value2, ..., keyN:valueN} format, OceanBase Database converts it into the mapping data type. Therefore, you can directly use such strings when inserting data into a mapped column.

The input parameters are described as follows:

• Each key-value pair is separated by ,. A colon : separates the key from its value.
• Keys do not require quotation marks. For example, {1:1,2:2,3:3} is valid.
• In the input key-value pairs, keys must be unique. If a duplicate key appears, the subsequent value will overwrite the earlier ones.

Here is an example:

CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT,
  m MAP(INT, INT)
);

INSERT INTO t1 (m) VALUES ('{1:1,2:2,3:3}');

SELECT m FROM t1;
+---------------+
| m             |
+---------------+
| {1:1,2:2,3:3} |
+---------------+
+----------+
1 row in set