JSON Conditionals: JSON_EXISTS, JSON_TEXTCONTAINS

November 22, 2017

in DDL, ORA Errors, SQL

In the last two articles, we saw the means and reasons for storing JSON data in Oracle and ways of retrieving JSON data from Oracle. In this article, we will explore ways of implementing true/false tests on JSON data using conditionals: JSON_EXISTS, JSON_TEXTCONTAINS.

JSON Conditional Logic JSON_EXISTS JSON_TEXTCONTAINS

JSON conditionals check for the existence of specified paths/values within JSON documents. They are typically applied as row filters in the SQL WHERE clause.

We’ll see how JSON conditionals work against the data in our old CUSTOMER table. CUSTOMER table structure:

SQL> -- CUSTOMER table with IS JSON check on metadata column
SQL> CREATE TABLE customer
  2    (custid    VARCHAR2(5) NOT NULL PRIMARY KEY,
  3     custname  VARCHAR2(50) NOT NULL,
  4     metadata  VARCHAR2(1000)
  5     CONSTRAINT chk_json CHECK (metadata IS JSON)); 

Table created.

Note that the column ‘metadata’ in the CUSTOMER table holds JSON content; this column has an IS JSON constraint on it to ensure the JSON documents are well-formed.

CUSTOMER data:

SQL> select * from customer;
 
 CUSTID CUSTNAME METADATA
 ------ -------- --------------------------------------
 1      Susan    {"Mobile"       : "(555) 555-1234",
                     "OfficePh"     : "(651)-555-1999",
                     "HomePh"       : "(651) 399-9262",
                    }
 
 2      Martin   {"Mobile"       : "(555) 321-4311",
                     "HomePh"       : "(627)-444-2888",
                     "HomePhAlt"    : "(627) 587-6623"
                   }

With that table and test data in an Oracle 12.1 environment, we’ll run SQLs to search within the JSON documents based on JSON conditionals.

JSON_EXISTS

JSON_EXISTS checks if a specified JSON path exists in JSON data or not.

The JSON path is evaluated from the left, starting with ‘$’ followed by zero or more steps: each step can be an object or an array. If, after parsing the full path, a match is found, JSON_EXISTS returns TRUE else it returns FALSE.

In the CUSTOMER table’s data, let’s look for the path to "OfficePh" in the JSON column.

-- JSON_EXISTS: Select rows where the JSON
-- document contains an OfficePh attribute
select * from customer
where json_exists
  (metadata, '$.OfficePh');

When run:

SQL> -- JSON_EXISTS: Select rows where the JSON 
SQL> -- document contains an OfficePh attribute
SQL> select * from customer
  2  where json_exists
  3    (metadata, '$.OfficePh');

CUSTID CUSTNAME   METADATA
------ ---------- --------------------------------------
1      Susan      {"Mobile"       : "(555) 555-1234",
                      "OfficePh"     : "(651)-555-1999",
                      "HomePh"       : "(651) 399-9262",
                     }

True enough – JSON_EXISTS returns only CUSTID 1, which has the attribute OfficePh, and omits CUSTID 2, which does not.

Another path that matches no documents:

-- JSON_EXISTS: Select rows where the JSON 
-- document contains a non-existent path
select * from customer
where json_exists
  (metadata, '$.NotAValidPath');

When run:

SQL> -- JSON_EXISTS: Select rows where the JSON 
SQL> -- document contains a non-existent path
SQL> select * from customer
  2  where json_exists
  3    (metadata, '$.NotAValidPath');

no rows selected

JSON_exists_on_error_clause

JSON_EXISTS takes an optional JSON_exists_on_error_clause: this clause tells the function what to return if the JSON document is not well-formed. The options are TRUE / FALSE / ERROR (an ORA-error to indicate malformed JSON).


How do we test this on our CUSTOMER table? Remember that we have an IS JSON constraint on column CUSTOMER.metadata – this means the the data in the column is always well-formed.

So we don’t have a ready situation to test the JSON_exists_on_error_clause.

But wait – we can run our test on another non-JSON column – say, CUSTOMER.custname – and see what it does.

