2

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.

3

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, privacy policy and cookie policy

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