Conversion rules for JSON data types

You can convert string types, such as VARCHAR2 and CLOB, into JSON data types by using the CAST expression. Here are some examples:

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

You can also convert string data types by using the TREAT expression. However, this expression does not verify the JSON syntax and returns only JSON strings, instead of data in the JSON format, which do not support JSON features. We recommend that you do not use this expression except for compatibility with historical Oracle behavior. Here are some examples:

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