I have the table below.

╔════════════════════╦════════════════════╦═════════════╗
║id                  ║arr1                ║arr2         ║  
╠════════════════════╬════════════════════╬═════════════╣             
║1                   ║{1,2,3,4}           ║{2,1,7}      ║
║2                   ║{0}                 ║{3,4,5}      ║
╚════════════════════╩════════════════════╩═════════════╝

I want to find out the elements which are in arr1 and not in arr2.
Expected output

╔════════════════════╦════════════════════╗
║id                  ║diff                ║  
╠════════════════════╬════════════════════╣             
║1                   ║{3,4}               ║      
║2                   ║{0}                 ║
╚════════════════════╩════════════════════╝

If I have 2 individual arrays, I can do as follows:

select array_agg(elements)
from (
  select unnest(array[0])
  except
  select unnest(array[3,4,5])
) t (elements)

But I am unable to integrate this code to work by selecting from my table. Any help would be highly appreciated. Thank you!!

CodePudding user response：

You should use except for each id and after that group by for each group

Demo

with diff_data as (
    select id, unnest(arr1) as data
    from test_table
        except
    select id, unnest(arr2) as data
    from test_table
)
select id, array_agg(data order by data) as diff
from diff_data
group by id

CodePudding user response：

I would write a function for this:

create function array_diff(p_one int[], p_other int[])
  returns int[]
as
$$
  select array_agg(item)
  from (
     select *
     from unnest(p_one) item
     except
     select *
     from unnest(p_other)
  ) t 
$$
language sql
stable;

Then you can use it like this:

select id, array_diff(arr1, arr2)
from the_table