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]
Since 8.4, there are useful builtins in Postgres which make the function from the first answer easier and possibly faster (that's what EXPLAIN tells me, anyway: "(cost=0.00..0.07 rows=1 width=64)" for this query vs. "(cost=0.00..60.02 rows=1 width=64)" for the original one).
The simplified code is:
SELECT ARRAY
(
SELECT UNNEST(a1)
INTERSECT
SELECT UNNEST(a2)
)
FROM (
SELECT array['two', 'four', 'six'] AS a1
, array['four', 'six', 'eight'] AS a2
) q;
and yeah, you can turn it into a function:
CREATE FUNCTION array_intersect(anyarray, anyarray)
RETURNS anyarray
language sql
as $FUNCTION$
SELECT ARRAY(
SELECT UNNEST($1)
INTERSECT
SELECT UNNEST($2)
);
$FUNCTION$;
which you can call as
SELECT array_intersect(array['two', 'four', 'six']
, array['four', 'six', 'eight']);
But you can just as well call it inline too:
SELECT array(select unnest(array['two', 'four', 'six']) intersect
select unnest(array['four', 'six', 'eight']));
function array_intersect(text[], text[]) does not exist. The last example works fine though!
array_intersect function as IMMUTABLE.
Try & instead of &&
See PostgreSQL Docs for more.
just another method..
SELECT ARRAY( SELECT * FROM UNNEST( $1 ) WHERE UNNEST = ANY( $2 ) );
If you don't mind installing an extension, the intarray extension provides the & operator to do this as @dwc pointed out.:
SELECT ARRAY[1, 4, 2] & ARRAY[2, 3];
Returns {2}.
ERROR: operator does not exist: integer[] & integer[] LINE 1: SELECT ARRAY[1,4,2] & ARRAY[2,3] ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
& operator but it did not provide a usage example.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[]);
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.
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'] )
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[]
&&operator was introduced in version 8.2 some time after the introduction of arrays: postgresql.org/docs/8.2/functions-array.html