PostgreSQL UNNEST() function: Syntax & 6 Essential Queries

PostgreSQL offers a variety of data types for columns. PostgreSQL users have many options, from in-built data types to user-defined ones. If you have acquainted with PostgreSQL in your application development, then you must be familiar with the Array data type for columns in PostgreSQL. 

Creating, querying, and performing operations on array columns are some of the primary functions in PostgreSQL. But there would be some scenarios where you need to convert arrays back to rows in PostgreSQL.

For example, let’s say you want to aggregate two array columns into one set. But, PostgreSQL only offers the UNION method for rows, not for array columns. Enter, PostgreSQL unnest() Function — to the rescue!

With PostgreSQL unnest() function, you can convert arrays to rows. 

You crashed on the correct link if you are searching for a step-by-step tutorial on unnest() Function in PostgreSQL. In this article, you will gain information about PostgreSQL unnest() function. You will also gain a holistic understanding of different operations associated with the PostgreSQL unnest() function, its syntax, and example use cases.

Prerequisites

• Basic knowledge of writing PostgreSQL Queries
• pgAdmin dashboard

What is unnest() Function in PostgreSQL?

PostgreSQL unnest comes under the umbrella of array functions. The purpose of unnest function in PostgreSQL is to expand the array into rows. Unnest function generates a table structure of an array in PostgreSQL. 

Unnest array function is beneficial in PostgreSQL for expanding the array into the set of values or converting the array into the structure of the rows. PostgreSQL offers unnest() function. It is a system function that allows you to extend an array to a specified number of rows.

When converting an array into a table structure on an older version of PostgreSQL, we had to utilize an array with a cross join. After merging it, we produced a series of the array using a cross join. After creating a series, the array’s items will be arranged into a table using the cross join and produce series functions. It was an overwhelming process of covering arrays to rows in older versions of PostgreSQL.

Why Use unnest() Function in PostgreSQL?

In the above section, we have seen how long it takes to convert the array into row structures in the older version of PostgreSQL. Therefore, the PostgreSQL unnest function comes with the following advantages:

• By utilizing the unnest function, we can avoid the cross join or create series functions to turn an array into a table-like structure. We’ve just used the unnest method with an array.
• In PostgreSQL, we utilized an array for numbers or text to turn the array into a table-like structure.
• We can also use the unnest function with an order by clause. We’re utilizing an order by clause with 1 and an unnest function.
• We can also utilize the limit clause with the unnest function. When we use a limit clause, it returns the result based on the number we specified with the limit.

PostgreSQL unnest() Function Overview

PostgreSQL unnest() function is essential in data manipulation. In this section, we will go through some fundamental unnest operations, their functionality, and examples. These operations will aid us in dealing with PostgreSQL Unnest functions efficiently. Let’s address them one by one. But, before we get to the functions, let’s look at PostgreSQL’s unnest basic function syntax.

Syntax of unnest() Function

The syntax of the PostgreSQL Unnest function is as follows:

unnest(array)

• unnest() is PostgreSQL function to to set the elements in table-like structure.
• Unnsest function must be provided with an array as an argument.

The unnest() method in PostgreSQL returns a set, with each member in the array becoming a row in the set. 

Unnest One-Dimension Array in PostgreSQL

The below example demonstrates how to use PostgreSQL’s unnest() function to convert a one-dimensional array into a set.

Example 1: Unnest method using array as a number. The below code snippet will expand the number array of size 7 into 7 rows. 

SELECT unnest(ARRAY[10, 11, 12, 13, 14, 15, 16]);

Output:

Example 2: Unnest method using array as text. The below code snippet will expand the text array of size 6 into 6 rows. 

SELECT unnest(ARRAY['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday']);

Output:

Unnest Multi-Dimension Array in PostgreSQL

This section illustrates how to use PostgreSQL’s unnest() function to convert a two-dimensional array into a set.

Example: Unnest method using 2-D array as an integer. The below code snippet will expand the integer array into 6 rows.

SELECT unnest('[2:4][2:3]={{21,22},{23,24},{25,26}}'::integer[]);

Output: In the result set, each array element becomes a row.

