Postgres Array Match More than one element

Question

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?

What if you have d,c,b,a and a,b,c,d? (order of elements in an array matter) — user330315
– user330315, Commented Nov 10, 2021 at 8:03

Stefanov.sm · Accepted Answer · 2021-11-10 08:43:50Z

2

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;

edited Nov 10, 2021 at 8:43

answered Nov 10, 2021 at 8:13

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jim Jones · Accepted Answer · 2021-11-10 10:39:16Z

1

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

edited Nov 10, 2021 at 10:39

answered Nov 10, 2021 at 8:22

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

3 Comments

Stefanov.sm Over a year ago

This query produces wrong results if the array elements are not unique.

Jim Jones Over a year ago

@Stefanov.sm I also thought about it and in fact you're right. I just left this option out because the data sample suggested a different scenario: I edited my question to make your point. thanks for pointing out. cheers and happy coding

Stefanov.sm Over a year ago

Cheers to you too

Collectives™ on Stack Overflow

Postgres Array Match More than one element

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related