In this
doc we will see some JSON functions supported by Oracle 12c, like JSON_EXISTS,
IS JSON [STRICT/LAX], AND JSON_TABLE. We will also see how to retrieve JSON
object arrays using the json_query.
JSON_EXISTS:
This
function searches the input document (for example, the contents of a clob field) for a user specified property
name and returns a boolean (true/false) if that property exists.
Consider
this transaction table having this structure:
SQL> CREATE TABLE txn
2
(txnid number(12),
3
txndate date,
4
trxdata CLOB
5
CONSTRAINT ensure_json CHECK (trxdata IS JSON));
Table created
Inserting
some test data into it…
SQL> insert into txn values
(1, to_date('01012016','ddmmyyyy'),
2 '{
3
txndetail_id :"1000",
4 itemid
: "10",
5
qty : "1",
6
value : "5"
7
}');
1 row inserted
SQL> insert into txn values
(2, to_date('01012016','ddmmyyyy'),
2 '{
3
txndetail_id :"1001",
4
itemid : "10",
5
qty : "3",
6
value : "1",
7
ref : "34867778"
8
}');
1 row inserted
SQL> commit;
Commit complete
Querying the table using JSON_EXISTS on trxdata:
SQL> select txnid, txndate,
json_value(trxdata,'$.txndetail_id') txndetail_id, trxdata
2 from
txn where json_exists( trxdata,'$.ref');
TXNID TXNDATE TXNDETAIL_ID TRXDATA
------ -----------
--------------- -------------------------------------
2 01-Jan-16 1001 {
txndetail_id :"1001",
itemid : "10",
qty : "3",
value : "1",
ref : "34867778"
}
Note:
The full
json content (trxdata) as well as the json_value-extracted fields are seen.
The
function json_exists (trxdata,’$.ref’)
searches for the ‘ref attribute within the trxdata clob content, and returns
those records who have that attribute.
The
function json_value(trxdata,’$.txndetail_id’)
extracts the value stored against the
txndetail_id attribute.
IS JSON
The IS
JSON function checks the input data to see whether it is well-formatted JSON
data. It returns a true or false accordingly. There are a number of syntax
rules that describe how JSON data must be represented, like.
- No duplicate field names
- Each field and named enclosed
in double-quotes.
- No trailing comma after last
field.
By default, Oracle’s JSON syntax checking is lax, therefore, it allows such data representations
not supported in the strict JSON definition. Below is
an example of JSON syntax checking in Oracle:
Using the IS JSON constraint on a
column:
A table’s
column can have an IS JSON constraint imposed upon it. All dml against that
column will be validated as per Oracle’s JSON syntax rules, and bad data will
be rejected.
CREATE TABLE merchant
(merchant_id number(8),
merchant_name varchar(200),
contact_persons CLOB
CONSTRAINT ensure_contact_json CHECK
(contact_persons IS JSON));
Below is
an example where a record is created bad JSON data and is rejected.
SQL> insert into merchant
values (4, 'M00000004', 'bad json data');
insert into merchant values
(4, 'M00000004', 'bad json data')
*
ERROR at line 1:
ORA-02290: check constraint
(SYS.ENSURE_CONTACT_JSON) violated
Below is a record that does get inserted - note that it has duplicate Address field and the
field names are not double-quote delimited. It is JSON but with lax syntax. We will use this record in an later example.
SQL> insert into merchant
values (3, 'M0000003',
2 '{
name : "Lax",
3 Address:{"street" : "Street 13",
4 "city" : "Singapore",
5 "zipcode" :
"54345"
6 },
7
Address:{"street" :
"test",
8 "city" : "Singapore",
9 "zipcode" :
"3435345"
10 },
11
Phone : [{"type" : "Office", "number" :
"33435335"},
12 {"type" : "Mobile",
"number" : "1343435"}]}');
1 row created.
SQL> COMMIT;
Commit complete.
Using IS JSON in an SQL statement
Its
possible to apply the new IS JSON expression on the above table to select rows
conditionally, as shown
SQL>
select * from merchant where contact_persons is not json strict;
MERCHANT_ID MERCHANT_NAME CONTACT_PERSONS
------------ ---------------
------------------------------------------------------------
3 M0000003 { name : "Lax",
Address:{"street" :
"Street 13",
"city" : "Singapore",
"zipcode" : "730713"
},
Address:{"street" :
"test",
"city" : "Singapore",
"zipcode" : "730688"
},
Phone :
[{"type" : "Office", "number" :
"33435335"},
{"type" : "Mobile",
"number" : "1343435"}]}
Note the
syntax above – It specifies IS NOT JSON
STRICT, requesting to return rows which comply to only the lax JSON syntax,
and it returns the record inserted with duplicate Address field.
Let’s
create a record complying with the strict JSON standard as below:
SQL> insert into merchant
values (1, 'M0000001',
2 '{
"name" : "Alex",
3
"Address":{"street"
: "Street 32",
4 "city" : "Singapore",
5 "zipcode" :
"999999"
6 },
7
"Phone" :
[{"type":"Office","number":"33435335"},
8 {"type":"Mobile","number":"1343435"}]}');
1 row inserted
SQL> commit;
Commit complete.
Using IS
JSON STRICT/LAX check on the table.
SQL> select * from merchant
where contact_persons is json strict;
MERCHANT_ID MERCHANT_NAME CONTACT_PERSONS
----------- ---------------
--------------------------------------------------
1 M0000001 { "name" : "Alex",
"Address":{"street"
: "Street 32",
"city" : "Buona
Vista",
"zipcode" : "123000"
},
"Phone"
:
[{"type":"Office","number":"33435335"}
,
{"type":"Mobile","number":"1343435"}
]}
All
strictly JSON compliant data is of course lax compliant also. So if we query
for IS JSON LAX, we get both STRICT and LAX-compliant records ( lax json compliant
record is in bold below)
SQL> select * from merchant
where contact_persons is json lax;
MERCHANT_ID MERCHANT_NAME CONTACT_PERSONS
------------ ---------------
------------------------------------------------------------
3 M0000003 { name : "Lax",
Address:{"street" :
"Street 13",
"city" : "Singapore",
"zipcode" : "730713"
},
Address:{"street" : "test",
"city" : "Singapore",
"zipcode" : "730688"
},
Phone : [{"type" :
"Office", "number" : "33435335"},
{"type" : "Mobile",
"number" : "1343435"}]}
1 M0000001 { "name" : "Alex",
"Address":{"street"
: "Street 32",
"city" : "Singapore",
"zipcode" : "999999"
},
"Phone" :
[{"type":"Office","number":"33435335"},
{"type":"Mobile","number":"1343435"}]}
SQL>
JSON_table function
The JSON
table function can be used to project json field values as a table.
It is
possible to do such a projection using the JSON_VALUE function (which we'll see here first), but
involving multiple JSON_VALUE function calls, one per projected column:
SQL> select txnid, txndate,
2
json_value(trxdata,'$.txndetail_id') txndetail_id,
3
json_value(trxdata,'$.itemid') itemid,
4
json_value(trxdata,'$.qty') qty,
5
json_value(trxdata,'$.value') value
6
from txn;
TXNID TXNDATE TXNDETAIL_ID ITEMID
QTY VALUE
------ -----------
--------------- ---------- ----- -----
1 01-Jan-16 1000 10 1
5
2 01-Jan-16 1001 10 3
1
The same
can be accomplished using the JSON_TABLE function. This function requires,
however, a number of inputs. Below is a
sample invocation : (the row sources txn and jt are joined implicitly)
SQL> select
jt.txndetail_id, jt.itemid, jt.quantity, jt.has_ref, jt.ref from txn,
2
json_table( trxdata, '$'
3
columns ( txndetail_id number(12) PATH '$.txndetail_id',
4 itemid number(5) PATH
'$.itemid',
5 quantity number(10,4) PATH
'$.qty',
6 value number(10,2) PATH '$.value',
7 has_ref varchar2(5 char)
exists path '$.ref',
8 ref varchar(20 char) path
'$.ref'
9
)) jt;
TXNDETAIL_ID ITEMID
QUANTITY HAS_REF REF
--------------- ----------
---------- ------- --------------------
1000 10 1 false
1001 10 3 true 34867778
Below are the inputs required by the JSON_TABLE function:
- Name of the input clob column
(trxdata).
- JSON path expression
indicating start of parse ($ in this case)
- A COLUMNS clause containing
the list of columns to project.
- Columns projected are
specified by a column name, display format, and a JSON path.
( txndetail_id number(12) PATH '$.txndetail_id',
With reference to the fragment above
Column name is txndetail_id
Format is number(12)
JSON path is specified as PATH ‘$.txndetail_id’
Note:
The REF field is not
present in all of the JSON-formatted records. Its not present for record # 1.
TXNDETAIL_ID ITEMID
QUANTITY HAS_REF REF
---------------
---------- ---------- ------- --------------------
1000 10 1 false
1001 10 3 true 34867778
The existence of the JSON field AMOUNT can be probed for and tested using the
EXISTS PATH <EXPRESSION> syntax (see below the fragment from the
JSON_TABLE function):
has_ref
varchar2(5 char) exists path '$.ref',
It returns a true/false.
Below is the explain plan for the SQL, note the JSONTABLE
EVALUATION operation that appears for the JSON_TABLE call. The cardinality
numbers, however, are totally off, despite gathering statistics.
SQL>
select jt.txndetail_id, jt.itemid, jt.quantity, jt.has_ref, jt.ref from
txn,
json_table( trxdata, '$'
columns ( txndetail_id number(12) PATH '$.txndetail_id',
itemid number(5) PATH '$.itemid',
quantity number(10,4) PATH '$.qty',
value number(10,2) PATH
'$.value',
has_ref varchar2(5 char) exists path '$.ref',
ref varchar(20 char) path '$.ref'
))
jt;
2 3
4 5 6
7 8 9
Execution Plan
----------------------------------------------------------
Plan hash value: 1752208875
-------------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 |
SELECT STATEMENT |
| 16336 | 3573K| 58
(0)| 00:00:01 |
| 1 | NESTED LOOPS |
| 16336 | 3573K| 58
(0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TXN
| 2 | 428 |
2 (0)| 00:00:01 |
| 3 | JSONTABLE EVALUATION | |
| | | |
-------------------------------------------------------------------------------
JSON_QUERY
This
function is similar to JSON_VALUE, it extracts value portion of a field named
in the JSON path expression form, but it can extract an array of values whereas
JSON_VALUE can only extract scalar values.
Consider
this simple table containing 2 records. It stores an owner id and a collection
of associated phone numbers. Each phone number has a type (mobile/home) and a
number. The number of phone entries for a record can be single or multiple.
create table phones ( owner_id
number(2), phones varchar(200));
insert into test values
(1, '{
Phone:{
"type":"mobile",
"number":"123"}
}');
insert into test values
(2, '{
Phone:[{
"type":"mobile","number":"456"},{
"type":"home","number":"789"}]
}');
In the
above sample, the first record has just a single number of type ‘mobile’.
The second
record has two entries, one each for the types mobile and home.
It is
specified as an array by wrapping it with a pair of [] brackets.
For data
in this form JSON_VALUE cannot be used for extraction of the fields. See the
result:
SQL> select owner_id,
json_value(contact_nos,'$.Phone[*].number' ) phones from phones;
OWNER_ID PHONES
----------
--------------------
1 123
2 <null>
Using
JSON_QUERY, we get:
SQL> select owner_id, json_query(contact_nos,'$.Phone[*].number'
with conditional wrapper) phones from phones;
OWNER_ID PHONES
----------
--------------------
1 ["123"]
2 ["456","789"]
Note:
- The first record has only a
single entry. Its data is returned as an array. Note the [].
- The second array has 2
elements and is returned as an array, like it should be.
- We queried with a JSON path
expression ‘$.Phone[*].number’. The field[*].property
expression means that we are expecting the field to be multi-valued and we want all values.
That expression also works for the single-valued entry for record # 1 transparently.
- We can also interrogate the
phone field, as if it contained a single-valued scalar field.
SQL>
select owner_id, json_query(contact_nos,'$.Phone.number' with conditional
wrapper) phones from phones;
OWNER_ID PHONES
----------
--------------------
1 ["123"]
2 ["456","789"]
In the above query we have tried to interrogate the phone
field as a scalar value. The expression
‘$Phone.number’ is for a single-valued field. But we can see that this expression works
implicitly for the multi-valued content in the second record also. This
behaviour is described in Oracle docs as implicit wrapping/unwrapping of data.
