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

enter image description here

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:

enter image description here

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

  • 1
    It would be nice if you share your desired output. Thanks. – Luan Huynh Jan 18 at 12:42
  • And maybe a dbfiddle – dezso Jan 18 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.