2 added 996 characters in body
source | link

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;

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;

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;
1
source | link

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;