I have a table containing a json array of objects similar to this:

id    |   record
name1 | [{"a":0, "b":x}, {"a":1, "b":y}, {"a":2, "b":z}, ...]

I would like to get a table containing a json array of only, say, the "a" values:

id    |   record
name1 | [0, 1, 2, ...]

I use PostgreSQL 11, so the latest functions are acceptable.


1 Answer 1


You need to first unnest the array elements, and then aggregate back each value:

select id, 
       (select jsonb_agg(t -> 'a') from jsonb_array_elements(record) as x(t)) as record
from the_table;

Online example: https://rextester.com/ZONHTW97204

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.