JSON Data Type | NEW JSON Constructor | Teradata Vantage - NEW JSON Constructor - Advanced SQL Engine - Teradata Database

Teradata provides a JSON data type constructor to creates a new instance of a JSON data type. The constructor accepts optional parameters to specify a JSON string, character set, and storage format for the new JSON instance.

You can use the NEW JSON constructor to insert a JSON type into a table column or as a JSON data type argument to a function or method that accepts or requires them.

Syntax

NEW JSON ( [ JSON_string_spec | JSON_binary_data_spec ] )

Syntax Elements

JSON_string_spec

'JSON_String' [, { LATIN | UNICODE | BSON | UBJSON } ]

JSON_binary_data_spec

'JSON_binary_data' [, { BSON | UBJSON } ]

'JSON_String'

A text string that will be the value of the resulting JSON instance.

The string must conform to JSON syntax as described in JSON String Syntax.

LATIN

UNICODE

The character set of the resulting JSON instance.

If you do not specify a character set, the default character set of the user is used.

BSON

UBJSON

The storage format of the resulting JSON instance.

The result of the constructor is a JSON type with its data stored in either BSON or UBJSON format.

JSON_binary_data

JSON binary data that will be the value of the resulting JSON instance.

The binary data must be BYTE, VARBYTE, or BLOB type.

If you specify JSON_binary_data, you must specify either BSON or UBJSON.

Rules and Restrictions

If the JSON type is being inserted into a column or used as an argument to a function or method, a check is performed to ensure that the actual length of the JSON data is less than or equal to the maximum length specified for this particular instance, since it could be less than the absolute maximum. In any case where the data is too large for its current usage, an error is reported. Note that truncation does not occur.

Usage Notes

In the default constructor, no arguments are passed to the constructor expression. NEW JSON() initializes an empty JSON type value with the character set based on the character set of the user. The data is set to a null string, and the default storage format is text.

If you pass an empty JSON object as an argument to the constructor, for example, NEW JSON(''), the constructor returns an empty JSON object.

You can use the NEW JSON constructor to construct a JSON document that is stored in a binary format; however, if the result of this constructor is sent to a client, it will be sent as a CLOB which contains the text representation of the binary data.

You can append a JSON dot notation reference to the end of a constructor expression as described in JSON Dot Notation (Entity Reference).