I am looking for a way to query a collect_set/list column with multiple values using like operator.
In the below example I am looking to get the rows that have one of the values like '121%'
id values
1 ["8001","12100"]
2 ["12134","9999","2222"]
3 NULL
4 ["5671","9765]
Result:
id values
1 ["8001","12100"] -- because of 121 in 2nd value of the collect_set
2 ["12134","9999","2222"] -- because of 121 in first value of the collect_set
Any help will be appreciated. Thanks.
Concatenate array using some delimiter, for example | and use concatenated string in RLIKE operator.
Demo:
with mytable as (
select 1 id, array('8001','12100') as `values`
union all
select 2, array('12134','9999','2222')
union all
select 3, array()
union all
select 4, array('5671','9765')
)
select * from mytable
where concat('|',concat_ws('|',`values`),'|') rlike '\\|121'
Result:
id values
1 ["8001","12100"]
2 ["12134","9999","2222"]
Note: Pipe | in regex needs to be escaped with double backslash.