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