JSON Auto Composition and Shredding - Teradata Database - Teradata Vantage NewSQL Engine

This feature introduces new syntax that makes it easier to compose JSON data from table columns and to shred JSON data into table columns. The INSERT statement and the SELECT statement are enhanced to handle JSON composition and shredding. This provides an alternative way of composing and shredding JSON data which may be simpler than using the JSON_COMPOSE function or JSON_TABLE table operator.

The feature also provides for a JSON AUTO COLUMN which is used to store extra data encountered during the shredding process that do not match any existing columns in the table.

Benefits

• The new SELECT AS JSON syntax automatically composes select columns into JSON data so that each row of data returned is JSON data.
• The enhanced INSERT statement provides easier, simpler syntax for shredding JSON data into a table. In addition, when using the enhanced INSERT statement to shred JSON data that is a string literal, the shredding operation can be a one-AMP operation which results in a performance improvement.
• The AUTO COLUMN can store input JSON data that does not match existing table columns so that no information is lost during the shredding process.
• This feature allows Teradata to handle NoSQL data more fluidly.

Considerations

• You can only use the INSERT...JSON statement to shred JSON data that is in text format. The shredded data will be in VARCHAR format and implicit casting is used to convert the VARCHAR data to the target table column format. If the VARCHAR data cannot be CAST to the target column format, the insertion fails.
• For parameterized SQL, INSERT...JSON supports VARCHAR, CLOB, and external JSON data (which is treated as a CLOB).

SQL Changes

These statements are extended to support the AUTO COLUMN declaration:

• CREATE TABLE
• ALTER TABLE

The SELECT statement is extended to support the SELECT AS JSON syntax.

The INSERT statement is extended to support the INSERT...JSON syntax.

Additional Information