How to read and parse an array of tuples into a snowfklake table?

Question

I am working with a source field which is in the form of an array of tuples

[(a,145), (b,12), (c,63), (d,1), (e,54), (f,99), ...]

I am unable to load this field into a variant type into snowflake. When I try to load this field, I get the following error - Exception: net.snowflake.client.jdbc.SnowflakeSQLException: Error parsing JSON.

As a work around, I loaded this field as a VARCHAR into Snowflake but I am now having trouble trying to parse it as an array and flatten it using lateral flatten function.

My goal is to flatten this array and break out each tuples into its own row. Then I would like to split the tuple into separate columns. Does anyone have suggestions how to get this to work in snowflake ?

Stuart Ozer Stuart Ozer · Accepted Answer · 2020-03-31T17:18:33

select 
    split_part(regexp_replace(value, '[\\(\\)]'), ',', 1) as left, 
    split_part(regexp_replace(value, '[\\(\\)]'), ',', 2) as right
from table(split_to_table(
    regexp_replace('[(a,145), (b,12), (c,63), (d,1), (e,54), (f,99)]', '[\\[\\]]',''),
    ' ') 
   );

How to read and parse an array of tuples into a snowfklake table?

1 Answers