16.20 - NEW JSON Constructor Expression - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ JSON Data Type

Teradata Database
Teradata Vantage NewSQL Engine
March 2019
Programming Reference


The NEW JSON constructor allocates a JSON type instance. The constructor can be used without arguments (default constructor) or with arguments to set the value, the character set, or storage format of the JSON instance. The resulting JSON instance can be used to insert a JSON type into a column or as a JSON type argument to a function or method.


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.
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.
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 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

JSON_String or JSON_binary_data must be less than or equal to the maximum possible length of the resulting JSON type:
  • 16776192 LATIN characters or 8388096 UNICODE characters for JSON text data
  • 16776192 bytes for binary data

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 entity reference to the end of a constructor expression as described in JSON Entity Reference (Dot Notation Syntax).

Example: Default JSON Constructor


Examples: JSON Constructor with Text String Arguments

NEW JSON ('{"name" : "cameron", "age" : 24}')

NEW JSON ('{"name" : "cameron", "age" : 24}', LATIN)

Example: JSON Constructor with Binary Data Arguments

This example illustrates the hex format of a BSON document. It is not expected that hex string literals will be used to create a JSON document in the BSON format. Tokens in the data are indicated by alternating bold and regular font. The various hex values are interpreted as follows:
  • 0x00000016: Overall length of the BSON data
  • 0x02: Indicates that the data following the key is of type 'string'
  • 0x68, 0x65, 0x6C, 0x6C, 0x6F, 0x00: The null-terminated string 'hello', interpreted as the key
  • 0x00000006: The length of the string value that follows the key
  • 0x77, 0x6F, 0x72, 0x6C, 0x64, 0x00: The null-terminated string 'world', interpreted as the value
  • 0x00: The null-terminator to indicate the end of the document

The data represents a JSON document that looks like the following in text format:

/*Creates a JSON document stored as BSON, explicitly*/
SELECT NEW JSON ('160000000268656C6C6F0006000000776F726C640000'xb, BSON);