sqlpostgresqlpostgresql-10postgresql-json

How to display a key from array of hashes in postgres?

We have a table 'Books':

id - int
data - text

Actually we should have kept data as jsonb column, but we have created this table long back and aren't planning to migrate.

We can cast it to jsonb as and when required.

My question here is

In data I have {"genres":[{"text":"Crime","id": "1"},{"text":"Romance","id": "5"}],"name":"Harry Potter"}

I need a select query to display all the text of genres comma separated for every book.

How can I do this?

I tried this,

select data::json -> 'genres'  as genres
from books
where data::json -> 'genres'  is not NULL
limit 1;

However this displays the id as well and it also displays the square brackets.

Solution

  • Use json_array_elements

    select string_agg(j->>'text',',')  from books
cross join lateral json_array_elements(data::json->'genres') as j

    Demo

    If you want for every book, add group by id or whatever uniquely identifies a book.