How to loop through jsonb array of objects to get values of keys within objects Postgres

Question

I am using Postges 9.4 and I have a table that has a jsonb field as an array of objects.

Here is a sample field:

[{"pk": 224, "arbitrary_value": 50, "description": "United States"}, {"pk": 125, "arbitrary_value": 10, "description": "Canada"}]

In this array field, there are TWO objects. But it is a variable length.

I have a query that looks like this:

select jsonb
     , array_agg( ... )
from public.jsonb_data

In the array_agg(...) column I need to be able to return an array of the pk value as contained in EACH object of the jsonb field.

The result set should look something like:

| jsonb | array_agg |
| ----- | --------- |
| [{"pk": 224, "arbitrary_value": 50, "description": "United States"}, {"pk": 125, "arbitrary_value": 10, "description": "Canada"}] | {224,125} |

Sorry, not sure how to make tables in this editor...

I have tried messing around with the various jsonb operators available in Postgres 9.4 but I would like any pointers if somebody has them.

Answer 1

Eureka!

I found it, using the jsonb_to_recordset() function.

Here is the query I made:

select primary_key
    , array_agg(e.pk)
from public.jsonb_data, jsonb_to_recordset(jsonb_field) as e( pk int )
group by primary_key
order by primary_key

This then gave me a perfect output of:

| Primary Key | jsonb_array |
| ----------- | ----------- |
| 1           | {224,152}   |
| 2           | {224,186}   |
| 3           | {224}       |

Hope this helps somebody...