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 -
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