Extract values from array of json elements in Postgresql [duplicate]

Question

Im trying to extract an array of values from an array of objects in Postgresql, essentially map over the array and extract.

For instance I have a value of:

[{"id":123,"quantity":4,"amount":180},
 {"id":563,"quantity":1,"amount":675},
 {"id":563,"quantity":1,"amount":875},
 ...]

And I would like to extract just the amounts in an array, e.g: [180,675,875...]

Is there a simple way of doing this in postgres?

Thank you for any help in advance

SelVazi · Accepted Answer · 2023-05-05 08:52:20Z

1

Use jsonb_array_elements to extract the array into a set of JSON values then reaggregate your array using array_agg :

select array_agg(elm->>'amount')
from mytable t
CROSS JOIN jsonb_array_elements(t.my_json) elm
group by my_json

answered May 5, 2023 at 8:52

SelVazi

16.2k2 gold badges19 silver badges35 bronze badges

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

1 Answer 1