Inserting Values into a JSON Column

Teradata Vantageā„¢ JSON Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K
You can insert a value into a JSON column using one of the following statements:
  • INSERT

    During the INSERT operation, a JSON column value may be initialized by an expression which evaluates to the JSON type. Typical examples include the return value of a UDF which has a JSON return type, the JSON type constructor, or a string that is cast to a JSON type (in which the data conforms to the JSON string syntax).

    In the following INSERT forms, you can use a JSON type constructor or an expression which evaluates to a JSON type as the source value:
    • Positional list
    • Assignment list
    • Named list
  • INSERT...SELECT

    During the INSERT...SELECT operation, the source table with a JSON column whose actual data length is compatible with the JSON column of the target table may be selected and used as the source value. If the source data is larger than the maximum possible length of the target JSON column, an error is reported.

    If the source JSON column is UNICODE and the target JSON column is LATIN, and the source JSON instance contains a character that cannot be represented in LATIN, an error is reported.

  • MERGE

    You can use the MERGE statement to insert a value based on the WHEN NOT MATCHED THEN INSERT clause. You can use the WHEN MATCHED THEN UPDATE clause to modify a JSON column.

  • UPDATE (Upsert Form)

    You can use the UPDATE (Upsert Form) to update JSON column values in a specified row and, if the row does not exist, it inserts the row into the table with a specified set of initial column values.

You can insert data into a BSON-formatted column using the following:
  • CAST statement
    • To/from CHAR/VARCHAR/CLOB (in JSON text format)
    • To/from BYTE/VARBYTE/BLOB (in BSON format or any other JSON format)
  • NEW JSON Constructor

    The input to the constructor can be CHAR/VARCHAR/CLOB (in JSON text format) or BYTE/VARBYTE/BLOB (in BSON format).

You can insert data into a UBJSON-formatted column using the following:
  • CAST statement

    To/from CHAR/VARCHAR/CLOB (in JSON text format or any other JSON format).

  • NEW JSON Constructor

    The input to the constructor can be CHAR/VARCHAR/CLOB (in JSON text format).