About JSON Type Cast

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
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 or from VARCHAR/CHAR and you do not specify a character set, the character set of the JSON type is 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.

When a dot notation expression is present in a CAST statement, the database attempts to return the desired data type without performing an explicit cast. This provides a performance boost in terms of data conversion. In addition, if the conversion fails, a NULL value is returned instead of an error. This is useful for handling dirty data that may contain anomalies not matching the desired target data type.

When casting a JSON dot notation expression to the following data types and the conversion fails, an error is returned instead of a NULL value:
  • TIME or TIMESTAMP
  • DATE
  • LOB or CLOB
  • UDT

If any truncation of data occurs as a result of the cast, a NULL value is returned.

Examples: Using Cast with the JSON Type

A dot notation expression is present in the following CAST statements:

SELECT CAST(NEW JSON('{"a":"1"}')..a AS NUMBER);
Result:
> 1.E0
SELECT CAST(NEW JSON('{"a":"a"}')..a AS INTEGER);
Result:
> ?
The following CAST statements specify an inline length for the resulting JSON type:
SELECT CAST('{}' AS JSON(300) INLINE LENGTH 100);
SELECT CAST(new JSON('{}', LATIN) AS JSON(10) INLINE LENGTH 10);
SELECT CAST(jsn1 AS JSON(300) INLINE LENGTH 300) FROM jsonTable;

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.