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.

Walker Farrow · Accepted Answer · 2016-12-28 02:11:02Z

3

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...

answered Dec 28, 2016 at 2:11

Walker Farrow

4,0289 gold badges36 silver badges58 bronze badges

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

Collectives™ on Stack Overflow

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

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