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?

CodePudding user response：

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: