Search code examples
sqlarraysamazon-athenaprestotrino

checking element present in an array using sql

I have a table with 3 columns: col1,col2,col3. Column3 is an array.

col3
[abc,def,xyz]
[abc,qwe,pou]
[]
[qwe,xyz]
[qwe,pou]

I have to write a sql query where i get col1, col2 and instead of col3, i have check whether it has value in('abc','xyz'). If it has these values then new_col = 1 else 0.

So the new_col looks like this:

new_col
1
1
0
1
0

i am looking for something like

select col1,col2, case when col3 in('xyz','abc')then 1 else 0 end as new_col from table

But this gives me an error saying "IN value and list items must have same type:array(varchar)"

Solution

  • This solution worked for me.

      select col1, col2,case when cardinality(filter(col3, x->x in( 'abc', 'xyz')))>0 then 1 else 0 end as new_col from <table>