Syed Asad Abbas Zaidi Syed Asad Abbas Zaidi - 20 days ago 5
SQL Question

Postgres : Get name value from json array using SELECT

In a column say details the values are stored as:

[{"id":"key1","name":"Concerts \u0026amp; Tour Dates"},{"id":"key2","name":"Nightlife \u0026amp; Singles"}]


Column Type: text

I need to get comma separated value for name using a single SELECT statement.Also need the amp sign.

Result:
[Concerts & Tour Dates,Nightlife & Singles]

Answer

This will do it:

select string_agg(d.e ->> 'name', ',')
from the_table, json_array_elements(that_text_column::json) as d(e);

To change & to & use the replace function:

select string_agg(replace(d.e ->> 'name','&', '&'), ',')
from the_table, json_array_elements(that_text_column::json) as d(e);

Online example: http://rextester.com/XAPDTC62018