By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE demo(
id int not NULL,
value jsonb Not NULL
);
INSERT into demo VALUES
(1, '[{"entityId": 1, "entityType": "type1", "childrenIds": []}, {"entityId": 10, "entityType": "type5", "childrenIds": [30, 20]}]'::jsonb),
(2, '[{"entityId": 1, "entityType": "type2"}, {"entityId": 15, "entityType": "type1", "childrenIds": [4, 20]}]'::jsonb),
(3, '[{"entityId": 1, "entityType": "type3", "childrenIds": [5, 4]}, {"entityId": 25, "entityType": "type4", "childrenIds": [30, 4]}]'::jsonb),
(4, '[{"entityId": 35, "entityType": "type4", "childrenIds": [35, 4]}]'::jsonb);
SELECT id, jsonb_path_query_array(value, '$[*].entityType')
FROM demo WHERE VALUE @? '$[*] ? (@.entityId == 1 && (!exists (@.childrenIds) || exists (@.childrenIds ? (@[*] == 4))))' ;
4 rows affected
| id | jsonb_path_query_array |
|---|---|
| 2 | ["type2", "type1"] |
| 3 | ["type3", "type4"] |