Snowflake Convert Array to Rows

Snowflake supports the array functions. You can insert an array of values such as integer, characters, etc to the Snowflake table. In this article, we will check how to convert Snowflake array type into rows using table functions.

When working with arrays in Snowflake, you often need to expand array elements into multiple rows.

The recommended method to convert an array of integer or characters to rows is to use the table function. We will use the FLATTEN function for the demonstration.

Snowflake FLATTEN Function

FLATTEN is a table function that takes an ARRAY column and produces a lateral view. The lateral view is an inline view that contains correlation referring to other tables that precede it in the FROM clause.

Snowflake Convert Array to Rows Example

Following data will be used to convert array to rows using FLATTEN function.

+-------+----------+-----+-----------------+
| EMPID | LASTNAME | DID | PROJECTNAMES    |
|-------+----------+-----+-----------------|
|   101 | ABC      |   1 | [               |
|       |          |     |   "IT",         |
|       |          |     |   "PROD"        |
|       |          |     | ]               |
|   102 | BCD      |   1 | [               |
|       |          |     |   "PS",         |
|       |          |     |   "PRODSupport" |
|       |          |     | ]               |
|   103 | CDE      |   2 | NULL            |
+-------+----------+-----+-----------------+

Note that, the PROJECTNAMES is an array of project names. We will convert PROJECTNAMES array values to rows.

For example, consider below SQL statement that uses a flatten function to convert array of characters to rows.

SELECT empid, 
       lastname, 
       F.value AS PROJECT_NAMES 
FROM   e_emp, 
       Table(Flatten(e_emp.projectnames)) F; 

+-------+----------+---------------+
| EMPID | LASTNAME | PROJECT_NAMES |
|-------+----------+---------------|
|   101 | ABC      | "IT"          |
|   101 | ABC      | "PROD"        |
|   102 | BCD      | "PS"          |
|   102 | BCD      | "PRODSupport" |
+-------+----------+---------------+

Note that, FLATTEN function ignores values such as NULL values if any in the table. You should use OUTER joins to display all rows from the source table.

Handle NULL values in Snowflake FLATTEN Function

Following statement uses outer join to display all records from the source table.

SELECT E.empid, 
       E.lastname, 
       V.project_names 
FROM   e_emp AS E 
       LEFT OUTER JOIN (SELECT empid, 
                               lastname, 
                               F.value AS PROJECT_NAMES 
                        FROM   e_emp, 
                               Table(Flatten(e_emp.projectnames)) F) AS V 
                    ON E.empid = V.empid; 

+-------+----------+---------------+
| EMPID | LASTNAME | PROJECT_NAMES |
|-------+----------+---------------|
|   101 | ABC      | "IT"          |
|   101 | ABC      | "PROD"        |
|   102 | BCD      | "PS"          |
|   102 | BCD      | "PRODSupport" |
|   103 | CDE      | NULL          |
+-------+----------+---------------+

Snowflake Convert Array of integer to Rows Example

Convert array of integer values to rows is a common requirement when you work with semi-structures data such as arrays or json input formats.

Following data will be used in the SQL query.

+-------+----------+-----+-----------+
| EMPID | LASTNAME | DID | PROJECTID |
|-------+----------+-----+-----------|
|   101 | ABC      |   1 | [         |
|       |          |     |   1,      |
|       |          |     |   2       |
|       |          |     | ]         |
|   102 | BCD      |   1 | [         |
|       |          |     |   2,      |
|       |          |     |   3       |
|       |          |     | ]         |
|   103 | CDE      |   2 | NULL      |
+-------+----------+-----+-----------+

For example, considers below example that converts an array of integer to rows using table function.

SELECT E.empid, 
       E.lastname, 
       V.project_id 
FROM   e_emp_new AS E 
       LEFT OUTER JOIN (SELECT empid, 
                               lastname, 
                               F.value AS PROJECT_ID 
                        FROM   e_emp_new, 
                               Table(Flatten(e_emp_new.projectid)) F) AS V 
                    ON E.empid = V.empid; 

+-------+----------+------------+
| EMPID | LASTNAME | PROJECT_ID |
|-------+----------+------------|
|   101 | ABC      | 1          |
|   101 | ABC      | 2          |
|   102 | BCD      | 2          |
|   102 | BCD      | 3          |
|   103 | CDE      | NULL       |
+-------+----------+------------+