PostgreSQL unnest() Function: Syntax & 6 Essential Queries

on PostgreSQL • July 26th, 2022 • Write for Hevo

PostgreSQL unnest() Function Featured Image

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.

Table of Contents

Prerequisites

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.

Scale your data integration effortlessly with Hevo’s Fault-Tolerant No Code Data Pipeline

As the ability of businesses to collect data explodes, data teams have a crucial role to play in fueling data-driven decisions. Yet, they struggle to consolidate the scattered data in their warehouse to build a single source of truth. Broken pipelines, data quality issues, bugs and errors, and lack of control and visibility over the data flow make data integration a nightmare.

1000+ data teams rely on Hevo’s Data Pipeline Platform to integrate data from over 150+ sources in a matter of minutes. Billions of data events from sources as varied as SaaS apps, Databases, File Storage, and Streaming sources can be replicated in near real-time with Hevo’s fault-tolerant architecture. What’s more – Hevo puts complete control in the hands of data teams with intuitive dashboards for pipeline monitoring, auto-schema management, and custom ingestion/loading schedules. 

All of this combined with transparent pricing and 24×7 support, makes us the most loved data pipeline software on review sites.

Take our 14-day free trial to experience a better way to manage data pipelines.

Get Started for Free With Hevo!

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:

Unnest One-Dimension Array in PostgreSQL Example
Image Source: Self

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 One-Dimension Array in PostgreSQL Example 2
Image Source: Self

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.

Unnest Multi-Dimension Array in PostgreSQL Example
Image Source: Self

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:

Expand Multiple Array with Unnest Function
Image Source: Self

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 Order By Clause Example
Image Source: Self

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 Limit Clause Example
Image Source: Self

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:

Unnest Function With Distinct Clause Example
Image Source: Self

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.

Try Hevo’s No-Code Automated Data Pipeline For PostgreSQL