Rules for Using the JSON Option - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
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