Thread: How to get array of unique array values across rows?

How to get array of unique array values across rows?

From
Ken Tanzer
Date:
I have a field containing a set of codes in a varchar array, each tied to a person.

 client_id                             | integer                          | 
 service_codes                   | character varying(10)[] |

I'm trying to query this info so that I can get the list (presumably in an array) of all the values in this array, across all the records for a client. So that if a person has two records, one with ORANGE and BLUE, and one with BLUE and GREEN, I could end up with a list of ORANGE, BLUE and GREEN.  

I had hopes for:

SELECT client_id,array_agg(service_codes) FROM foo GROUP BY client_id;

But was rebuffed with "ERROR:  could not find array type for data type character varying[]"

There's probably an easy answer for this, but it's completely escaping me.  Any help appreciated.  Thanks.

Ken

--
AGENCY Software  
A data system that puts you in control
100% Free Software
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: How to get array of unique array values across rows?

From
ChoonSoo Park
Date:
Try this one.

select   X.client_id, array_agg(X.color) 
  from   (select distinct client_id, unnest(service_codes) as color 
               from foo) X
group by X.client_id;


On Tue, Mar 5, 2013 at 3:28 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:
I have a field containing a set of codes in a varchar array, each tied to a person.

 client_id                             | integer                          | 
 service_codes                   | character varying(10)[] |

I'm trying to query this info so that I can get the list (presumably in an array) of all the values in this array, across all the records for a client. So that if a person has two records, one with ORANGE and BLUE, and one with BLUE and GREEN, I could end up with a list of ORANGE, BLUE and GREEN.  

I had hopes for:

SELECT client_id,array_agg(service_codes) FROM foo GROUP BY client_id;

But was rebuffed with "ERROR:  could not find array type for data type character varying[]"

There's probably an easy answer for this, but it's completely escaping me.  Any help appreciated.  Thanks.

Ken

--
AGENCY Software  
A data system that puts you in control
100% Free Software

learn more about AGENCY or
follow the discussion.

Re: How to get array of unique array values across rows?

From
Ken Tanzer
Date:
That worked perfectly.  Thanks a lot!

On Tue, Mar 5, 2013 at 12:49 PM, ChoonSoo Park <luispark@gmail.com> wrote:
Try this one.

select   X.client_id, array_agg(X.color) 
  from   (select distinct client_id, unnest(service_codes) as color 
               from foo) X
group by X.client_id;


On Tue, Mar 5, 2013 at 3:28 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:
I have a field containing a set of codes in a varchar array, each tied to a person.

 client_id                             | integer                          | 
 service_codes                   | character varying(10)[] |

I'm trying to query this info so that I can get the list (presumably in an array) of all the values in this array, across all the records for a client. So that if a person has two records, one with ORANGE and BLUE, and one with BLUE and GREEN, I could end up with a list of ORANGE, BLUE and GREEN.  

I had hopes for:

SELECT client_id,array_agg(service_codes) FROM foo GROUP BY client_id;

But was rebuffed with "ERROR:  could not find array type for data type character varying[]"

There's probably an easy answer for this, but it's completely escaping me.  Any help appreciated.  Thanks.

Ken

--
AGENCY Software  
A data system that puts you in control
100% Free Software

learn more about AGENCY or
follow the discussion.




--
AGENCY Software  
A data system that puts you in control
100% Free Software
(253) 245-3801

learn more about AGENCY or
follow the discussion.