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
