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

Question

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?

You can always put the query from the function directly into your real query. — user330315
– user330315, Commented Jan 24, 2022 at 16:11
The difficulty to find a solution and the bad performances that this solution will have is the consequence of poor modeling of the database... With some tables in place of your nested arrays the solution is to use the EXCEPT operator that compare sets of data. One solution is to apply an operator to your arrays to transforme them into table and then use the EXCEPT operator. Each time you wil enter non atomic values into a colum of a table, you will have difficulties to write queries and poor performances to execute — SQLpro
– SQLpro, Commented Jan 24, 2022 at 16:18
@LaurenzAlbe the data type is returned as ARRAY. This was found by using: ``` SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourTableName' ``` — Joehat
– Joehat, Commented Jan 24, 2022 at 16:58

Laurenz Albe · Accepted Answer · 2022-01-25 03:17:53Z

1

Since there is no array_except function or operator in PostgreSQL, you have to unpack the array in your query:

SELECT id,
       CASE missing_categories
            WHEN ARRAY[NULL]::text[]
            THEN ARRAY[]::text[]
            ELSE missing_categories
       END
FROM (SELECT tab.id,
             array_agg(arr.elem) AS missing_categories
      FROM tab
         LEFT JOIN LATERAL unnest(tab.categories_a) AS arr(elem)
            ON NOT tab.categories_b @> arr.elem
      GROUP BY tab.id) AS q;

The outer query only replaces an array with a NULL element that occurs because of the outer join with an empty array.

answered Jan 25, 2022 at 3:17

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related