Intersection of multiple arrays in PostgreSQL

Question

I have a view defined as:

 CREATE VIEW View1 AS 
 SELECT Field1, Field2, array_agg(Field3) AS AggField 
 FROM Table1 
 GROUP BY Field1, Field2;

What I would like to do is get the intersection of the arrays in AggField with something like:

SELECT intersection(AggField) FROM View1 WHERE Field2 = 'SomeValue';

Is this at all possible, or is there a better way to achieve what I want?

Answer 1

The closest thing to an array intersection that I can think of is this:

select array_agg(e)
from (
    select unnest(a1)
    intersect
    select unnest(a2)
) as dt(e)

This assumes that a1 and a2 are single dimension arrays with the same type of elements. You could wrap that up in a function something like this:

create function array_intersect(a1 int[], a2 int[]) returns int[] as $$
declare
    ret int[];
begin
    -- The reason for the kludgy NULL handling comes later.
    if a1 is null then
        return a2;
    elseif a2 is null then
        return a1;
    end if;
    select array_agg(e) into ret
    from (
        select unnest(a1)
        intersect
        select unnest(a2)
    ) as dt(e);
    return ret;
end;
$$ language plpgsql;

Then you could do things like this:

=> select array_intersect(ARRAY[2,4,6,8,10], ARRAY[1,2,3,4,5,6,7,8,9,10]);
 array_intersect 
-----------------
 {6,2,4,10,8}
(1 row)

Note that this doesn't guarantee any particular order in the returned array but you can fix that if you care about it. Then you could create your own aggregate function:

-- Pre-9.1
create aggregate array_intersect_agg(
    sfunc    = array_intersect,
    basetype = int[],
    stype    = int[],
    initcond = NULL
);

-- 9.1+ (AFAIK, I don't have 9.1 handy at the moment
-- see the comments below.
create aggregate array_intersect_agg(int[]) (
    sfunc = array_intersect,
    stype = int[]
);

And now we see why array_intersect does funny and somewhat kludgey things with NULLs. We need an initial value for the aggregation that behaves like the universal set and we can use NULL for that (yes, this smells a bit off but I can't think of anything better off the top of my head).

Once all this is in place, you can do things like this:

> select * from stuff;
    a    
---------
 {1,2,3}
 {1,2,3}
 {3,4,5}
(3 rows)

> select array_intersect_agg(a) from stuff;
 array_intersect_agg 
---------------------
 {3}
(1 row)

Not exactly simple or efficient but maybe a reasonable starting point and better than nothing at all.

Useful references:

• array_agg
• create aggregate
• create function
• PL/pgSQL
• unnest