help with aggregation query across a second text array column - Mailing list pgsql-general

Hi everyone,

I had a dataset like so:

Servername|Primary class

----------------------------------

Server1| retail

Server2| dmz

Server3 | NA

And I used this query to summarize it for automated tools:

select environment|| ':' || string_agg(name,',') from servers group by environment order by environment;

Now my dataset has changed to:

Servername text|Primary class text|Secondary class text[1d array]

--------------------------------------------------------------------------------

Server1| retail |[‘dmz’]

There can be multiple classes for secondary but they will be the same classes as the primary classes.  Input is controlled via web pages so that should be guaranteed.  For instance, servers that have primary class of SQL but also belong to an application class.  Now I need to change my summary query to include all the servers for each class taking into account the secondary class column and I’m completely baffled on how to do so with the array.  I know the any trick and use it to match against the array when querying for specific primary and secondary classes but I can’t figure out how to generate the listing the same as the old summary query I was using.