Conversion rules for JSON data types

You can convert JSON data types by using the TREAT keyword. Sample code:

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

obclient> SELECT TREAT('a string scalar' AS JSON) FROM DUAL;
+------------------------------+
| TREAT('ASTRINGSCALAR'ASJSON) |
+------------------------------+
| "a string scalar"            |
+------------------------------+
1 row in set

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

Implicit conversion rules

Implicit conversion between JSON data and VARCHAR2, CLOB, or BLOB data is supported. JSON data in VARCHAR2, CLOB, and BLOB types that support JSON data is called textual JSON data, serialized JSON data, or JSON text. JSON data stored in BLOB type is not necessarily binary JSON data.

• VARCHAR2, CLOB, or BLOB data written into JSON columns is implicitly converted to the JSON type.
• JSON data written to VARCHAR2, CLOB, or BLOB columns is implicitly converted to the corresponding data type. Native binary JSON (OSON) data is converted to textual JSON data.

Other data types are not implicitly converted to the JSON type.

Explicit conversion rules

• You can call JSON constructors to convert text data to JSON data.
• You can call the JSON_VALUE function to specify the type of the return value or use the dot notation.item_method() syntax to convert JSON data to other data types.