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

Example data

• array string type -> varchar

I want

CodePudding user response：

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.