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

Question

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

Answers 1
Added at 2016-12-28 02:12
Tags
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.

Answers to

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

nr: #1 dodano: 2016-12-28 03:12

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

Source Show
◀ Wstecz