1

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.

  • Is eventorders.cities the JSONB column? – a_horse_with_no_name Jun 25 at 5:35
  • 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 – daniel9x Jun 25 at 10:35
2

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;
  • 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. – daniel9x Jun 24 at 20:58
  • @daniel9x: see my edit. – a_horse_with_no_name Jun 25 at 10:49
  • Thanks so much! The function solution works like a charm and is exactly what I needed! – daniel9x Jun 25 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.