1

I have data from a postgres tables that looks like this: 

Server    |    Database    | Contact 

server1   |    db1         | contact 1
server1   |    db2         | contact 2
server1   |    db3         | contact 3 
server2   |    db1         | contact 4
server2   |    db2         | contact 3

But I need to transform it to pass to a front end view in this format: 

Server    | Database        | Contact 

server1   | {db1, db2, db3} | {contact1, contact2, contact3}
server2   | {db1, db2}      | {contact3, contact4}

For every unique value in the Server column I want to create an array column out of every other column in my SQL query. So far I have something like this but it does not seem to create an array out of the Server column (Contact column excluded for clarity): 

SELECT     "table1"."Server"
            COALESCE( NULLIF( array 
           ( 
                  select DISTINCT x 
                  FROM   unnest( array_agg(DISTINCT "table1"."Server") ) x 
                  WHERE  x IS NOT NULL ), '{}' ), '{"No Data"}' ) AS "serverList" , 
           COALESCE( NULLIF( array 
           ( 
                  SELECT x 
                  FROM   unnest( array_agg(DISTINCT "table1"."Database") ) x 
                  WHERE  x IS NOT NULL ), '{}' ), '{"No Data"}' ) AS "databseList"
FROM       "table1"                                     
GROUP BY   
           "table1"."Server"

How can I correctly create array columns out of unique entries in the Server column?

e1v1s
  • 365
  • 6
  • 18

1 Answers1

1

How about array_agg()?

select server, array_agg(database) as databases, array_agg(contact) as contacts
from t
group by server;

If the values need to be unique in the arrays, use distinct.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • how about distinct `database` and get corresponding `contact`. meaning `len(database) == len(contact)` in selecte result. – Jiang YD Aug 03 '20 at 08:55