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

Teradata Vantage™ NewSQL Engine Release Summary

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Release Notes
featnum
B035-1098-162K

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

  • Teradata Vantage™ JSON Data Type, B035-1150
  • Teradata Vantage™ SQL Data Manipulation Language , B035-1146
  • Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144