unnest
Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API
Expands the elements of one or more arrays into a set of rows.
<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.
Results:
| day |
|---|
| Mon |
| Tue |
| Wed |
| Thu |
| Fri |
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.
Results:
| day | ordinality |
|---|---|
| Mon | 1 |
| Tue | 2 |
| Wed | 3 |
| Thu | 4 |
| Fri | 5 |
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.
Results:
| name | task |
|---|---|
| Mon | ramp up |
| Tue | work |
| Wed | work |
| Thu | work |
| Fri | wrap up |
| Sat | NULL |
| Sun | NULL |