'PostgreSQL: Without using a function, how can nested arrays be compared?

I am using PostgreSQL in Metabase (I have read-only rights so functions won't work)

I have the following nested data in two columns:

Categories_A    Categories_B
{"A"}           {"B","F","C"}
{"B","A"}       {"Z","B","F"}
{"B","F"}       {"A","E","R"}

I would like to return in a new column the missing category/categories in categories_B when compared to categories_A - what is in Categories_A that is not in categories_B. So ideally:

Categories_A    Categories_B      Missing_Category
{"A"}           {"B","F","C"}     {"A"}
{"B","A"}       {"Z","B","F"}     {"A"}
{"B","F"}       {"A","E","R"}     {"B","F"}

This is the code I have but it doesn't work due to the "read-only" rights I have in Metabase.

create function array_except(p_one anyarray, p_two anyarray)
  returns anyarray
as
$$
  select array_agg(e)
  from (
    select e
    from unnest(p_one)
    except
    select e
    from unnest(p_two)
  )
$$
language plpgsql
immutable
;

select categories_a, categories_b, 
       array_except(categories_a, categories_b) as missing_categories
from my_table

How can I achieve this without a function?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source