Convert String to Array in Presto Athena

Question

I am new in SQL. I am trying to pass a list in WHERE IN statement. But the values are in a string similar to this:

'["id1", "id2", "id3", "id4", "id5", "id6"]'

The WHERE IN statement do not accept string but accept it:

WHERE IN (SELECT ids FROM unnested_id_related.id_related)

I can get this table using UNNEST statement

CROSS JOIN 
    unnest(ids) AS unnested_id_related (id_related)

But the value ids needs to be an ARRAY. Anyone knows how can I convert String to Array? Or a better way to pass this string in the WHERE IN statement?

Answer 1

Your data looks like json array, so you can parse it as one and cast to array of strings:

select cast(json_parse('["id1", "id2", "id3", "id4", "id5", "id6"]') as array(varchar))

Output:

_col0
[id1, id2, id3, id4, id5, id6]