How to flatten Array string column to row in snowflake?

I’ve tried using snowflake’s flatten, but it doesn’t work, so I’m asking.

Example data

  • array string type -> varchar
id array string
1 [10001,100000][10003,100000000][10004,10000][10006,100]

I want

id array string
1 [10001,100000]
1 [10003,100000000]
1 …..

>Solution :

SELECT 
    id, 
    '[' || c.value::string AS "array string"
FROM data d,
     LATERAL FLATTEN(input=>split(d."array string", '[')) c;

should work, as you are splitting on the open array token, and thus need to put it back in the output.

Leave a Reply