Creating and Altering Tables to Store JSON Data

Teradata Vantageā„¢ JSON Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K
You can create tables containing JSON type columns or alter a table to add, drop, or rename JSON type columns:
  • You can specify the same CREATE TABLE or ALTER TABLE options that are permitted on the UDT types on the JSON types.
  • 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 use ALTER TABLE to change the maximum length and inline storage length of a JSON column subject to the following restrictions:
    • If the existing JSON column type is a LOB type, you can only change the maximum length to a larger value.
    • If the existing JSON column type is a nonLOB type, the newly changed data type must remain a nonLOB type, and the new maximum length and inline length values must be greater than the old values.
  • You cannot use ALTER TABLE to change 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.

For details about CREATE TABLE and ALTER TABLE, see Teradata Vantageā„¢ SQL Data Definition Language Syntax and Examples, B035-1144.

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;

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;