[ { "name" : "A", "key" : "KA" }, { "name" : "B", "key" : "KB" } ]
Given a column containing the above data,
I use select jsonb_path_query_array(column, '$.key')
to get the output [KA, KB]
However this doesn’t work in Postgres-11. Are there any alternatives for the same ?
Advertisement
Answer
Yes. This yields a Postgres array. Use jsonb_agg
instead of array_agg
if you need a JSON array.
select array_agg(j ->> 'key') from jsonb_array_elements(column) t(j);
Update
select (select array_agg(j ->> 'key') from jsonb_array_elements(column) t(j)) from the_table;