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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

User_C7DAK

Same issue - following

1 - 1

Post Details

Added on Jul 22 2022
4 comments
628 views