clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36271 in the last week).

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"]
 hidden batch(es)