Unnest ids from arrays based on priority with no duplication

Here is the sample schema for my database:

Priority  | Productive | UnProductive | Neutral |
-------------------------------------------------
  High    |   [1, 2]   |      []      |  [4, 5] |
  Medium  |   [3, 4]   |    [5, 7]    |   [2]   |
  Low     |     [1]    |    [2, 6]    |    []   |

NOTE: These numbers are actually UUIDs in my database and no numbers will be repeated in the same row.

I want to unnest the category from productive, unproductive and neutral such that:

• Not any from 1 to 7 numbers are repeated and

• If the number is already captured by High Priority it will not be captured by Medium and same goes for Low.

Expected output:

Productive: [1, 2, 3]  # no 4 because it have been captured by neutral High priority
UnProductive: [7, 6]  # no 5 and 2 because it have been captured before
Neutral: [4, 5]  # no 2 because it is caputed before

This can be cast as greatest-n-per-group problem in disguise. So DISTINCT ON is my core feature after unnesting and before aggregating back:

To produce one row like in the original table:

WITH cte AS (
   SELECT *, CASE priority WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 WHEN 'Low' THEN 3 END AS prio
   FROM   tbl
   )
SELECT array_agg(id ORDER BY prio) FILTER (WHERE state = 'p') AS productive  -- ORDER BY prio?
     , array_agg(id ORDER BY prio) FILTER (WHERE state = 'u') AS unproductive
     , array_agg(id ORDER BY prio) FILTER (WHERE state = 'n') AS neutral
FROM  (
   SELECT DISTINCT ON (id) *
   FROM (
                SELECT unnest(neutral) AS id, prio, 'n' AS state FROM cte
      UNION ALL SELECT unnest(productive)   , prio, 'p'          FROM cte
      UNION ALL SELECT unnest(unproductive) , prio, 'u'          FROM cte
      ) sub1
   ORDER  BY id, prio, state
   ) sub2;

Or with pivoted result:

WITH cte AS (
   SELECT *, CASE priority WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 WHEN 'Low' THEN 3 END AS prio
   FROM   tbl
   )
SELECT state, array_agg(id ORDER BY prio)  -- ORDER BY prio?
FROM  (
   SELECT DISTINCT ON (id) *
   FROM (
                SELECT unnest(neutral) AS id, prio, 'neutral' AS state FROM cte
      UNION ALL SELECT unnest(productive)   , prio, 'productive'       FROM cte
      UNION ALL SELECT unnest(unproductive) , prio, 'unproductive'     FROM cte
      ) sub1
   ORDER  BY id, prio, state
   ) sub2
GROUP  BY 1;

db<>fiddle here

Further reading:

• Select first row in each GROUP BY group?

You did not mention performance in this question. Consider the answer to your previous question for this:

• Remap array columns to single column with value

StackExchange Database Administrators Q#287027, answer score: 2