Flatten an Array

Flatten an Array

Description

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

where:

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

Example:

Here’s an array with JSON objects within. We can flatten these objects twice to get to the JSON values.