0

DB table looks like this: 

state |  city    | contact
---------------------------
NY    |  city1   | person1;person2;person3       
NY    |  city2   | person4;person5;person6
NY    |  city3   | null
CA    |  city1   | person7;person8;person9
CA    |  city2   | person10;person11;person12

I want to group by state and turn city into an array and split contact on semicolon to turn into an array: 

state   |    city.               | contact 
------------------------------------------------
NY      |  {city1, city2, city3} | {person1,person2,person3,person4,person5,person6,null}
CA      |  {city1, city2}        | {person7,person8,person9,person10,person11,person12}

This aggregates contacts for each state into 1 row and should handle null values, but it's not being split on semicolons:

select 
    t.state,
    coalesce(nullif(array(Select x from unnest(array_agg(t.contact order by t.city)) x where x is not null, '{}', '{}') as "contacts_agg"
    -- t.city, ^^ same logic as above 
from table as t 
group by 
    t.state

How can I modify my query to group by state while aggregating all city and contact rows for each state?

e1v1s
  • 365
  • 6
  • 18

1 Answers1

0

You can unnest the contacts and reaggregate:

select t.state, array_agg(distinct city) as city, array_agg(distinct contact) as contacts
from t cross join
      regexp_split_to_table(contacts, ';') c(contact)
group by t.state;

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I need to do a couple of inner joins to get to the `contact` column in the first place. Say that `contact` is a column I can access as the result of two inner joins, when I add this logic I get an error saying `"there is an entry for table .... but it cannot be referenced in this part of the query"` – e1v1s May 24 '20 at 04:25
  • @elvis . . . Not only does this answer your question, but it provides a db fiddle illustrating that it work. You can take your existing query and make it a CTE. If you can't figure that you ask a *new* question. This one is answered. – Gordon Linoff May 25 '20 at 00:58