Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle 19c + JSON_EXISTS: JSON Path null or not available. How to pick the data

BanchaJul 22 2022 — edited Jul 22 2022

CREATE TABLE employee
(id number NOT NULL,
CONSTRAINT employee_pk PRIMARY KEY,
employee_doc CLOB,
CONSTRAINT employee_doc_ensure_json CHECK (employee_doc IS JSON (STRICT WITH UNIQUE KEYS)));

INSERT INTO employee
VALUES (1,
'{"EmployeeId": 100,
"Age": 40,
"Gender" : "M"}');

INSERT INTO employee
VALUES (2,
'{"EmployeeId": 101,
"Age": 40,
"Gender" : ""}');

INSERT INTO employee
VALUES (3,
'{"EmployeeId": 102,
"Age": 40}');

INSERT INTO employee
VALUES (4,
'{"EmployeeId": 103,
"Age": 40,
"Gender" : "F"}');

SELECT *
FROM employee
WHERE JSON_EXISTS(employee_doc,'$?( @.Age == "40" && @.gender == 'M')')
I am getting first record correctly.
But now my requirement is gender should be consider as optional. If gender tag is there in json then it should consider otherwise not.
Example: employee 103 should not populate as gender is F. All other 3 record should populate.
My expectation is all 3 record should display.
Can someone help me for the logic

Comments
Post Details
Added on Jul 22 2022
4 comments
818 views