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