SQL JSON query operators – Part II

Next in our introduction of the new SQL JSON query operator is JSON_QUERY, this is complementary to JSON_VALUE.

JSON_VALUE takes JSON as input and return the scalar SQL value as output. JSON_QUERY on the other hand returns a JSON (i.e, an Array or an Object), this implies that JSON_QUERY could be chained (JSON in – JSON out) versus the output of the JSON_VALUE can never be used as the input for another operator that expects a JSON input.

create table t(cust_details varchar2(4000)
constraint is_valid_json check(
cust_details IS JSON STRICT) );

insert into t(cust_details)  
values( '{
 "custNo":2,
 "name" : "Jane",
 "status":"Gold",
 "address": {
 "Street": "Main Rd 1",
 "City": "San Jose",
 "zip": 95002
}}');

insert into t(cust_details)
values ('{
 "custNo":3,
 "name" : "Jill",
 "status":["Important","Gold"],
 "address": {
 "Street": "Broadway 2",
  "City": "Belmont",
 "zip": 94065
}}');
commit;



We can see each customer has an address which is a JSON object; we can return this embedded object using JSON_QUERY.

demo@ORA12C> select json_query( cust_details, '$.address') from t;

JSON_QUERY(CUST_DETAILS,'$.ADDRESS')
----------------------------------------------------------------------
{"Street":"Main Rd 1","City":"San Jose","zip":95002}
{"Street":"Broadway 2","City":"Belmont","zip":94065}

2 rows selected.

demo@ORA12C>

Similarly we can select an inner array, say the “status” of each customer.

demo@ORA12C> select json_query( cust_details, '$.status') from t;

JSON_QUERY(CUST_DETAILS,'$.STATUS')
-------------------------------------------------------------------

["Important","Gold"]

2 rows selected.

demo@ORA12C>


So what happened? We have a field called “status” but only one value shows up, the other value is NULL, may be this NULL comes from a masked error ( the default error handler is to return NULL ), so let’s make sure that errors get reported. 

demo@ORA12C> select json_query( cust_details, '$.status' ERROR ON ERROR) from t;
select json_query( cust_details, '$.status' ERROR ON ERROR) from t
                                                                 *
ERROR at line 1:
ORA-40480: result cannot be returned without array wrapper


demo@ORA12C>


So what does this error mean? Basically it is the path expression ($.status) has selected something, but it cannot be returned because it is not a JSON value (not an object or array), but this can be resolved with Array wrappers.


demo@ORA12C> select json_query( cust_details, '$.status' WITH ARRAY WRAPPER) from t;

JSON_QUERY(CUST_DETAILS,'$.STATUS'WITHARRAYWRAPPER)
--------------------------------------------------------------------------------------
["Gold"]
[["Important","Gold"]]

2 rows selected.

demo@ORA12C>


Now every selected value gets wrapped inside an array and the values are returned. But sometimes we may want to only wrap a value if needed and not wrap a value which is already a JSON; this can be done by adding CONDITIONAL keyword to it.


demo@ORA12C> select json_query( cust_details, '$.status' WITH CONDITIONAL ARRAY WRAPPER) from t;

JSON_QUERY(CUST_DETAILS,'$.STATUS'WITHCONDITIONALARRAYWRAPPER)
-----------------------------------------------------------------
["Gold"]
["Important","Gold"]

2 rows selected.

Given this JSON_QUERY can do even more.


insert into t(cust_details)
values ('{
 "custNo":3,
 "name" : "Jim",
 "status": "Silver",
 "address":[
 {
 "Street": "Fastlane 4",
 "City": "Atherton",
 "zip": 94027
 },
 {
 "Street": "Slowlane 5",
 "City": "San Francisco",
 "zip": 94105
 } ]
}');
commit;


Obviously, since both addresses inside one array, we can select the array as a whole.

demo@ORA12C> select json_query( cust_details, '$.address' ) from t;

JSON_QUERY(CUST_DETAILS,'$.ADDRESS')
--------------------------------------------------------------------------------------------------------------------
{"Street":"Main Rd 1","City":"San Jose","zip":95002}
{"Street":"Broadway 2","City":"Belmont","zip":94065}
[{"Street":"Fastlane 4","City":"Atherton","zip":94027},{"Street":"Slowlane 5","City":"San Francisco","zip":94105}]

3 rows selected.

demo@ORA12C>


We can also select one address out of each array.


demo@ORA12C> select json_query( cust_details, '$.address[0]' ) from t;

JSON_QUERY(CUST_DETAILS,'$.ADDRESS[0]')
-------------------------------------------------------------------------
{"Street":"Main Rd 1","City":"San Jose","zip":95002}
{"Street":"Broadway 2","City":"Belmont","zip":94065}
{"Street":"Fastlane 4","City":"Atherton","zip":94027}

3 rows selected.


So, how can we select the City names? The attribute “City” is inside “address” object in the first two JSON document, for the third JSON document this “City” is inside the “address” array – so how to retrieve this City names? 


Using JSON_VALUE, we couldn’t get the full set of values, across all the three JSON documents.


demo@ORA12C> select json_value( cust_details ,'$.address.City') from t;

JSON_VALUE(CUST_DETAILS,'$.ADDRESS.CITY')
---------------------------------------------------------------------------
San Jose
Belmont


3 rows selected.

demo@ORA12C>


But, with JSON_QUERY, we could get a full set of values like this.


demo@ORA12C> select json_query( cust_details, '$.address[*].City' with conditional array wrapper ) from t;

JSON_QUERY(CUST_DETAILS,'$.ADDRESS[*].CITY'WITHCONDITIONALARRAYWRAPPER)
------------------------------------------------------------------------------------------------------------
["San Jose"]
["Belmont"]
["Atherton","San Francisco"]

3 rows selected.

demo@ORA12C>