CODEWITHSUNDEEP
postgresqljsonb
Heligo
Heligo

Reputation: 165

Postgresql remove object from jsonb array of objects by key value

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.

Upvotes: 0

Views: 392

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 2

Related Questions