How to flatten a jsonb array and return it in a table?-CodePudding

I've tried all sorts of methods to get from this:

    qualif_id    |                                                                    qualif_assessment_formats
----------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------
 QL0000000000007 |
 QL0000000000008 |
 QL0000000000009 |
 QL0000000000001 | [{"af_sum": 420, "af_branch": "BR0000000000005", "af_currency": "EUR", "af_app_deadline": "2023-04-22 12:00:00", "af_acquire_datetime": "2023-04-22 14:00:00"}]

to this:

    qualif_id    |   qualif_assessment_formats
----------------- -------------------------------
 QL0000000000007 |
 QL0000000000008 |
 QL0000000000009 |
 QL0000000000001 | ["BR0000000000005"]

Speaking simply, looking for an analoguous of the flatMap function in js. Like Array.flatMap(x => x.af_branch)

Tried and jsonb_to_recordset and jsonb_populate_recordset, though it does not seem to work without the REPEAT function in postgresql. Any ideas?

Thanks very much in advance

CodePudding user response：

If you are using Postgres 12 or later, you can use a JSON path function:

select qualif_id,
       jsonb_path_query_array(qualif_assessment_formats, '$[*].af_branch')
from the_table;