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:
https://sql-99.readthedocs.io/en/latest/chapters/10.html
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.
SQL UNNEST
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:
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:
DECLARE array_ids INTARRAY;
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:
SELECT "sites" AS site,
[struct("linuxhint" AS site_name, ["Golang", "Rust", "SQL_server", "Redis"] AS site_topics)] AS sites
);
SELECT
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"]} |
+-------+---------------------------------------------------------+
Conclusion
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.