Expand Multiple Array with Unnest Function

The PostgreSQL unnest() function allows you to expand one array at a time and also expand many arrays (of potentially different data types) into a set of rows.

Syntax:

unnest ( anyarray, anyarray [, ... ] ) 

Note: If the arrays are not of the same length, NULLs are used to pad the shorter ones. This form is only permitted in the FROM clause of a query.

Example 1: 

SELECT * FROM
  unnest(
    ARRAY[1, 2, 3, 4],
    ARRAY['Shawn', 'Bible', 'Build', 'Jeff'],
    ARRAY[23, 24, 28, 27]
  ) AS data(id,name,age);

Output:

In this case, the unnest() method extends several arrays, similar to extending each array individually into a set, and then connects multiple sets by row.

Unnest Function with Order By Clause

The unnest function can also be used with an order by clause. We are using an unnest function with an order by clause.

Example: The example below illustrates an unnest function with a limit clause. In the following example, we utilize a number array.

Note:  We have used the ordinal value of the column with the Order By clause. In the example below, ‘2’ is the ordinal value of the second column hence the results will be ordered according to the 2nd column.

SELECT * from unnest(
ARRAY[1, 2, 3, 4, 5],
ARRAY['Arya Stark','Snow Stark','Sensa Stark']) 
As data(id, name) order by 2;

Output:

unnest() Function With Limit Clause

In PostgreSQL, we have also used the limit clause with the unnest function. When utilizing the limit clause, the resultant row set will be displayed based on the limit we set using the unnest function.

Example: The example below illustrates an unnest function with a limit clause. In the following example, we utilize a number array.

SELECT unnest(ARRAY[1.43, 12.4, 43.4, 1.43, 5.45, 6.66, 6, 7, 8, 9, 10]) limit 5;

Output:

unnest() Function With Distinct Clause

DISTINCT can aid you in removing duplicates from any type of data. It does, however, need the usage of rows as data. This signifies that this function supports integers, text, floats, and other data kinds, but not arrays. You must first use the UNNEST function to eliminate duplicates to transform your array-type data into rows. The transformed data rows will then be given to the DISTINCT clause. The output shows that the array was transformed into rows, and then only the distinct values from these rows were obtained using the DISTINCT clause.

Example: First, the text array has been converted into rows using the UNNEST function. These rows will be sorted into ascending order using the ORDER BY clause, as shown below code snippet.

SELECT DISTINCT UNNEST( '{Pol, Arm, Kinn, Pol, Porchay}'::text[] )
As result Order BY 1;

Output:

Conclusion

The blog offered an overview of the PostgreSQL unnest(), a definition of the Unnest’s function, and examples. The lesson then demonstrated how to use unnest() function with Distinct, Limit, and Order by clause in PostgreSQL.

Hopefully, you will try leveraging the PostgreSQL unnest() function for your future projects. Feel free to leave a comment below expressing your thoughts or recommendations.

If you are a PostgreSQL user, replicating data into a warehouse using ETL for Data Analysis can get demanding. This problem is exaggerated because they need extensive money and resources to hire data engineers and analysts to make sense of this data. 

Luckily, you can set up and start Data Replication from PostgreSQL to your favorite warehouse in a matter of minutes using Hevo. 

Hevo Data, with its strong integration with 100+ Data Sources such as PostgreSQL, MySQL, and MS SQL Server, allows you to not only export data from sources & load data to the destinations but also transform & enrich your data & make it analysis-ready so that you can focus only on your critical business needs and perform insightful analysis using BI tools. 

Visit our Website to Explore Hevo

Hevo lets you replicate your data from your PostgreSQL database to any Data Warehouse of your choice like Amazon Redshift, Snowflake, Google BigQuery, or Firebolt within minutes with just a few clicks.

Give Hevo a try. Sign Up here for a 14-day full feature access trial and experience the feature-rich Hevo suite first hand. You can also check our unbeatable pricing and make a decision on your best-suited plan. 

Share your thoughts on learning about PostgreSQL unnest() function in the comments section below. If you have any questions, do let us know. We’d be happy to help.