SQL Standard

Unnest SQL

An array is a part of the collection type in SQL, as introduced in SQL 99. An array is a collection of elements of pre-defined length and data type. An array can be of any user-defined type or due to an SQL operation that returns an array result set.

You can also have an array of varying lengths using a MULTISET as introduced in SQL 2003.

You can learn more in the resource provided below:


Note: Although ANSI SQL Standard supports the array type, some SQL engines do not yet support it.

SQL Create Array

We can create an array by declaring an array type, and then setting an array variable of set type.

An example syntax is shown below:


The previous syntax creates an array type INTARRAY.


To select data from an array, we use the unnest function. This function takes in an array and returns a table with a row for each element in the specified collection.

The function syntax is as shown:


Take the following example:

SELECT * FROM unnest([1,2,3,4,5]) AS id WITH offset AS offset ORDER BY offset;

In the previous example query, we use the unnest function to unpack or “flatten” the array into a row set.

NOTE: We use the WITH OFFSET statement to implement the order of the values of the row, because the unnest function will destroy the order of the elements in the specified array. Therefore, you can omit the ORDER BY clause if you do not care about the order of the elements.

Query Nested Arrays

In some cases, you may encounter an array of structs. To access and query the struct fields, you must unpack them using the JOIN UNNEST clause.

A simplistic example is shown below:

SET array_ids = array[1,2,3,4,5];
SELECT * FROM JOIN unnest(array_ids) AS ID;

In the previous example, we start by declaring an array type. Next, we use an array constructor to initialize the array with new values.

Finally, we use the unnest function to select the items in the array into a row table set.

A more practical example is shown below:

WITH sites AS(
    SELECT "sites" AS site,
        [struct("linuxhint" AS site_name, ["Golang", "Rust", "SQL_server", "Redis"] AS site_topics)] AS sites
    site, site_topics
    FROM sites s
    CROSS JOIN unnest(s.sites) AS site;

The previous query should return the output as shown below:

| site  | site_topic                                              |
| sites + {linuxhint, ["Golang", "Rust", "SQL_server", "Redis"]}  |


This article covers the fundamentals of working with SQL collection types and the unnest function definition. Keep in mind that this may vary depending on the database engine and whether or not array type is implemented. We hope you found this article helpful. Check the other Linux Hint articles for more tips and tutorials.

For concrete documentation on SQL Standard arrays, check the resource below.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list