How to flatten Array string column to row in snowflake?

Question

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	.....

Answer 1

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.