PostgreSQL provides a built-in function named UNNEST() that expands the given array into rows. The UNNEST() function takes an array as an argument/parameter and expands the given array into a set of rows. In simple terms, it converts an array to a table-like structure.
This post will explain how the UNNEST() function works in Postgres with examples. So, let’s get started.
How to Use UNNEST() Function in Postgres?
The below snippet illustrates the syntax of UNNEST() function:
UNNEST(arr);
Where “arr” is an array to be expanded. We can use the LIMIT clause, DISTINCT clause, ORDER BY clause, etc., with the UNNEST() function to achieve different functionalities.
Example #1: How to Expand/Convert a Numeric Array Into Rows?
In this example, we will use the UNNEST() function to expand the given array into a set of rows:
SELECT UNNEST(ARRAY[1, 5, 72, 100, 514]);
The output proves the working of UNNEST() function as it successfully expanded the array of five elements into five rows.
Example #2: How to Expand/Convert a String Array to Rows?
We will pass an array of string elements to the UNNEST() function:
SELECT UNNEST(ARRAY['John', 'Joe', 'Mike', 'Daniel', 'Ambrose']);
The UNNEST() function expanded the string array into different rows.
Example #3: How to Expand an Array into a Specific Order?
We can use the ORDER BY clause with the UNNEST() function to expand the array into a set of rows but in a specific order:
SELECT UNNEST(ARRAY[10, 123, 32, 14, 9]) ORDER BY 1;
The output authenticates the working of UNNEST() with the ORDER BY clause.
Example #4: How to Expand an Array Into Limited Rows?
Use the LIMIT clause with the UNNEST() function to expand/convert the given array into limited rows:
SELECT UNNEST(ARRAY[10, 123, 32, 14, 9]) LIMIT 3;
In this way, you can use the LIMIT clause with the UNNEST() function to get the limited rows.
Example #5: How to Use UNNEST() Function on Tables Data?
The below query will show you the details of the student_details table:
SELECT * FROM student_details;
The output shows that the std_email column is an array of text-type data. Let’s use the UNNEST() function to expand the given array into a set of rows:
SELECT UNNEST(std_email) FROM student_details;
This is how the UNNEST() function works on the table’s data.
Conclusion
PostgreSQL provides a built-in function named UNNEST() that accepts an array as an argument and expands the given array into a set of rows. The UNNEST() function can accept the array of any data type, such as text, int, etc., and expands the given array into rows. This post explained the working of the UNNEST() function with examples.
