Teradata provides casting functionality for a JSON type.
- The JSON type can be cast to all other forms of the JSON type.
- The JSON type can be cast to a JSON type of a different character set, such as JSON LATIN from JSON UNICODE.
- The JSON type can be cast to and from VARCHAR and CLOB of the same character set:
- VARCHAR(32000) CHARACTER SET UNICODE
- VARCHAR(64000) CHARACTER SET LATIN
- CLOB(8388096) CHARACTER SET UNICODE
- CLOB(16776192) CHARACTER SET LATIN
- The JSON type can be cast to a CHAR of the same character set:
- CHAR(32000) CHARACTER SET UNICODE
- CHAR(64000) CHARACTER SET LATIN
- A JSON type specified with a binary storage format can be cast to and from BYTE, VARBYTE, or BLOB.
If you cast a JSON type to/from VARCHAR/CHAR and you do not specify a character set, the character set of the JSON type will be used.
The casting functionality can be implicitly invoked, and the format of the data cast to/from in the text conforms to JSON syntax.
If the input to CAST is an empty JSON object, for example SELECT CAST( '' AS JSON);, the cast routine returns an empty JSON object.
If any truncation occurs as a result of the cast, an error is reported.
Casting and Storage Formats
When casting to a JSON type, you can use the STORAGE FORMAT syntax to specify the desired storage format of the target JSON type. This allows for easy conversion between BYTE/VARBYTE/BLOB data and JSON types stored using one of the optional binary formats. Conversion can also be done between JSON types with different storage formats so that JSON data in one storage format can be cast to any other storage format. Because the casting functionality can be implicitly invoked, data can easily be loaded into JSON columns which use an optional binary storage format.
When casting between BYTE/VARBYTE/BLOB and JSON, the data is subject to the specification of the target binary storage format.
CHAR/VARCHAR/CLOB data can also be cast to a JSON type with a binary format. This is only useful when inserting data into a JSON column where its data is stored in one of the binary formats. In this case, the character data must be in valid JSON syntax otherwise an error is reported. Note that any extended data types made available in the binary formats will not be used in this case because the JSON text format does not provide these extensions. The data represented as an extended data type is converted to a string in the resulting JSON text. The same rules apply to casting from JSON in the text format to JSON stored in one of the optional binary formats.
When casting between JSON stored as BSON and JSON stored as UBJSON, any source data specified in an extended data format that does not have an associated extended data format in the target is converted to a string value.