Salesforce Docs Portal

unnest

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

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

Syntax

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

Arguments

Required

  • <array>: The array to expand.

Optional

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

Returns

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.

Examples

Unnest an Array

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

Results:

day
Mon
Tue
Wed
Thu
Fri

Unnest an Array while numbering the rows

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.

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

Results:

day ordinality
Mon 1
Tue 2
Wed 3
Thu 4
Fri 5

Unnest Multiple Arrays

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.

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

Results:

name task
Mon ramp up
Tue work
Wed work
Thu work
Fri wrap up
Sat NULL
Sun NULL

Related Documentation