Rules for Shredding JSON Data Using the INSERT JSON Statement - 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 fails because JSON does not have a matching textual value for binary data.
• The INSERT statement only handles 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 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 thrown.
  • 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 thrown. This is another reason why specifying a JSON literal is preferable to using parameterized SQL.

For more information about INSERT JSON, INSERT, and INSERT SELECT, see INSERT/INSERT ... SELECT Syntax.