Introduction to PostgreSQL Unnest
PostgreSQL unnest is the type of array functions; the unnest function in PostgreSQL is basically used to expand the array into rows. Unnest function is converting an array into a table-like structure; we can also generate a table structure of an array using unnest function in PostgreSQL. Unnest array function is very useful in PostgreSQL for expanding the array into the set of values or convert the array into the table structure. If we need a table-like structure of array simultaneously, we have to use the unnest function on the array on which we have converted array data into the table-like structure.
Syntax:
Below is the syntax:
Unnest(Any_array_number (Any array element number which was we have used with unnest function)
Unnest (Any_array_text (Any array element text which was we have used with unnest function)
Select unnest (any_array (Number of text value which was used in array.))
Select unnest (any_array (Number of text value which was used in array.)) limit number;
Below is the parameter description syntax of Unnest array function:
- Unnest: This function which we have defined in PostgreSQL to set the element in a table-like structure. We have used unnest element with text as well as number array. We have to define the number or text element with unnest array function in PostgreSQL.
- Any array text: This is defined as which was we have using an array of text values to convert the array into the table like structure in PostgreSQL by using unnest array function.
- Any array number: This is defined as a number. We have used any number values to convert an array into the table-like structure in PostgreSQL using the unnest array function.
- Select: This operation is used to select the array’s value by using the unnest array function in PostgreSQL. Select operations are very useful and important while using unnest array function in PostgreSQL.
- Limit: In PostgreSQL, we have also using the limit clause with unnest function in PostgreSQL. While using the limit clause will show the number as per the limit we have used with unnest function.
How does Unnest Function work in PostgreSQL?
- It basically has two overloads. The first overload has the obvious usefulness, and the second overload is the exotic.
- The first overload of usefulness aims to transform the values from an array into a single array.
- In the old version of PostgreSQL, when we have to convert an array into the table structure, we have to use an array with the cross join. After joining the array with cross join, we have generated the series of the same.
- After generating series, all the elements of the array will be structured into the table using cross join and generate series function.
The below example shows that in the old version, we have used cross join with generate series function to convert the array into the table structure.
Code:
SELECT test_unnest[A] as array_element FROM (SELECT ARRAY[11, 12, 13, 14, 15, 16, 17, 18, 19, 20] As test_unnest) as gen_ser CROSS JOIN generate_series(1, 10) As A;
Output:
- The above example shows that we have used array element as [11, 12, 13, 14, 15, 16, 17, 18, 19, 20]; we have used this array element with generate series function for converting an array into the table like structure.
- We have also used cross join with the array element and generate a series function for converting an array into a table-like structure.
- By using unnest function, we have no need to use cross join or generate series function to convert an array into the table like structure. We have simply using unnest function with an array.
- We have also used an array for number or text to convert the array into a table-like structure in PostgreSQL.
- We can also use unnest function with an order by clause. We have using order by clause with 1 while using an unnest function.
- We have also use the limit clause with unnest function. After using a limit clause, it will show the output as per the number which was we have used with the limit.
Examples of PostgreSQL Unnest
Different examples are mentioned below:
Example #1 – Unnest function with array as a number.
Below example shows that unnest function with array as a number. In the below example, we use an array of numbers as [1, 2, 3, 4, 5, 6, 6, 7, 8, 9, 10].
Code:
SELECT unnest(ARRAY[1, 2, 3, 4, 5, 6, 6, 7, 8, 9, 10]);
Output:
Example #2 – Unnest function with array as text.
Below example shows that unnest function with array as text. In below example we are using array of text as [‘ABC’, ‘PQR’, ‘XYZ’, ‘ABC’, ‘PQR’, ‘XYZ’, ‘ABC’, ‘PQR’, ‘XYZ’, ‘ABC’, ‘PQR’, ‘XYZ’].
Code:
SELECT unnest(ARRAY['ABC', 'PQR', 'XYZ', 'ABC', 'PQR', 'XYZ', 'ABC', 'PQR', 'XYZ', 'ABC', 'PQR', 'XYZ']);
Output:
Example #3 – Unnest function with an order by clause.
Below example shows that unnest function with an order by clause. In the below example, we use an array of numbers as [1, 2, 3, 4, 5, 6, 6, 7, 8, 9, 10].
Code:
SELECT unnest(ARRAY[1, 2, 3, 4, 5, 6, 6, 7, 8, 9, 10]) order by 1;
Output:
Example #4 – Unnest function with limit clause.
Below example shows that unnest function with limit clause. In the below example, we use an array of numbers as [1, 2, 3, 4, 5, 6, 6, 7, 8, 9, 10].
Code:
SELECT unnest(ARRAY[1, 2, 3, 4, 5, 6, 6, 7, 8, 9, 10]) limit 5;
SELECT unnest(ARRAY[1, 2, 3, 4, 5, 6, 6, 7, 8, 9, 10]) limit 8;
Output:
Recommended Articles
This is a guide to PostgreSQL Unnest. Here we discuss the introduction, how unnest function works in PostgreSQL? and examples, respectively. You may also have a look at the following articles to learn more –
