Oracle 12C IS JSON SQL Example

In the last post, we saw a basic introduction to using JSON in Oracle 12C. In that post, we used a check constraint to enforce the incoming data being inserted to be of type JSON. However, you may have existing tables in your database, on which you cannot enforce such constraints due to business limitations and in this article we can see how to use the new SQL constructs in Oracle 12C IS JSON and IS NOT JSON to filter records of our interest, with simple examples.Needless to say, you need to have 12C version 12.1.0.2 and above to get these examples working.

IS JSON and IS NOT JSON

These constructs help to check if the data being inspected confirms to JSON standards or not. See the example below for a better understanding.

SQL> create table t(myjson clob)
  2  /

Table created.

SQL> insert into t values ('this is plain text')
  2  ;

1 row created.

SQL> insert into t values ('<abc>this is xml </abc>');

1 row created.

SQL> insert into t values ('{"Welcome":"Json","test":"tested"}');

1 row created.

/* Here we select all the records that are of type JSON using IS JSON */
SQL> select * from t where myjson is json
  2  /

MYJSON
--------------------------------------------------------------------------------
{"Welcome":"Json","test":"tested"}

/* Here we select all the records that are not of type JSON */

SQL> select * from t where myjson is not json
  2  /

MYJSON
--------------------------------------------------------------------------------
this is plain text
<abc>this is xml </abc>

SQL> 

We can add some more records that appear to be of JSON format and see the output;

Support for STRICT and LAX JSON

Oracle 12C also supports STRICT and LAX keywords in IS JSON syntax. A strict JSON syntax requires key and values to be enclosed in double quotes, whereas a LAX allows for some exclusions. Here is an example that explains this better;

SQL> insert into t values ('{"Name" : "Value"}'); /* Strict */

1 row created.

SQL> insert into t values ('{"Number": 10 }'); /* Strict */

1 row created.

SQL> insert into t values('{"ISSTRICT" : FALSE}'); /* Lax because of uppercase in FALSE */

1 row created.

SQL> insert into t values ('{"Percentage" :00045}'); /* Lax because of leading zeros */

1 row created.

SQL> insert into t values ('{"Fraction" : .14 }'); /* Lax due to absence of 0 before decimal point */

1 row created.

SQL> insert into t values ('{"Negative" : -4 }'); /* Strict */

1 row created.

SQL> select * from t where myjson is json strict; /* returns strict rows */

MYJSON
--------------------------------------------------------------------------------
{"Name" : "Value"}
{"Number": 10 }
{"Negative" : -4 }

SQL> select * from t where myjson is json lax; /* returns everything as stricts are also lax :) */

MYJSON
--------------------------------------------------------------------------------
{"Name" : "Value"}
{"Number": 10 }
{"ISSTRICT" : FALSE}
{"Percentage" :00045}
{"Fraction" : .14 }
{"Negative" : -4 }

6 rows selected.

SQL> select * from t where myjson IS NOT JSON; /* Every record is JSON */

no rows selected


/* The below query returns all records that are not STRICT JSON */
SQL> select * from t where myjson IS NOT JSON STRICT;

MYJSON
--------------------------------------------------------------------------------
{"ISSTRICT" : FALSE}
{"Percentage" :00045}
{"Fraction" : .14 }

/* However, it also returns non JSON records ; as an example */

SQL> insert into t values ('this is a dummy record');

1 row created.

SQL> select * from t where myjson is not json strict;

MYJSON
--------------------------------------------------------------------------------
{"ISSTRICT" : FALSE}
{"Percentage" :00045}
{"Fraction" : .14 }
this is a dummy record /* we dont need this */

/* To get only LAX JSON records, first eliminate not STRICT JSON and include only LAX */

SQL> select * from t where myjson is not json strict and myjson is json lax;

MYJSON
--------------------------------------------------------------------------------
{"ISSTRICT" : FALSE}
{"Percentage" :00045}
{"Fraction" : .14 }

/span>

A full list of JSON scenarios that are considered LAX is available in Oracle documentation. 

JSON Records With Unique Keys

IS JSON also provides a WITH UNIQUE KEYS clause to filter records with unique keys alone. This is better understood with an example as provided below:

SQL> insert into t values ('{"a":100,"b":200}');          

1 row created.

SQL> insert into t values ('{"a" : 50, "a" : 60 }');

1 row created.

/* With unique keys only */

SQL> select * from t where myjson is json with unique keys;

MYJSON
--------------------------------------------------------------------------------
{"a":100,"b":200}

/* without unique keys i.e. the default */

SQL> select * from t where myjson is json without unique keys;

MYJSON
--------------------------------------------------------------------------------
{"a":100,"b":200}
{"a" : 50, "a" : 60 }

/* SQL to output strict JSON with unique keys */

SQL> insert into t values ('{"a": FALSE}');

1 row created.

SQL> select * from t where myjson is json strict with unique keys;

MYJSON
--------------------------------------------------------------------------------
{"a":100,"b":200}


SQL> insert into t values ('{"a" : False, "a" : True }');                            

1 row created.

/* SQL to output lax json with unique keys */

SQL> select * from t where myjson is not json strict and myjson is json lax with unique keys;

MYJSON
--------------------------------------------------------------------------------
{"a": FALSE}

/* SQL to output lax json with non unique keys */

SQL> select * from t where myjson is not json strict and myjson is json lax without unique keys;

MYJSON
--------------------------------------------------------------------------------
{"a": FALSE}
{"a" : False, "a" : True }