Aggregate per Group returns Duplicates

Question

I have the following scenario a user belongs to several Groups. The Groups are organized in a hierachy:

I want to aggregate all the Person Information of the Groups the User Belongs to. Eg. Person 1 belongs to Group 7. And Group 7 has connections to the following Groups 1,5,7,8 (complete branch)

I wrote the following query:

SELECT person.id, array_agg(info)
FROM person LEFT JOIN person_group 
ON ARRAY[person.id] && path
LEFT JOIN person_information ON
ARRAY[person_information.id] && path
GROUP BY person.id;

Problem with this query is that I get duplicated Information:

I don't want to use UNIQUE on the array_agg(info) field. Rather I would like write my join so that I only get UNIQUE rows of the Person Information Table.

How would I do that? Thank you

Possible duplicate of [How to remove duplicates, which are generated with array\_agg postgres function](https://stackoverflow.com/questions/26363742/how-to-remove-duplicates-which-are-generated-with-array-agg-postgres-function) — Sebastian Brosch, Jan 18 '19 at 10:35
Please do [not crosspost](http://meta.stackexchange.com/q/64068/157328). Also asked here: https://dba.stackexchange.com/questions/227473 — a_horse_with_no_name, Jan 18 '19 at 10:37
*so that I only get UNIQUE rows of the Person Information Table* - your shown rows are unique? — Sebastian Brosch, Jan 18 '19 at 10:41

score 0 · Answer 1 · answered Jan 18 '19 at 10:58

I solved it with this query:

SELECT person.id,  array_agg(info) FROM person 
LEFT JOIN person_information ON
person_information.id IN (
  SELECT DISTINCT(UNNEST(path)) 
  FROM person_group
  WHERE ARRAY[person.group_id] && path
) GROUP BY person.id;

Do you have a better approach?

Aggregate per Group returns Duplicates

1 Answers1