Is there a way to use like operator with an array in HiveQL?

Question

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.

Answer 1

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.