There will be situations when we will have an array with strings - ['string1' , 'string2' , 'string3'] , and we will want to filter results that at least one of the strings in the array is a substring of another column.
For example -
Keywords_array | sites | Column 3 |
['mango' , 'banana' , 'melon'] | https://www.firebolt.io/banana | match |
['honey' , 'cinnamon'] | https://www.firebolt/not_substring | doesn't match |
In this case want to return only the first row because "banana" is a part of the site, and filter out the second row because "honey" and "cinnamon" is not a substring of the site.
We will use the functions "UNNEST" and "STRPOS" (To check if there is a match of the substring and the site) to achieve this results, like that -
with cte1 as (
select ['mango' , 'banana' , 'melon'] as Keywords_array , 'https://www.firebolt.io/banana' as "site"
union
select ['honey' , 'cinnamon'] as Keywords_array , 'https://www.firebolt.io/not_substring' as "site"
)
select
Keywords_array ,
"site"
from cte1
UNNEST(Keywords_array as unnested_keywords)
where strpos(site , unnested_keywords) > 0