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.
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.
INSERT INTO MyTable JSON '{"pkey":123,"val":1234,"extra":"1234"}';
{"extra":"1234"}
SELECT AS JSON pkey, val, jsn FROM MyTable;
{"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.