To flatten an ARRAY in other dialects you could use UNNEST. In Snowflake, that functionality is called FLATTEN. To use it you can do:
SELECT VALUE FROM <TABLE>, LATERAL FLATTEN(INPUT=> <ARRAY_COLUMN>)
<TABLE>is your table w/ the array column
<ARRAY_COLUMN>is the name of the column with the arrays
Note: You can select more than VALUE; there are a lot of things returned. See the example below to see.
Here’s an array with JSON objects within. We can flatten these objects twice to get to the JSON values.