How to Unnest in PostgreSQL

1. PostgreSQL unnest () Functionality
2. Use of PostgreSQL unnest()

In this article, we will describe the unnest() keyword and its uses in PostgreSQL with multiple examples.

PostgreSQL unnest () Functionality

In PostgreSQL, you can use an array as a data type. There are also multiple functions related to improving the usability of arrays in the language.

The unnest() function is one of those many array functionalities we will explain today. However, the basic functionality is quite clear from the word ‘unnest’ itself: it expands arrays into rows.

It is essential when trying to display the arrays in a tabular form. PostgreSQL allows this functionality to expand arrays into a set of singular values we can represent in a tabular form as rows.

In older versions of PostgreSQL, there were slower and more complicated methods of achieving an expansion of arrays that were replaced by this unnest() functionality which we can also use with other PostgreSQL functionalities.

Using it with other PostgreSQL functionalities enables vast possibilities for easier and faster operations.

Use of PostgreSQL unnest()

We will start with the basic functionality and then slowly move towards more complicated use cases. The syntax is unnest(array).

It will simply take all the individual values of the array and put them in a set displayed in the form of rows in the table.

Note: Must provide the unnest() keyword with an array. For example: unnest (array_to_expand).

Now, let’s move on to the examples of each essential function.

Using the PostgreSQL unnest () Function on a Simple One-Dimensional Array

Let there be an array of natural numbers till 5. The simple way to expand them and display them as rows in a table is to unnest them.

SELECT unnest (ARRAY[1,2,3,4,5]);

It will provide an output with all 5 digits in a tabular form (5 rows):

Now, let’s try the exact thing with words instead of numbers to see if there is any change.

SELECT unnest (ARRAY['apple', 'oranges', 'mango']);

It will provide an output with all 3 words in a tabular form (3 rows):

As we can see, it works perfectly for both integers and text.

Use the PostgreSQL unnest () Function on a Simple Multi-Dimensional Array

We have explained how to apply the unnest () function on a 1D array above. The example below illustrates how to use it on 2D arrays.

SELECT unnest (array[array[1, 2], array[2, 3], array[4,5]]);

It will display in total 6 rows with all the 6 digits in a tabular form.

Use the PostgreSQL unnest () Function on Multiple Arrays at the Same Time

This section will demonstrate the use of the unnest () function on multiple arrays simultaneously. The arrays can also be of different data types.

After using the unnest () function, each array will appear as a column in the table with the values of arrays as rows.

We can write it as unnest (array1, array2, array3…). The example below will help clarify the concept.

SELECT * FROM
unnest
(
    array [1, 2, 3],
    array ['HP', 'AMD', 'APPLE']
)
AS data(ID, Company);

It will display a table with each array as a column and values inside as rows of those columns.

Let’s take another example where the two arrays have an unequal number of elements.

SELECT * FROM
unnest
(
    array [1, 2, 3],
    array ['HP', 'AMD', 'APPLE', 'DELL']
)
AS data(ID, Company);

In this example, there is one more company than the IDs. In this case, the shorter one will be padded with NULLS to compensate, and a table will be displayed without errors.

Use the PostgreSQL unnest () Function With ORDER BY Clause

The PostgreSQL unnest () function can also be used alongside the ORDER BY clause. We will be illustrating it with examples.

One of the ways of using the ORDER BY clause is by utilizing the ordering of arrays. The result will be sorted in this case according to the second column (Company).

SELECT * FROM
unnest
(
    array [1, 2, 3],
    array ['HP', 'AMD', 'APPLE']
)
AS data(ID, Company) ORDER BY 2;

Another way of using the ORDER BY clause is by assigning the array a name using AS. The result will be sorted in this case according to the second column (Ranking).

SELECT * FROM
unnest
(
    array['HP', 'AMD', 'APPLE'],
    array[23,14,1]
)
AS data(Company, Ranking) ORDER BY Ranking;

Use the PostgreSQL unnest () Function With LIMIT Clause

PostgreSQL allows you to use the unnest () function with the LIMIT clause.

In this case, the resultant table (after expansion of arrays) will be limited to the condition described in the LIMIT clause. The examples below will clarify the idea.

SELECT unnest (array[1,2,3,4,5,6])
LIMIT 3;

It will display only the array’s first 3 values as rows.

You can also use the OFFSET command with LIMIT.

SELECT unnest (array[1,2,3,4,5,6])
LIMIT 3 OFFSET 3;

It will skip the first 3 values and display only the last 3 values (4,5,6) as rows in the resultant table.

Use the PostgreSQL unnest () Function With DISTINCT Clause

The DISTINCT clause is used in multiple database languages to remove duplicates.

In PostgreSQL, it can remove all the duplicates of any kind of data, whether an integer, string, etc., but it has a limitation in that it can only remove duplicates in the data in tabular form.

This limitation makes it difficult to remove duplicates from arrays. However, the unnest () functionality solves this problem quickly.

By using the unnest () command, we can first convert the values of an array into tabular form. Then, the DISTINCT clause can be easily applied to remove duplicates.

This idea is illustrated in the below example for your understanding.

SELECT DISTINCT unnest (array['HP', 'DELL', 'APPLE','AMD', 'HP']);

In this example, the array is first converted to tabular form using unnest (). Then, the DISTINCT clause is applied to remove duplicates. As a result, the resultant table will have HP only once.