So I've got this table, with formats text and jsonb respectively.
qualif_id | qualif_assessment_formats
-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QL0000000000003 | [{"af_sum": 432, "af_branch": "BR0000000000001", "af_currency": "EUR", "af_app_deadline": "25-10-2022 12:00:00", "af_acquire_datetime": "10-11-2022"}]
QL0000000000004 | [{"af_sum": 432, "af_branch": "BR0000000000001", "af_currency": "EUR", "af_app_deadline": "25-10-2022 12:00:00", "af_acquire_datetime": "10-11-2022"}, {"af_sum": 432, "af_branch": "BR0000000000005", "af_currency": "EUR", "af_app_deadline": "25-10-2022 12:00:00", "af_acquire_datetime": "10-11-2022"}]
What I'm trying to do is to make a query, which would remove the object from each array, which contains a specific af_branch value, for example 'BR0000000000001'. So the result would look like that:
qualif_id | qualif_assessment_formats
-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QL0000000000003 | []
QL0000000000004 | [{"af_sum": 432, "af_branch": "BR0000000000005", "af_currency": "EUR", "af_app_deadline": "25-10-2022 12:00:00", "af_acquire_datetime": "10-11-2022"}]
Again, having only 'BR0000000000001' for entry. Tried both using jsonb_to_recodset, then filter by it, then jsonb_agg to bring it back to the state... And also with jsonb_query_path_array, but all seems not working.
You can use a JSON path function:
select qualif_id,
jsonb_path_query_array(qualif_assessment_formats,
'$[*] ? (@.af_branch == "BR0000000000005")')
from the_table
The function iterates through all array elements and returns those that match the condition specified after the ? operator.