CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformflatten
John Thomas
John Thomas

Reputation: 1105

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.

Upvotes: 0

Views: 1212

Answers (2)

Eric Lin
Eric Lin

Reputation: 1520

I use the FLATTEN version:

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

Upvotes: 1

Greg Pavlik
Greg Pavlik

Reputation: 11056

The nested replace functions simply remove the square brackets so the split_to_table table function can do its thing.

select T1.ID, V1."VALUE", T1.KEY, T1."DATE", T1."USER"
from TABLE1 T1, table(split_to_table(replace(replace(VALUE, '[', ''), ']', ''), ',')) V1
where V1."VALUE" <> ''
;

Upvotes: 1

Related Questions