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.
Snowflake Convert Array to Rows
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.
Related Article,
The flatten function explodes or expands the compound values into rows.
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 |
+-------+----------+------------+
Related Articles,
- How to combine two arrays in Snowflake?
- Extract Numbers using Regular Expression Functions
- Snowflake Extract Date using Regular Expression Functions
- Snowflake Split String on Delimiter-Functions and Examples
- How to Get Most Queried Table in Snowflake?
Hope this helps 🙂