Oracle JSON_EXISTS Function

JSON_EXISTS tests a JSON document with a path expression and is true when the path finds something. It answers two kinds of question: whether a field is present at all, and whether the document satisfies a filter, such as a loyalty tier of Gold or Platinum, or an array that contains a given airport. Like REGEXP_LIKE, it is a condition, used in WHERE, CASE, and CHECK constraints.

Code for This Guide

The main example is in the examples/json folder of the Oracle Database 26ai code repository on GitHub, with its output. It queries NIMBUS, the sample schema of a fictional airline, whose CUSTOMERS table stores a loyalty profile as JSON. Install it with the scripts in the setup/nimbus folder.

It comes from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

json_exists(expr, 'path'
            [passing expr as "name" [, ...]]
            [{true | false | error} on error])

A loyalty document looks like {"memberId":"NM100037","tier":"Gold","points":48210,"preferences":{"seat":"aisle","meal":"vegetarian"},"favoriteAirports":["DXB","LHR"]}.

Test Whether a Field Exists

The simplest path names a field; JSON_EXISTS is true when the document has it.

Example:

-- does the document have the field at all?
select count(*) as customers,
       count(case when json_exists(loyalty, '$.tier') then 1 end)             as with_tier,
       count(case when json_exists(loyalty, '$.preferences.meal') then 1 end) as with_meal,
       count(case when loyalty is null then 1 end)                            as no_loyalty
from   customers;

Output:

   CUSTOMERS    WITH_TIER    WITH_MEAL    NO_LOYALTY
____________ ____________ ____________ _____________
         120          107          107            13

The 13 customers without a loyalty profile have a NULL document, for which JSON_EXISTS is not true. A field that exists with the JSON value null still counts as existing.

Filter Documents

A filter in the path tests values. The first query counts Gold and Platinum members. The second finds members whose favorite airports include Auckland and who have more than 100,000 points, with the values passed as variables.

Example:

select count(*) as gold_or_better
from   customers
where  json_exists(loyalty, '$?(@.tier == "Gold" || @.tier == "Platinum")');

select customer_id, json_value(loyalty, '$.points') as points
from   customers
where  json_exists(loyalty, '$.favoriteAirports?(@ == $code)' passing 'AKL' as "code")
and    json_exists(loyalty, '$?(@.points > $min)' passing 100000 as "min");

Output:

   GOLD_OR_BETTER
_________________
               43

   CUSTOMER_ID POINTS
______________ _________
            55 157247

Inside a filter, @ is the current item: the whole document in '$?(...)', or each array element in '$.favoriteAirports?(...)'. PASSING keeps literals out of the path, so the same statement can be reused with bind variables.

Things to Know

• A search index on a JSON column lets JSON_EXISTS conditions use an index on large tables.
• Use JSON_EXISTS to test presence or a condition, and JSON_VALUE to return a value.
• Filters support comparison operators, && and ||, exists(), and functions such as starts with and like_regex.

Related Guides

• Oracle JSON_VALUE Function
• Oracle JSON_QUERY Function
• Oracle JSON_TABLE Function

Conclusion

JSON_EXISTS is true when a SQL/JSON path finds something in a document. Use a plain path to check that a field is present, a filter to test values, and PASSING to supply the values as variables.