Snowflake; convert strings to an array-CodePudding

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?

CodePudding user response：

Using PARSE_JSON:

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

DESC RESULT LAST_QUERY_ID();

Output:

CodePudding user response：

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

CodePudding user response：

SELECT PARSE_JSON(column_1) from fake_table;