Equivalent of jsonb_path_query_array in Postgresql-11

Question

[
  {
    "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 ?

Yeah, and that code worked when I was trying it out locally. However when I tried deploying it on my company's server, I found out that function wasn't available, since my company's currently using version 11. — Suraj
– Suraj, Commented Sep 2, 2021 at 17:20

Stefanov.sm · Accepted Answer · 2021-09-02 17:50:24Z

2

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;

answered Sep 2, 2021 at 17:15

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks. Should I replace t with the table name.