Overview of mapping data types

OceanBase Database supports the mapping data type (MAP), which is used to store unordered key-value pairs. A mapping data type consists of keys and their corresponding values. This data type allows you to easily manage unordered key-value pairs, such as {a:1, b:2, c:3}, and is very useful in scenarios such as storing configuration options, user attributes, and product information.

Currently, the MAP data type mainly supports the definition (DDL) and operations (DML) of mapping data. You can build a mapping in two ways: by using a mapping expression or by using a string. Additionally, two expressions, map_keys and map_values, are provided to extract the key or value array from a mapping, facilitating data processing and analysis.

The supported mapping element types for keys in OceanBase Database are as follows:

• Integer types: TINYINT, SMALLINT, INT/INTEGER, and BIGINT, which support the UNSIGNED modifier.
• Floating-point types: FLOAT and DOUBLE.
• String type: VARCHAR.

The supported mapping element types for values are as follows:

• Integer types: TINYINT, SMALLINT, INT/INTEGER, and BIGINT, which support the UNSIGNED modifier.
• Floating-point types: FLOAT and DOUBLE.
• String type: VARCHAR.
• Array type: ARRAY.

The following limitations and considerations apply:

• The <, >, >=, and <= operators are not supported for comparing values.
• The ORDER BY, GROUP BY, MIN, MAX, AVG, and SUM aggregate operations are not supported.
• If the value is an array, the maximum nesting depth of the array is 5. Since the map itself is considered one layer, the total depth is limited to 6, allowing arrays within the value to be nested up to 5 layers.

In addition to providing the mapping data type, OceanBase Database also provides functions for mapping data. For more information, see Mapping functions.