Postgres Array Match More than one element-CodePudding

I am trying to write a query to match more than one element in postgres arrays

Array1	Array2	More than one element Match
a,b,c,d	a	FALSE
a,b,c,d	a,b	TRUE
a,b,c,d	a,b,c	TRUE
a,b,c,d	a,d	TRUE
a,b,c,d	a,c,e	TRUE
a,b,c,d	a,b,c,d	TRUE
a,b,c,d	a,b,e,d,f	TRUE
a,b,c,d	a,z,e,f,g,h	FALSE

I tried with all of these ops https://www.postgresql.org/docs/current/functions-array.html

Is there any inbuilt way to do it?

CodePudding user response：

Unnest and join the arrays in a scalar subquery; Count the number of resulting rows and check if it is greater than 1.

select array1, array2, 
  (
    select count(*) 
    from unnest(array1) t1 
    join unnest(array2) t2 on t1 = t2
  ) > 1
from the_table;

This is if array elements are unique and the order of elements in the arrays does not matter.

Update
To make it work correctly with repeating array elements replace unnest(arrayx) tx with (select distinct t from unnest(arrayx) t) tx

select array1, array2, 
(
 select count(*) 
 from (select distinct t from unnest(array1) t) t1 
 join (select distinct t from unnest(array2) t) t2 on t1 = t2
) > 1 from the_table;

CodePudding user response：

Write a subquery and inside of it unnest both arrays, and then make a cross join with all elements, e.g.

SELECT   
  array1, array2,
  (SELECT count(*) FILTER (WHERE el1 = el2)
   FROM unnest(t1.array1) a1(el1), unnest(t1.array2)a2(el2)) > 1
FROM t t1;

EDIT (See comments below): In case the array elements do repeat (opposite the given data sample) and you wish to count the overlap only once, just unnest the arrays with another subquery using DISTINCT, e.g.

SELECT   
  array1, array2,
  (SELECT count(*) FILTER (WHERE el1 = el2)
   FROM (SELECT DISTINCT unnest(t1.array1)) a1(el1), 
        (SELECT DISTINCT unnest(t1.array2)) a2(el2)) > 1
FROM t t1;

Demo: db<>fiddle