OceanBase AI Database supports creating mapped columns using the CREATE TABLE statement.
Considerations
- Default values cannot be set.
- For supported mapping types, descriptions, and limitations, see Overview of mapped data types.
Examples
You can execute the CREATE TABLE statement to create a table with mapped columns. Syntax example:
CREATE TABLE t1 (
id INT,
c1 MAP(INT, FLOAT),
c2 MAP(VARCHAR(10), VARCHAR(2)),
c3 MAP(FLOAT UNSIGNED, ARRAY(INT)),
c4 MAP(VARCHAR(3), ARRAY(ARRAY(VARCHAR(20))))
);
The following example defines a map type whose value is a nested array:
CREATE TABLE t2(c1 MAP(INT, ARRAY(INT)));
CREATE TABLE t3(c1 MAP(INT, INT[][]));
A value can support up to six levels of nested mapping (including the MAP itself). Example:
CREATE TABLE t4 (c1 MAP(INT, INT[][][][][]));
DESC t4;
+-------+-------------------------------------------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------------------------------------------+------+------+---------+-------+
| c1 | MAP(INT,ARRAY(ARRAY(ARRAY(ARRAY(ARRAY(INT)))))) | YES | | NULL | |
+-------+-------------------------------------------------+------+------+---------+-------+
1 row in set
The following example shows how to write and query mapped data.
Create a test table named t5:
obclient> CREATE TABLE t5(
c1 MAP(INT, INT),
c2 MAP(VARCHAR(256), VARCHAR(256)),
c3 MAP(BIGINT, BIGINT)
);
The return result is as follows:
obclient> DESC t5;
+-------+--------------------------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------------+------+------+---------+-------+
| c1 | MAP(INT,INT) | YES | | NULL | |
| c2 | MAP(VARCHAR(256),VARCHAR(256)) | YES | | NULL | |
| c3 | MAP(BIGINT,BIGINT) | YES | | NULL | |
+-------+--------------------------------+------+------+---------+-------+
3 rows in set
Insert data:
obclient> INSERT INTO t5 VALUES (MAP(1,2), MAP(1,2), MAP(1,2));
Query the test table, and the result is as follows:
obclient> SELECT * FROM t5;
+-------+-----------+-------+
| c1 | c2 | c3 |
+-------+-----------+-------+
| {1:2} | {"1":"2"} | {1:2} |
+-------+-----------+-------+
1 row in set