By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE user_groups
("usr_id" int, "groups" integer[], "priority" int)
;
INSERT INTO user_groups
("usr_id", "groups", "priority")
VALUES
(1, '{1, 2, 3, 5}', 3),
(1, '{2, 5, 10, 12}', 2),
(2, '{1, 2, 3, 5}', 1),
(2, '{2, 5, 10, 12}', 2)
;
CREATE TABLE
INSERT 0 4
WITH CTE As
(SELECT usr_id,grp, MIN(priority) priority FROM user_groups,unnest("groups") AS grp
GROUP By usr_id,grp)
SELECT usr_id, ARRAY_AGG( grp ORDER BY "priority",grp) as grps
FROM CTE
GROUP BY usr_id
| usr_id | grps |
|---|---|
| 1 | {2,5,10,12,1,3} |
| 2 | {1,2,3,5,10,12} |
SELECT 2