Using FLATEN join in Snowflake

To Nha Notes | June 8, 2023, 7:49 p.m.

select * from employees;

Using LATERAL FLATTEN

SELECT emp.employee_ID, emp.last_name, index, value AS project_name
FROM employees AS emp, LATERAL FLATTEN(INPUT => emp.project_names) AS proj_names
ORDER BY employee_ID;

Using TABLE(FLATTEN(INPUT => <ARRAY_COLUMN>))

SELECT emp.employee_ID, emp.last_name, index, value AS project_name
FROM employees AS emp, TABLE(FLATTEN(INPUT => emp.project_names)) AS proj_names
ORDER BY employee_ID;

Using TABLE(FLATTEN(INPUT => <ARRAY_COLUMN>, OUTER => TRUE))

SELECT emp.employee_ID, emp.last_name, index, value AS project_name
FROM employees AS emp, TABLE(FLATTEN(INPUT => emp.project_names, OUTER => TRUE)) AS proj_names
ORDER BY employee_ID;

Reference

https://docs.snowflake.com/en/sql-reference/constructs/join-lateral