Extract JSON array of numbers from JSON array of objects

Question

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.

• McNets: How to turn json array into postgres array?

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