SQL UNNEST Function

An array refers to an ordered list of elements of the same data type. Arrays can range from simple values to a nested set of arrays.

Arrays are a significant building block in programming, and Standard SQL allows you to work with them.

In this article, we will discuss how you can use one of the most beneficial array functions. The UNNEST function allows you to take a given array and return it as a table for each element in the array.

Function Syntax

The function syntax is as follows:

UNNEST(ARRAY) [WITH OFFSET]

The function accepts the array as an argument and returns the array as a table. You can also specify the WITH OFFSET clause to provide an additional column for the position of each element in the array.

Example 1: Basic Function Usage

The following example shows how to use the unnest function to deconstruct the array into a table:

SELECT
*
FROM
UNNEST([1,2,3,4,5]) AS tble;

Resulting table:

Example 2: Using the Unnest Function with Offset

The following example illustrates how to use the unnest function with the offset parameter:

SELECT
*
FROM
UNNEST([1,2,3,4,5,6]) AS tbl
WITH
OFFSET
AS `offset_value`;

The resulting table is as follows:

Example 3: Quickly Generate a Table Using the Unnest Function

We can combine the power of arrays and unnest function to quickly generate a table with random data.

An example is shown in the following:

SELECT
*
FROM
UNNEST(
ARRAY<STRUCT>[
('string1', 4), ('string2', 6), ('string3', 2)
]
)
AS simple_table;

Output table:

Conclusion

In this post, we discussed how to use the unnest function in the Standard SQL to convert the values of an array to table rows.

Thanks for reading!