Rules for Using the JSON Option - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™
  • You can only shred JSON data in text format. You cannot use the INSERT statement to shred JSON data that is in one of the binary formats such as BSON or UBJSON.
  • The shredded data is 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 example, if the target column is VARBYTE, casting values other than NULL to VARBYTE returns an error because JSON does not have a matching textual value for binary data.
  • The INSERT statement processes a single row of JSON data with JSON OBJECT at the root. That is, the JSON data starts with '{' as the first non-white space character. You cannot insert multiple rows of data using the INSERT statement.
  • The INSERT statement supports Load Isolation options if the target table is an LDI table.
  • The target table must be a table and not a view.
  • The column name matching is not case sensitive.
  • If the same column is matched multiple times, the data stored is the last match.
  • If any of the target table columns is NOT NULL, and the JSON input data does not contain any data for the column, the following rules apply:
    • If the target column does not have a DEFAULT value, an error is returned.
    • If the target column has a DEFAULT value and the JSON data is a string literal, the DEFAULT value is inserted into the target table.
    • If the target column has a DEFAULT value, and INSERT...JSON uses parameterized SQL, the DEFAULT value is ignored and an error is returned. In this case, specifying a JSON literal is preferable to using parameterized SQL