Examples: Inserting Data in JSON Format - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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;

Result:

          a           b j
----------- ----------- --------------------------------------
          1           1 {"extra":"1"}
          2           2 {"extra1":"2","extra2":"222"}
          3           3 {}