Create map columns
OceanBase Database supports creating map columns using the CREATE TABLE statement.
Considerations
- Default values are not supported for map columns.
- For supported map types, along with their descriptions and limitations, see Overview of map element types.
Examples
The following is an example of creating a table with map columns using the CREATE TABLE statement:
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))))
);
Here is an example of defining a map type where the value type is a nested array:
CREATE TABLE t2(c1 MAP(INT, ARRAY(INT)));
CREATE TABLE t3(c1 MAP(INT, INT[][]));
The value type supports up to 6 levels of nested arrays (including the map itself). Here is an 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
Here is an example of writing and querying map data:
Create a test table t5:
obclient> CREATE TABLE t5(
c1 MAP(INT, INT),
c2 MAP(VARCHAR(256), VARCHAR(256)),
c3 MAP(BIGINT, BIGINT)
);
The 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 into the table:
obclient> INSERT INTO t5 VALUES (MAP(1,2), MAP(1,2), MAP(1,2));
Query the test table. The result is as follows:
obclient> SELECT * FROM t5;
+-------+-----------+-------+
| c1 | c2 | c3 |
+-------+-----------+-------+
| {1:2} | {"1":"2"} | {1:2} |
+-------+-----------+-------+
1 row in set