JSON AUTO COLUMN - Advanced SQL Engine - Teradata Database

JSON Data Type
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
gzn1554761068186.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1150
lifecycle
previous
Product Category
Teradata Vantage™

When using the INSERT...JSON statement to shred JSON data into a table, it is possible that the input JSON data may include extra data that does not match any existing columns in the table. You can define a designated JSON column to store this extra data.

In the CREATE TABLE statement, specify a designated JSON column by defining it as an AUTO COLUMN. For example:
CREATE TABLE MyTable
(
    pkey INTEGER,
    val  INTEGER,
    jsn  JSON CHARACTER SET LATIN AUTO COLUMN
);

You can also use the ALTER TABLE statement to add a JSON AUTO COLUMN to a table or to convert an existing JSON column into an AUTO COLUMN.

During shredding, when the JSON root object contains an attribute that does not match an existing column, the extra attributes are aggregated and inserted into the JSON AUTO COLUMN. If the table does not have a JSON AUTO COLUMN, the extra attributes are ignored.

For example, suppose you issue the following INSERT statement:
INSERT INTO MyTable JSON '{"pkey":123,"val":1234,"extra":"1234"}';
The jsn column will contain the following JSON data which did not match any of the columns in MyTable:
{"extra":"1234"}
Be aware that if you compose the shredded JSON data back into JSON, the data may not match the original JSON. For example, if you use SELECT AS JSON to compose the JSON data that was shredded in the previous INSERT statement:
SELECT AS JSON pkey, val, jsn FROM MyTable;
The result is the following JSON data:
{"pkey":123,"val":1234,"jsn":{"extra":"1234"}}

JSON AUTO COLUMN Usage Notes

  • In most cases, a JSON AUTO COLUMN behaves like a normal JSON column. You can insert any valid JSON value into the column, including binary JSON values. However, when the column is used with an INSERT...JSON statement for shredding, only JSON data in text format can be shredded into the AUTO COLUMN.
  • If the AUTO COLUMN is nullable, and the shredded data does not contain any extra data, this column is NULL, provided that the column is not specifically specified in the JSON data.
  • If the AUTO COLUMN is NOT NULL, and the shredded data does not contain any extra data, '{}' is inserted. Note that JSON columns cannot have DEFAULT values.

For more information about the AUTO COLUMN, see "CREATE TABLE" and "ALTER TABLE" in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.