How to remove duplicate values by comparing two array type column values in Postgresql?

Question

I want to remove duplicate values by comparing two array type column values.

I want to get the result of subtracting the fruit I already ate from my favorite fruit. But if there ara 2 apples in your favorite fruit column and 1 apple in eaten fruit column, I'd like to leave 1 apple in favorite fruit column.

how to get this result?

I don't know the answer no matter how hard I think about it.

table : favorite

idx | favorite_fruit | fruit_eaten

1     | {apple,banana,orange}         | {apple,orange}

---

2     | {apple,apple,banana,orange}   | {apple,banana,orange}

The result I want

idx | Result

1     | {banana}    

---

2     | {apple} 

Answer 1

There might be better ways to do this, but here's my five minute stab at it:

SELECT
    array_agg(remaining.fruit) AS remaining_favorites
FROM (
    SELECT
        fruit, row_number() OVER (PARTITION BY fruit) AS ordinal
    FROM
        unnest('{apple,apple,banana,orange}'::text[]) favortites (fruit)
    EXCEPT
    SELECT
        fruit, row_number() OVER (PARTITION BY fruit) AS ordinal
    FROM
        unnest('{apple, banana, orange}'::text[]) eaten (fruit)) remaining;