17.05 - Rules for Using the JSON Option - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Advanced SQL Engine
Teradata Database
Release Number
January 2021
English (United States)
Last Update
  • 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