Examples: Inserting Data in JSON Format - Advanced SQL Engine - Teradata Database

Below is the table definition for the following example.

CREATE TABLE MyTable (
pkey INTEGER, 
val INTEGER, 
j JSON AUTO COLUMN);

This statement inserts the value 10 into the column pkey and the value 1234 into the column val. Because the table does not have a column named extra, the value 1234 is inserted into the auto column j.

INSERT INTO MyTable JSON '{"pkey":10,"val":1234,"extra":"1234"}';

This statement inserts parameterized SQL, represented by the question mark (?), into the table MyTable for a client that supports parameterized SQL.

INSERT INTO MyTable JSON ?;

Below is the table definition for the following example.

CREATE TABLE LDITable, WITH ISOLATED LOADING (
pkey INTEGER, 
val INTEGER, 
j JSON AUTO COLUMN);

This statement inserts into the load isolated table LDITable.

INSERT WITH ISOLATED LOADING INTO LDITable 
'{"pkey":10,"val":1234,"extra":"1234"}';

Below is the table definition for the example that follows. The table jsonTable includes the auto column j which must contain a value.

CREATE TABLE jsonTable (
a INTEGER, 
b INTEGER, 
j JSON AUTO COLUMN NOT NULL);

These statements insert three rows of data in the table JsonTable.

INSERT jsonTable JSON '{"a":1,"b":1,"extra":1}';

INSERT jsonTable JSON '{"a":2,"b":2,"extra1":2,"extra2":222}';

INSERT jsonTable JSON '{"a":3,"b":3};

Because the table does not have a column named extra, extra1, or extra 2, the values specified for extra, extra1, and extra2 are inserted into the auto column j. The third insert statement only specifies values for columns a and b. Because column j is defined as NOT NULL, an empty set of brackets ({}) is inserted in the third row for column j.

SELECT * FROM jsonTable ORDER BY 1;
          a           b j
----------- ----------- --------------------------------------
          1           1 {"extra":"1"}
          2           2 {"extra1":"2","extra2":"222"}
          3           3 {}