Postgresql remove object from jsonb array of objects by key value

Question

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.

user330315 · Accepted Answer · 2022-02-06 14:48:12Z

2

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.

answered Feb 6, 2022 at 14:48

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql remove object from jsonb array of objects by key value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related