Select rows where elements of two arrays don't match a pattern

Question

I try to write a query where I select rows based one a LIKE pattern that has to match none of the entries of an array. What I have is the current one

SELECT * FROM mytable WHERE 
NOT (EXISTS (SELECT * from unnest(a) WHERE  unnest LIKE '%XYZ%' ))) 

Actually what I want is pairs - so it is more like

SELECT * FROM mytable WHERE 
NOT (
  (EXISTS (select * from unnest(a) as A, unnest(b) as B  WHERE  A||B LIKE '%xyz%abc%' )) OR
  (EXISTS (select * from unnest(a) as A, unnest(b) as B  WHERE  A||B LIKE '%abc%xyz%' ))
)

This works for me but looks rather unwieldy. Is there prettier/more efficient solution to this? What I am find the most irksome is the SELECT as part of the WHERE clause to unroll arrays.

some points:

• In my case xyz and abc will not appear together as substrings in a single array entry (e.g. nothing like xyzblaabcexists)
• Both arrays have exactly the same number of elements (they are actually derived from another query)
• no null elements (but even if that would still work as then I don't have a pair of abc and xyz)

Edit:

Clarification: a row contains A as {rxyz,foo, bar} and B as {other, abc, this} should not be returned as it contains the rxyz in one array and abc in the other

Example:

SELECT * FROM(
 SELECT *
    FROM   (select 1 as ID, '{rxyz,foo,bar}'::varchar[] as a, '{abc,other,this}'::varchar[] as b) row1  UNION
 SELECT *
    FROM   (select 2 as ID, '{rxyz,foo,bar}'::varchar[] as a, '{other,rabc,this}'::varchar[] as b) row1  UNION
 SELECT *
    FROM   (select 3 as ID, '{else,foo,bar}'::varchar[] as a, '{abc,other,this}'::varchar[] as b) row2

    ) mytable
WHERE   
NOT (
  (EXISTS (select * from unnest(a) as A, unnest(b) as B  WHERE  A||'-|-'||B LIKE '%xyz%-|-%abc%' OR B||'-|-'||A LIKE '%xyz%-|-%abc%' )))

returns only row3. In my use case I can guaranty that -|- is not part of either list making it possible to separate these.

Answer 1

You can unnest both into a single derived table:

select *
from mytable
where not exists (select *
                  from unnest(a,b) as x(a,b)
                  where x.a like '%xyz%'
                     or x.b like '%xyz%'
                     or x.a like '%abc%'
                     or x.b like '%abc%')

Answer 2

The updated task description is:

Eliminate rows where any element of text array a contains one of ('abc', 'xyz') and any element of text array b contains the other.

Your second query achieves that, but at terrible cost. unnest(a) as A, unnest(b) combines every element of array a with every element of array b - a Cartesian product, O(N²), makes performance deteriorate quickly with longer arrays.

Try one of these instead:

Test against concatenated strings with CROSS JOIN:

SELECT t.*
FROM   tbl t, array_to_string(a, '|') AS a_string, array_to_string(b, '|') AS b_string
WHERE (a_string LIKE '%abc%' AND b_string LIKE '%xyz%'
   OR  a_string LIKE '%xyz%' AND b_string LIKE '%abc%') IS NOT TRUE;

Make sure to choose a separator that cannot produce false matches. '|' in the example.

Test against concatenated strings with NOT EXISTS:

SELECT *
FROM   tbl t
WHERE  NOT EXISTS (
   SELECT FROM array_to_string(a, '|') a_string
             , array_to_string(b, '|') b_string
   WHERE  a_string LIKE '%abc%' AND b_string LIKE '%xyz%'
      OR  a_string LIKE '%xyz%' AND b_string LIKE '%abc%'
   );

Test unnested elements with LATERAL subquery;

SELECT t.*
FROM   tbl t
JOIN   LATERAL (
   SELECT bool_or(a_elem LIKE '%abc%') AND bool_or(b_elem LIKE '%xyz%') 
       OR bool_or(a_elem LIKE '%xyz%') AND bool_or(b_elem LIKE '%abc%') AS exclude
   FROM   unnest(t.a,t.b) e(a_elem, b_elem)
   ) x ON exclude IS NOT TRUE;

Test unnested elements with NOT EXISTS

SELECT *
FROM   tbl t
WHERE  NOT EXISTS (
   SELECT FROM unnest(t.a,t.b) AS e(a_elem, b_elem)
   HAVING bool_or(a_elem LIKE '%abc%') AND bool_or(b_elem LIKE '%xyz%') 
       OR bool_or(a_elem LIKE '%xyz%') AND bool_or(b_elem LIKE '%abc%')
   );

db<>fiddle here

The last one is my favorite. NNot sure which one performs best. All of them should perform substantially faster than what you have now.

About unnest(a,b):

• Unnest multiple arrays in parallel

Some comments on your points

In my case xyz and abc will not appear together as substrings in a single array entry (e.g. nothing like xyzblaabcexists).

That's a big assumption. There may be good reasons for it, but are you sure there are no corner cases, not now, not ever? Else your code will break silently later and it will be extremely hard to determine the cause.

Both arrays have exactly the same number of elements (they are actually derived from another query)

The most efficient query would then typically be to apply the filter at the source, in that other query - if at all possible. Might even use an appropriate index. Like, a trigram index:

• PostgreSQL LIKE query performance variations

no null elements (but even if that would still work as then I don't have a pair of abc and xyz)

Fair enough. But be wary of NULL values doing what you want if there are some.