What is Unnest function in PostgreSQL with example?

In my previous article I have given multiple functions and its examples. In this article I would like to focus on one of the most used function in PostgreSQL which is Unnest function with real examples. The article will cover the usages of Unnest function in first part followed by Where to use the function and its example. It will give you the clear idea about the Unnest function .

What is Unnest Function in PostgreSQL ?

The unnest function in PostgreSQL is essentially used to expand the array into rows; it belongs to the category of array functions. With PostgreSQL’s unnest function, we can also create a table structure out of an array by transforming it into a table-like structure. On an earlier version of PostgreSQL, we had to use an array with a cross join in order to convert an array into a table structure. After merging it, we used a cross join to create an array series. The array’s items will be arranged into a table using the cross join and produce series functions after creating a series. In earlier versions of PostgreSQL, covering arrays to rows was a laborious process.

When to use unnest in PostgresQL?

The question is in everyone’s mind that what will be multiple scenarios to use unnest. We can avoid using a cross join or writing series functions to convert an array into a structure resembling a table by using the unnest function. We just used an array and the unnest technique. In PostgreSQL, we used an array of numbers or text to create a structure akin to a table.

Syntax and Examples of Unnest :

The key use of an UNNEST function is to takes an ARRAY and returns a table with a row for each element in the ARRAY.

Syntax :

unnest(array)
SELECT unnest(ARRAY[any array number]) ; – One Dimension Array
SELECT unnest(ARRAY[any array text]) ; – One Dimension Array
SELECT unnest(ARRAY[any array text])limit number ;

Syntax Explanation :

Unnest:

We created this method in PostgreSQL to set an element in a structure resembling a table. Both a text and a number array have been utilized with an unnest element. In PostgreSQL, we must define the number or text element with the unnest array function.

Any array text :

This is what is meant when PostgreSQL uses the unnest array function to transform an array of text values into a structure that resembles a table.
Any array number:

This is what is meant by a number. The unnest array function in PostgreSQL has been used to transform an array into a structure that resembles a table.
Select:

Using PostgreSQL’s unnest array function, this operation selects the array’s value. When utilizing PostgreSQL’s unnest array function, select operations are crucial and very handy.
Limit:

PostgreSQL also supports the use of the limit clause with the unnest function. When using the limit clause, the number will be displayed according to the limit that was set with the unnest function.

Few Examples of Unnest Function :

Example 1 :

SELECT
  *
FROM
  UNNEST([1, 3, 3, 5, NULL]) AS unnest_column_table

Output :
unnest_column
1
3
3
5
NULL

Example 2 : Create table of Electronic goods used in company
SELECT
  *
FROM
  UNNEST(
       ARRAY<STRUCT<Electroic_goods STRING , number INT64>>[
         ('Computers', 4), ('Laptop', 6), ('Mobile', 2)
       ]
  )
AS Goods_table;

Output :
Electronic_goods    number
Computers              4         
Laptop                 6   
Mobile                 2