Desired output is somewhat reversed version of this solution.
Let's say we have table 'letterstable' with letter column.
**letter**
a
b
c
ddgas
awdawaa
bdb
acchds
I have a query that matches rows from letter column with array elements but what I want to do is to match if any element of arrays 1 or 2 likes letter. Desired output from query below in this case would be:
+---------+----------+
| letter | category |
+---------+----------+
| ddgas | 1 |
| awdawaa | 1 |
| acchds | 1 |
+---------+----------+
Query:
with categories as(
select category, names from(
values
('1', ARRAY['aa', 'bb', 'cc']),
('2', ARRAY['dd', 'ee'])
) as t (category, names)
)
select
letter, category
from letterstable
join categories on cardinality(filter(names, x -> x like letter)) > 0
