The new SQL/JSON Query operators (Part3: JSON_EXISTS)

This
entry is about JSON_EXISTS: JSON_EXISTS takes a path expression (potentially
with a predicate) and checks if such path selects one (or multiple) values in
the JSON data. That’s it basically, let’s show some example and use cases where you may want to use it.

First,
let’s create a table and insert some sample data:

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

INSERT INTO
customerTab VALUES ('{"id":1, "name" : "Jeff"}');

INSERT INTO
customerTab VALUES ('{"id":2, "name" : "Jane",
"status":"Gold"}');

INSERT INTO
customerTab VALUES ('{"id":3, "name" : "Jill",
"status":["Important","Gold"]}');

INSERT INTO
customerTab VALUES ('{"name" : "John",
"status":"Silver"}');

commit;

Let’s
find rows where a field named ‘status’ exists:

SELECT
count(1)
FROM
customerTab
WHERE
JSON_EXISTS (custData, '$.status');

----------
3

In the previous example only the existence of a field called 'status' mattered. What value or type it has is irrelevant.
Now, let’s
find ‘gold’ customers, i.e. customers with a status field which have a corresponding value 'gold':

SELECT
count(1)
FROM
customerTab
WHERE
JSON_EXISTS (custData, '$.status?(@ == "Gold")');

----------
2

The last
query contains a predicate, this is expressed by using a question mark followed
by a Boolean condition in parentheses. The symbol’@’ denotes the current
context, i.e. the key/value pair selected by the path expression before the
predicate. We will show further examples with predicted in future blog entries.

You may
ask at this point why you cannot use JSON_VALUE or JSON_QUERY in the WHERE clause for this
purpose? There are a couple of reasons:

• JSON_EXISTS checks for the existence of a
  value. Since JSON can have a ‘null’ value one could not differentiate a
  JSON ‘null’ from a missing value in JSON_VALUE.
• JSON_VALUE can only select and return scalar
  values. As you can see for row 3 (Jill) the status “Gold” occurs inside an
  array. Here for the evaluation of the predicate only the existence of
  “Gold” inside the array matters, not the position. Since JSON_VALUE can
  only return one (scalar SQL) value we would have to test every array
  position which is not practical. Similarly, JSON_QUERY is not suitable to compare the selected value(s) with one scalar value. 

At this
point we can use JSON_EXISTS to enforce that every customer has an ‘id’ field
using a CHECK constraint.

DELETE FROM
customerTab

WHERE NOT (JSON_EXISTS(custData, '$.id'));

1 row deleted.

ALTER TABLE
customerTab

ADD CONSTRAINT ensure_id CHECK (JSON_EXISTS(custData, '$.id'));

With
this check constraint the row for ‘John’ cannot be inserted although the data
is syntactically correct JSON. Since the JSON data has no ‘id’ field it will be
rejected.

JSON_EXISTS allows TRUE ON ERROR and FALSE ON ERROR as error handlers. 

I 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.