Postgresql select where array overlaps using like

Question

Is it possible to determine if an ARRAY column contains overlapping values from another array with the LIKE clause?

The && operator works but the strings have to be exact matches

 q = """select * from articles where keywords && '{"mortgage brokers"}';""" // Exact match

Is it possible to filter the keywords where rows contain values with substring, not the full string? Something like:

 q = """select * from articles where keywords && LIKE '{"mortgage"}';""" // HOW TO FILTER keywords containing value with substring

GMB · Accepted Answer · 2019-09-15 19:31:52Z

2

LIKE operates on strings. To check whether two arrays overlap, you can use &&.

From the Array Functions and Operators documentation:

&& : overlap (have elements in common)

SELECT ARRAY[1,4,3] && ARRAY[2,1] arrays_overlap;

| arrays_overlap |
| -------------- |
| true           |

To see if there are values in an array that are LIKE those from another array, one solution would be to unnest both arrays and compare the results with LIKE:

SELECT EXISTS (
  SELECT 1
    FROM 
    unnest(ARRAY['abc', 'def' ]) my_array(x)
    INNER JOIN unnest (ARRAY['a', 'z' ]) my_keywords(x)  
        ON my_array.x LIKE '%' || my_keywords.x || '%'
 ) arrays_have_similar_elements;

| arrays_have_similar_elements |
| ---------------------------- |
| true                         |

SELECT EXISTS (
  SELECT 1
    FROM 
    unnest(ARRAY['abc', 'def' ]) my_array(x)
    INNER JOIN unnest (ARRAY['y', 'z' ]) my_keywords(x)  
        ON my_array.x LIKE '%' || my_keywords.x || '%'
 ) arrays_have_similar_elements;

| arrays_have_similar_elements |
| ---------------------------- |
| false                        |

Demo on DB Fiddle

edited Sep 15, 2019 at 19:31

answered Sep 15, 2019 at 19:17

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

kye Over a year ago

is it possible to combine && and like? && only returns results on exact matches

GMB Over a year ago

@kye: ah I see what you mean now. I updated my answer.

kye · Accepted Answer · 2019-09-15 20:01:07Z

1

Thanks to @GMB for the guidance. I was able to solve the problem using the query below.

      SELECT * from articles, unnest(keywords) my_array(x)
            INNER JOIN unnest(ARRAY['broker']) my_keywords(x)
            ON my_array.x LIKE '%' || my_keywords.x || '%';

answered Sep 15, 2019 at 20:01

kye

2,2463 gold badges30 silver badges43 bronze badges

Collectives™ on Stack Overflow

Postgresql select where array overlaps using like

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related