Example: Create a Table Containing JSON Type Columns
In this example, the employee table is created with the following columns that store JSON data:
| Column | How JSON Data is Stored |
|---|---|
| json1 | As text, using default character set of user (LATIN or UNICODE) |
| json2 | In BSON storage format |
| json3 | As UNICODE text |
| json4 | In UBJSON storage format |
| json5 | As text, using default character set of user |
CREATE TABLE employee (
id INTEGER,
json1 JSON(20),
json2 JSON(25) STORAGE FORMAT BSON,
json3 JSON(30) CHARACTER SET UNICODE,
json4 JSON(1000) STORAGE FORMAT UBJSON,
json5 JSON(5000));
Example: Add JSON Type Columns to a Table
The following ALTER TABLE statements add 3 JSON type columns to the jsonTable table.
CREATE TABLE jsonTable(id INTEGER); ALTER TABLE jsonTable ADD j1 JSON; ALTER TABLE jsonTable ADD j2 JSON STORAGE FORMAT BSON; ALTER TABLE jsonTable ADD j3 JSON STORAGE FORMAT UBJSON;
Example: Altering the Maximum Length and Inline Length
CREATE TABLE jsonTable (id INTEGER,
/* non-LOB */ jsn1 JSON(1000) CHARACTER SET LATIN,
/* LOB */ jsn2 JSON(1M) INLINE LENGTH 30000 CHARACTER SET LATIN);
ALTER TABLE jsonTable ADD jsn1 JSON(2000); ALTER TABLE jsonTable ADD jsn2 JSON(2M) INLINE LENGTH 30000 CHARACTER SET LATIN;