Skip to main content

FLATTEN

Categories: Semi-Structured Data

FLATTEN

Explodes compound values into multiple rows. The FLATTEN function takes a LIST column and produces a lateral view (that is, an inline view that contains correlation referring to other tables that precede it in the FROM clause).

Syntax​

FLATTEN(expression list) → list​

  • expression: The expression that will be unpacked into rows. The expression must be of data type LIST.

Examples

FLATTEN example
SELECT FLATTEN(CONVERT_FROM ('["Ford", "BMW", "Fiat"]', 'json'))
-- Ford
-- BMW
-- Fiat

Usage Notes​

The FLATTEN function eliminates rows from the result set if the target column contains an empty array or is null.

If the target column is an array of standard datatypes such as INT or VARCHAR, you can use the UNION operator to cast NULL as that datatype and include all rows in the result set. For example:

   SELECT col1, FLATTEN(col2) FROM myTable
   UNION
   SELECT col1, CAST(NULL as INT) FROM myTable where col2[0] is NULL or col2 is NULL

The UNION operator does not work if the target column is an array of structs or other complex types because UNION cannot cast NULL as a struct.