X
  • Monday, May 11, 2015

The new SQL/JSON Query operators (Part2: JSON_QUERY)

By: Beda Hammerschmidt | Consulting (Coding) Member of Technical Staff

Next in our introduction to the new SQL/JSON operators is
JSON_QUERY. In the previous two posts we have shown JSON_VALUE. So why do we need another operator?

JSON_QUERY is complementary to JSON_VALUE. JSON_VALUE takes JSON a input and returns one
scalar SQL value. Think of JSON_VALUE as the ‘bridge’ from JSON to SQL. (We will
use it later for relational concepts that operate on SQL values like virtual columns, functional
indexing, etc).

JSON_QUERY
on the other hand always returns JSON, i.e. an object or an array. This
implies that JSON_QUERY could be chained (JSON in – JSON out) versus the output
of JSON_VALUE can never be used as the input for another operator that expect a
JSON input.

Okay, sounds all very complex but it is not. Let’s start
with an example and create a table with test data.

drop table
customerTab;

CREATE TABLE
customerTab (custData VARCHAR2(4000),
CONSTRAINT ensure_json2 CHECK (custData IS
JSON STRICT));

INSERT INTO
customerTab

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

INSERT INTO
customerTab
VALUES ('{
"custNo":3,
"name" : "Jill",
"status":["Important","Gold"],
"address": {
"Street": "Broadway
2",
"City": "Belmont",
"zip": 94065
}}');

commit;

As one can see every customer has an address which is a JSON
object. We can return this embedded object using JSON_QUERY.

SELECT
JSON_QUERY(custData, '$.address')
FROM
customerTab;

-----------------------------------------------------
{"Street":"Main
Rd 1","City":"San Jose","zip":95002}
{"Street":"Broadway
2","City":"Belmont","zip":94065}

Similarly,
we can select an inner array

SELECT
JSON_QUERY(custData, '$.status')
FROM customerTab;

----------------------------------------------
["Important","Gold"]

But,
what happened here? We have selected the field called ‘status’ but only one
value shows up, the other value is NULL! Maybe this NULL comes from a masked
error (remember, the default error handler is to return NULL). So, let’s make
sure any error gets reported. We run the same query with
ERROR ON ERROR instead
of default
NULL ON ERROR.

SELECT
JSON_QUERY(custData, '$.status' ERROR ON ERROR)
FROM customerTab;

ORA-40480: result
cannot be returned without array wrapper

Now,
what does that mean? Basically, 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 we give you a hint: wrap it with an array and it can be
returned. Let’s do it then:

SELECT
JSON_QUERY(custData, '$.status' WITH ARRAY WRAPPER)
FROM
customerTab;

-----------------------------------------------------------
["Gold"]
[["Important","Gold"]]

Okay,
now every selected value gets wrapped inside an array and the values can be
returned (the array wrapper turned it into JSON). Also, we can get back the
selected value by unwrapping it (in a client application or in JavaScript in
the web browser). But sometimes we may
want to only wrap a value if needed and not wrap a value which is already JSON.
Can we do that? Yes, by adding they keyword ‘
CONDITIONAL’:

SELECT JSON_QUERY(custData, '$.status' WITH CONDITIONAL ARRAY WRAPPER)
FROM
customerTab;

--------------------------------------------------------------------------------
["Gold"]
["Important","Gold"]

Here we
go: the scalar value “Gold” needed wrapping, the other row’s value was already
JSON, it did not get another array wrapper.

But
JSON_QUERY
can do more:

Let’s
insert a customer with multiple addresses:

INSERT INTO
customerTab
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. Similarly we can
select one address out of the array because each address itself is a JSON
object. But how can we select the city names? We use a wildcard (*) to access
all members of the array:

SELECT
JSON_QUERY(custData, '$.address[*].City' WITH ARRAY WRAPPER)
FROMcustomerTab;

----------------------------------------------------------------
["San
Jose"]
["Belmont"]
["Atherton","San
Francisco"]

This is
where our JavaScript inspired path language exceeds the capabilities of
JavaScript navigation. Using the wildcard one can select more than one member
of the array. ‘*’ selects all. But we can also select a subset like [1,2,3] or
[1 to 5] or even [1,2, 5 to 10].

We invite you to provide feedback; for instance as a comment under this posting or in our LinkedIn group (https://www.linkedin.com/grp/home?gid=6966766).

You can also follow the Twitter hashtag #OracleJSON and #OracleDocDB.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha