Merge arrays in a specific order without duplicates with group_by

I have a table user_groups containing the following columns:

• usr_id - this is the user ID.
• groups - this is an array with group identifiers in a given group family.
• priority - priority.

I need a query that will return an array containing all user groups merged into one row per user.
For example, a user with id = 1 has two entries in "user_groups" one containing a family of groups: [1, 2, 3, 5] with priority = 1, the other containing a family of groups: [2, 5, 10, 12], where priority = 2.

The result for this user should be an array with a list of groups with a lower priority first, i.e. [1, 2, 3, 5] and then each family with a higher priority should be added, taking into account that the identifiers of these groups cannot be repeated, means is that for a priority 2 entry, I no longer add groups 2 and 5, because these were already included earlier in the family of lower priority groups. I hope it's not confusing.

View for that:

Result should be:

If we changed priority in 1 row to highest than in second row then the merge should happen in the order first second row, then first row. The result should be:

I've already tried unpacking the array with unnest and then using array_agg with ORDER BY to combine it back together, but that merges the duplicate elements:

SELECT 
    usr_id, ARRAY_AGG(grp ORDER BY prior_) 
FROM (SELECT unnest("groups") AS grp, * FROM user_groups ) AS "groups" 
GROUP BY usr_id 

So I came up with the idea to combine these arrays with the DISTINCT clause in array_agg, but I cannot use it with ORDER BY (Get an error: SQL Error [42P10]: ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list), and removing ORDER BY causes me to lose the order that matters.

Version of PostgreSQL: 13.11

Any ideas?

Rewriting ypur query will result in your wanted result

the basic problem is we need to filter out double numbers and keep still the priority, as all doubled numbers should appear in the lower priority, we need to eliminate the double numbers with GROUP By usr_id,grp for all users, and the choose for every number the smallest priority

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)
;

> status
> CREATE TABLE
>

> status
> 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

fiddle