Oracle Database 12.1.0.2 has now native support build-in for handling JSON (Javascript Object Notation) data. Oracle Database supports JSON natively with relational database features, including transactions, indexing, declarative querying, and views.
JSON data doesn’t need a JSON datatype. JSON data can be loaded in the already existing datatypes for string like: VARCHAR2, NVARCHAR2, CLOB, etc. The JSON data is available afterwards via the new functions and operators like: JSON_TABLE, JSON_VALUE, JSON_EXISTS or IS JSON.
IS JSON condition
With the latter, IS JSON, can a table be restricted on STRICT or LAX (lazy) mode to allow or disallow JSON data only. For example a table can have on column level a IS JSON constrain which restricts this column to be contain JSON formatted data only.
For example, the following table definition demonstrates multiple strict, lax, JSON syntax examples on column level. The default, when the level is not mentioned, is lazy JSON handling.
Also in SQL this condition, JSON data or not, can be checked via the following construct.
SQL> insert into JSON_TABLE
2 select JSON_DOCUMENT
3 from STAGING_TABLE
4 where JSON_DOCUMENT IS JSON
5 /
Loading JSON data
Besides loading JSON data like you used to like loading strings in varchar2 columns, you can also load it in CLOB columns which probably is more useful being not restricted by 4000 characters or 32K.
Loading of JSON C(LOB) data can be done via the following external table construct (based on Tim Hall’s post here:
http://www.oracle-base.com/articles/10g/load-lob-data-using-sql-loader.php and here http://www.oracle-base.com/articles/10g/ExternalTablesContainingLobData.php):
Hope to be of help with your first steps handling JSON data natively in your 12.1.0.2 database environment.
M.
