Postgres - Function to return the intersection of 2 ARRAYs?

user_161922 0
Viewed 51392

In postgresql, you can use the && operator to return t (true) if two arrays have common members, i.e. they overlap. Is there a function/operator that will return what those common members are?

i.e. something like this

select arrray_intersection(ARRAY[1, 4, 2], ARRAY[2, 3]);
ARRAY[2]
postgresql sql
8 Answers
dwc_573010

You can use this function:

CREATE OR REPLACE FUNCTION intersection(anyarray, anyarray) RETURNS anyarray as $$
SELECT ARRAY(
    SELECT $1[i]
    FROM generate_series( array_lower($1, 1), array_upper($1, 1) ) i
    WHERE ARRAY[$1[i]] && $2
);
$$ language sql;

It should work with any kind of array, and you can use it like this:

SELECT intersection('{4,2,6}'::INT4[], '{2,3,4}'::INT4[]);
Admin1 
SELECT  ARRAY
        (
        SELECT  a1[s]
        FROM    generate_series(array_lower(a1, 1), array_upper(a1, 1)) s
        INTERSECT
        SELECT  a2[s]
        FROM    generate_series(array_lower(a2, 1), array_upper(a2, 1)) s
        )
FROM    (
        SELECT  array['two', 'four', 'six'] AS a1, array['four', 'six', 'eight'] AS a2
        ) q

Works on non-integer arrays too.

user_551590

aother option, for those using for example postgresql, is to take benefits of the jsonb_path_exists tool to check for example if at least one of the elements of an array is also available in another array!

assuming for example here below that we have a table activity_role containing a column "rights" format JSONB, in which we could find for example

table: activity_role

id rights
1 {"folders":["write","read","assign"]}

if we want to check if for example id 1 has at least one of the following rights ['sign', approve', 'write'], we simply run

SELECT jsonb_path_exists(ar.rights, '$.folders[*] ? (@ == $requested_right)',jsonb_build_object('requested_right', array['sign', 'approve', 'write']) AS authorized FROM activity_rights ar WHERE ar.id = 1;

result will be

authorized

true

because at least 'write' matches in the array1 (rights->'folders') and in the array2 ( array['sign', approve', 'write'] )

user_196819550

How the inline operation && would look like in SELECT statement where we are selecting from a 2 tables and filtering only where two array columns intersect (at least 1 value)


SELECT
    table_1 .list::text[],
    table_2 .list::text[]
FROM
    table_1,
    table_2
WHERE
    table_1.list::text[] && table_2.list::text[]
user_165603970
Related