help with aggregation query across a second text array column
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.
Scot Kreienkamp | Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive | Monroe, Michigan 48162 | * 734-384-6403 | | * 7349151444 | * Scot.Kreienkamp@la-z-boy.com<mailto:%7BE-mail%7D>
www<http://www.la-z-boy.com/>.la-z-boy.com<http://www.la-z-boy.com/> | facebook.<https://www.facebook.com/lazboy>com<https://www.facebook.com/lazboy>/<https://www.facebook.com/lazboy>lazboy<http://facebook.com/lazboy> | twitter.com/lazboy<https://twitter.com/lazboy> | youtube.com/<https://www.youtube.com/user/lazboy>lazboy<https://www.youtube.com/user/lazboy>
[cid:lzbVertical_hres.jpg]
This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.
Attachments:
On Nov 12, 2018, at 9:40 AM, Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com> wrote:
…
I’m not too confident in my answer here (there could be a better way), but this might help. You could use the `unnest` function to transform the array into multiple rows. For example, given a table like
create table t1 (env text, cls text, cls2 text[]);
I can query it like:
select env, string_agg(cls, ‘,’)
from (select env, cls from t1
union
select env, unnest(cls2) from t1) t
group by env;
Rob
Thank you very much Rob, that concept worked out nicely. I would never have thought of unioning the table to itself with unnest.
Here's my final query:
select environment ||':' || string_agg(name, ',')
from (
select name,environment from servers union select name,unnest(auditenvironment) as environment from servers order by name) t
group by environment order by environment;
Cheers!
Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 7349151444 | Email: Scot.Kreienkamp@la-z-boy.com
-----Original Message-----
From: Rob Nikander [mailto:rob.nikander@gmail.com]
Sent: Monday, November 12, 2018 10:30 AM
To: Postgres General <pgsql-general@postgresql.org>
Cc: Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>
Subject: Re: help with aggregation query across a second text array columnOn Nov 12, 2018, at 9:40 AM, Scot Kreienkamp <Scot.Kreienkamp@la-z-
boy.com> wrote:
…
I’m not too confident in my answer here (there could be a better way), but this
might help. You could use the `unnest` function to transform the array into
multiple rows. For example, given a table likecreate table t1 (env text, cls text, cls2 text[]);
I can query it like:
select env, string_agg(cls, ‘,’)
from (select env, cls from t1
union
select env, unnest(cls2) from t1) t
group by env;Rob
This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.