Search code examples
snowflake-cloud-data-platformflatten

Extracting elements from a varchar column in snowflake

I have a sample data, as shown below;

NAME Values Typeof(Values)
BNL [1,2] VARCHAR

As seen the data type is VARCHAR for the second column.

Expected Output

Name Values
BNL 1
BNL 2

In a way, we want to split the row into two rows depending on how many elements we have in the Values column. I was looking into FLATTEN as an option;

select temp.*,
         t.value
     from temp,
     table(flatten(temp.values)) as t;

But since it is a VARCHAR, we get following error message;

invalid type [VARCHAR(16777216)] for parameter '1'

Can I please get some help here?thanx

Solution

  • One way is to use TRY_PARSE_JSON:

    select temp.*,
     t.value
 from temp,
 table(flatten(TRY_PARSE_JSON(temp.values))) as t;