flatten JSON array into comma delimited list

Question

I am trying to construct a SELECT statement which will take a JSONB column in the following format:

{
 "Cities": [
 {
 "Name": "Atlanta"
 },
 {
 "Name": "London"
 },
 {
 "Name": "New York"
 }
 ]
}

The output of the column result set needs to be in the following format:

Atlanta, London, New York

UPDATE

@a_horse_with_no_name's answer below is correct, but my requirements are actually a little more complicated than originally posted. I actually need to fit this select in to a larger (join) query as follows:

select eo.citydata.cities.names <-- Flattened Comma delimited JSON Array
from orderline o 
join eventorders eo on eo.orderlineid = o.id
join events e on e.id = eo.eventid
where e.id = '123'

Clearly the answer provided will need to be modified in order for this to work and I'm struggling to figure out how to do it.

• user1822: Is eventorders.cities the JSONB column?
• daniel9x: In this scenario it’s meant to represent the list of Name values within each city object in the cities array. Let’s say eo.citydata is the actual Jsonb column name

Answer 1

Unnest the array, then aggregate back:

select string_agg(city, ',')
from (
 select x.val ->> 'Name' as city
 from the_table t
 cross join jsonb_array_elements(t.the_column -> 'Cities') as x(val)
) t;

If you have a bigger query, use that in a derived table:

select string_agg(t2.city, ',')
from (
 select x.val ->> 'Name' as city
 from (
 select eo.citydata
 from orderline o 
 join eventorders eo on eo.orderlineid = o.id
 join events e on e.id = eo.eventid
 where e.id = 123
 ) t1
 cross join jsonb_array_elements(t1.citydata -> 'Cities') as x(val)
) t2;

---

Alternatively - if you need that very often - you can create a function that does this:

create function get_element_list(p_value jsonb, p_keyname text)
 returns text
as 
$$ 
 select string_agg(x.val ->> p_keyname, ',')
 from jsonb_array_elements(p_value) as x(val);
$$
language sql;

Then you can use it like this:

select get_element_list(eo.citydata -> 'Cities', 'Name')
from orderline o 
 join eventorders eo on eo.orderlineid = o.id
 join events e on e.id = eo.eventid
where e.id = 123;

• daniel9x: Thanks so much for the reply! Unfortunately I seemed to have oversimplified my initial question and have updated it accordingly. Not sure how to implement this solution into the join query I need to put this into. Again, thanks for your help.
• user1822: @daniel9x: see my edit.
• daniel9x: Thanks so much! The function solution works like a charm and is exactly what I needed!