SQL> -- JSON_EXISTS with TRUE ON ERROR: Select from
SQL> -- JSON document that is not well-formed
SQL> -- Expected: Should return the rows
SQL> select custid, custname from customer
  2  where json_exists
  3    (custname, '$.AnyRandomPath' TRUE ON ERROR);

CUSTID CUSTNAME
------ ----------
1      Susan
2      Martin

SQL> -- JSON_EXISTS with FALSE ON ERROR: Select from
SQL> -- JSON document that is not well-formed
SQL> -- Expected: Should not return the rows
SQL> select custid, custname from customer
  2  where json_exists
  3    (custname, '$.AnyRandomPath' FALSE ON ERROR);

no rows selected

SQL> -- JSON_EXISTS with ERROR ON ERROR: Select from
SQL> -- JSON document that is not well-formed
SQL> -- Expected: Should return an error
SQL> select custid, custname from customer
  2  where json_exists
  3    (custname, '$.AnyRandomPath' ERROR ON ERROR);
select custid, custname from customer
                             *
ERROR at line 1:
ORA-40441: JSON syntax error

So, when JSON_EXISTS + JSON_exists_on_error_clause is used as the only WHERE clause filter on a column with no well-formed JSON:

  • TRUE ON ERROR returns all rows
  • FALSE ON ERROR returns no rows
  • ERROR ON ERROR (default) returns ORA-40441: JSON syntax error 

JSON_TEXTCONTAINS

JSON_TEXTCONTAINS checks if a specified string exists in JSON property values or not.

The JSON path is evaluated the same way as for JSON_EXISTS (starting with $, left to right), with a key difference: each step must be an object; arrays are not supported with JSON_TEXTCONTAINS.

The string being searched for must be a discrete word e.g. ‘4’ will match ‘4 Privet Drive’, not ’42’.

Example: SQL to search for a specific office phone number in the JSON metadata column:

select * from customer
where json_textcontains
  (metadata, '$.OfficePh', '(651)-555-1999');

For JSON_TEXTCONTAINS to work on a column, a JSON search index, which is an Oracle Text index designed for use with JSON,  must be defined on the column.

Without this index, JSON_TEXTCONTAINS will lead you to an ORA-40467.

ORA-40467: JSON_TEXTCONTAINS() cannot be evaluated without JavaScript Object Notation (JSON) index

SQL> -- JSON_TEXTCONTAINS on a column
SQL> -- without JSON search index
SQL> select * from customer
  2  where json_textcontains
  3    (metadata, '$.OfficePh', '(651)-555-1999');
select * from customer
              *
ERROR at line 1:
ORA-40467: JSON_TEXTCONTAINS() cannot
be evaluated without JavaScript Object
Notation (JSON) index

Define a JSON search index

SQL> -- Define JSON search index
SQL> -- to enable use of JSON_TEXTCONTAINS (12.1 syntax)
SQL> create index cust_ix
  2    on customer (metadata)
  3    indextype is ctxsys.context
  4    parameters
  5    ('section group ctxsys.json_section_group sync (on commit)');

[Note: There’s a simpler syntax for search index creation in Oracle 12.2 – details in the 12.2 JSON Developer’s Guide.]

Run the SQL with JSON_TEXTCONTAINS (post JSON search index):

SQL> -- JSON_TEXTCONTAINS on a column
SQL> -- with JSON search index; look for a specific
SQL> -- value in OfficePh
SQL> select * from customer
  2  where json_textcontains
  3    (metadata, '$.OfficePh', '(651)-555-1999');
  
CUSTID CUSTNAME   METADATA
------ ---------- --------------------------------------
1      Susan      {"Mobile"       : "(555) 555-1234",
                      "OfficePh"     : "(651)-555-1999",
                      "HomePh"       : "(651) 399-9262",
                     }

That worked!

Summary

This article describes JSON conditional logic functions JSON_EXISTS and JSON_TEXTCONTAINS that can check JSON documents for the existence of specified paths/values.

Note that in the name-value pair structure of JSON documents:

  • JSON_EXISTS checks the names
  • JSON_TEXTCONTAINS checks the values

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: