You can create tables containing JSON type columns or alter a table to add, drop, or rename JSON type columns:
- You can use the CREATE TABLE statement to create a table that contains one or more JSON type columns.You cannot use a JSON type column in an index definition.
- You can use the ALTER TABLE statement to add, drop, or rename a JSON type column.
- You can specify the same CREATE TABLE or ALTER TABLE options that are permitted on the UDT types on the JSON types.
- You cannot use ALTER TABLE to change the following:
- The maximum length of a JSON type
- The storage format of an existing column
If you want to change the storage format of a JSON column, you must drop the column and add the column back specifying the new storage format.
Example: Create a Table Containing JSON Type Columns
In this example, the employee table is created with the following columns that store JSON data:
- json1 stores JSON data as text in the default character set of the user (LATIN or UNICODE)
- json2 stores JSON data using the BSON storage format
- json3 stores JSON data as text in UNICODE
- json4 stores JSON data using the UBJSON storage format
- json5 stores JSON data as text in the default character set of the 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;
For details about CREATE TABLE and ALTER TABLE, see SQL Data Definition Language - Syntax and Examples, B035-1144