Casting JSON Data Examples | Teradata Vantage - Casting JSON Data Examples - Advanced SQL Engine - Teradata Database

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);

> 1.E0

SELECT CAST(NEW JSON('{"a":"a"}')..a AS INTEGER);

> ?

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.

Examples

The following SELECT statements all return {"hello":"world"}, but as different data types and storage formats:

SELECT CAST('160000000268656C6C6F0006000000776F726C640000'xb AS JSON STORAGE FORMAT BSON);

SELECT CAST('{"hello":"world"}' AS JSON STORAGE FORMAT BSON);

SELECT CAST('{"hello":"world"}' AS JSON STORAGE FORMAT UBJSON);

SELECT CAST(NEW JSON('{"hello":"world"}') AS JSON STORAGE FORMAT BSON);

SELECT CAST(NEW JSON('{"hello":"world"}') AS JSON STORAGE FORMAT UBJSON);

SELECT CAST(NEW JSON('{"hello":"world"}',LATIN) AS JSON STORAGE FORMAT BSON);

SELECT CAST(NEW JSON('{"hello":"world"}',LATIN) AS JSON STORAGE FORMAT UBJSON);

SELECT CAST(NEW JSON('{"hello":"world"}',UNICODE) AS JSON STORAGE FORMAT BSON);

SELECT CAST(NEW JSON('{"hello":"world"}',UNICODE) AS JSON STORAGE FORMAT UBJSON);

SELECT CAST(NEW JSON('160000000268656C6C6F0006000000776F726C640000'xb, BSON) AS JSON STORAGE FORMAT UBJSON);