Skip to content
Advertisement

Equivalent of jsonb_path_query_array in Postgresql-11

[
  {
    "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;
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement