unnest

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Expands the elements of one or more arrays into a set of rows.

1unnest(<array> [, ...])

• <array>: The array to expand.

• ...: Additional arrays to expand in lock-step.

Returns a set of rows, each containing one element from the array(s). If multiple arrays are provided, they are expanded in lock-step. If the arrays aren’t all the same length, the shorter ones are padded with NULL values.

1SELECT * FROM unnest(ARRAY['Mon','Tue','Wed','Thu','Fri']) AS days(day)

Results:

The WITH ORDINALITY clause instructs unnest to also number the generated rows. Thereby, we know which element occured at which position in the original array.

1SELECT * FROM unnest(ARRAY['Mon','Tue','Wed','Thu','Fri']) WITH ORDINALITY AS days(day, ordinality)

Results:

You can also call unnest with multiple array parameters. In this case, the arrays are expanded in lock-step. If the arrays aren’t all the same length, the shorter ones are padded with NULL values.

1SELECT * FROM unnest(
2  ARRAY['Mon','Tue','Wed','Thu','Fri', 'Sat', 'Sun'],
3  ARRAY['ramp up','work','work','work','wrap up'],
4) AS days(name, task);

Results:

• Array Data Type
• Array Functions
• Set Returning Functions