Array intersection as aggregate function for group by

Question

I have the following table:

CREATE TABLE person
AS
  SELECT name, preferences
  FROM ( VALUES
    ( 'John', ARRAY['pizza', 'meat'] ),
    ( 'John', ARRAY['pizza', 'spaghetti'] ),
    ( 'Bill', ARRAY['lettuce', 'pizza'] ),
    ( 'Bill', ARRAY['tomatoes'] )
  ) AS t(name, preferences);

I want to group by person with intersect(preferences) as aggregate function. So I want the following output:

person | preferences
-------------------------------
John   | ['pizza']
Bill   | []

How should this be done in SQL? I guess I need to do something like the following, but what does the X function look like?

SELECT    person.name, array_agg(X)
FROM      person
LEFT JOIN unnest(preferences) preferences
ON        true
GROUP BY  name

Answer 1

You could create your own aggregate function:

CREATE OR REPLACE FUNCTION arr_sec_agg_f(anyarray, anyarray) RETURNS anyarray
   LANGUAGE sql IMMUTABLE AS
   'SELECT CASE
              WHEN $1 IS NULL
              THEN $2
              WHEN $2 IS NULL
              THEN $1
              ELSE array_agg(x)
           END
    FROM (SELECT x FROM unnest($1) a(x)
          INTERSECT
          SELECT x FROM unnest($2) a(x)
         ) q';

CREATE AGGREGATE arr_sec_agg(anyarray) (
   SFUNC = arr_sec_agg_f(anyarray, anyarray),
   STYPE = anyarray
);

SELECT name, arr_sec_agg(preferences)
FROM person
GROUP BY name;

┌──────┬─────────────┐
│ name │ arr_sec_agg │
├──────┼─────────────┤
│ John │ {pizza}     │
│ Bill │             │
└──────┴─────────────┘
(2 rows)