How to parse a list within a table in Snowflake using SQL

So I have a table as such:

ID   VALUE               KEY    DATE        USER
001  ["a"]               group1 2021-01-01  212
002  []                  group2 2021-01-01  212
003  ["a","c"]           group1 2021-01-02  212
004  ["apple", "pear"]   group3 2021-01-02  211

I would like to restructure this data so that each list element becomes its own row. If VALUE is an empty list, the expectation is it does not appear in the final dataset.

Final result should be:

ID   VALUE   KEY    DATE        USER
001  "a"     group1 2021-01-01  212
003  "a"     group1 2021-01-02  212
003  "c"     group1 2021-01-02  212
004  "apple" group3 2021-01-02  211
004  "pear"  group3 2021-01-02  211

I think using FLATTEN() in Snowflake should work here but I cant seem to get it.

Answer

I use the FLATTEN version:

SELECT 
  ID, f.VALUE, t.KEY, DATE, USER
FROM
test t, table(
  FLATTEN(
    INPUT => STRTOK_TO_ARRAY(value, '[], "')
  )
) f;

Advertisement

Answer