Snowflake; convert strings to an array

rumble_shark 0
Viewed 56

Using snowflake, I have a column named 'column_1'. The datatype is TEXT.
An example of a value in this column is here:

["Apple", "Pear","Chicken"]

I say: select to_array(column_1) from fake_table; and I get:

[ "[\"Apple\",\"Pear\",\"Chicken\"]" ]

So it put my text into it. But I want to convert the datatype. Seems like it should be simple.

I try strtok_to_array(column_1, ',') and get the same situation.

How can snowflake convert strings to an array?

snowflake-cloud-data-platform sql
3 Answers

Using PARSE_JSON:

SELECT PARSE_JSON('["Apple", "Pear","Chicken"]')::ARRAY;

DESC RESULT LAST_QUERY_ID();

Output:

enter image description here

enter image description here

Lukasz Szozda0

Since that's valid JSON, you can use the PARSE_JSON function:

select parse_json('["Apple", "Pear","Chicken"]');

select parse_json('["Apple", "Pear","Chicken"]')[0]; -- Get first one

select parse_json('["Apple", "Pear","Chicken"]')[0]::string; -- Cast to string
Greg Pavlik0

I'd say parse_json is the way to go, but if you're concerned some values might not be a valid json, you could get rid of the double quotes and square brackets and split the resulting comma separated string to array

select split(translate(col,$$"[]$$,''),',')

Note : Encapsulating in $$ makes escaping quotes and any other special character easier

Phil Coulson0
Related