Conversion rules for JSON data type

To display a string type, such as VARCHAR2 or CLOB, as a JSON type, you can use the CAST expression. Here is an example:

obclient> SELECT CAST('{"a":1}' AS JSON) FROM DUAL;
+-----------------------+
| CAST('{"A":1}'ASJSON) |
+-----------------------+
| {"a":1}               |
+-----------------------+
1 row in set

obclient> SELECT CAST('{}' AS JSON) FROM DUAL;
+------------------+
| CAST('{}'ASJSON) |
+------------------+
| {}               |
+------------------+
1 row in set

obclient> SELECT CAST('[1, 2, 3]' AS JSON) FROM DUAL;
+-----------------------+
| CAST('[1,2,3]'ASJSON) |
+-----------------------+
| [1,2,3]               |
+-----------------------+
1 row in set

To use a string as a JSON string, you can use the TREAT expression. However, this expression does not validate the JSON syntax, and the returned value is still a string, not a JSON type. Therefore, it is not recommended to use this expression, as it does not fully utilize the JSON features. It is only used for compatibility with Oracle's historical behavior. Here is an example:

obclient> SELECT TREAT('{"a":1}' AS JSON) FROM DUAL;
+------------------------+
| TREAT('{"A":1}'ASJSON) |
+------------------------+
| {"a": 1}               |
+------------------------+
1 row in set

obclient> SELECT TREAT('{}' AS JSON) FROM DUAL;
+-------------------+
| TREAT('{}'ASJSON) |
+-------------------+
| {}                |
+-------------------+
1 row in set

Implicit conversion rules

Implicit conversion exists between the JSON type and the VARCHAR2, CLOB, and BLOB types. JSON represented by VARCHAR2, CLOB, and BLOB is called Textual JSON or Serialized JSON, which is JSON text. Note that storing JSON in a BLOB does not mean it is stored as JSON Binary.

• When VARCHAR2, CLOB, or BLOB types are written to a JSON column, they are implicitly converted to the JSON type.
• When the JSON type is written to a VARCHAR2, CLOB, or BLOB column, it is implicitly converted to the corresponding data type, from JSON to Textual JSON.

Other types are not implicitly converted to the JSON type.

Explicit conversion rules

• Text types can be converted to the JSON type using JSON constructors.
• JSON can be converted to other types using the JSON_VALUE function to specify the return value type or using dot notation.item_method().