PostgreSQL UNNEST() function

Last update on July 30 2024 10:00:16 (UTC/GMT +8 hours)

UNNEST() function

The unnest() function in PostgreSQL is used to expand an array into a set of rows. It takes an array as input and returns a new table where each element of the array occupies a separate row. This function is particularly useful for normalizing denormalized data stored in array formats and for performing operations that require each array element to be processed individually.

Uses of the PostgreSQL UNNEST() Function

Normalize Data: Transform array data into individual rows for easier processing.

Facilitate Joins: Enable joins with other tables by expanding arrays into rows.

Aggregate Data: Perform aggregate functions on individual array elements.

Filter Array Elements: Apply filters to specific elements within an array.

Convert Arrays to Tables: Turn arrays into tabular format for better data manipulation.

Combine with Other Functions: Use in conjunction with other PostgreSQL functions for advanced data operations.

Syntax:

unnest(anyarray)

Return Type:

setof anyelement

PostgreSQL Version:

Available since PostgreSQL 9.3

Example: PostgreSQL UNNEST() function

Basic Usage

Code:


-- Selecting and unnesting an array of integers
SELECT 
    unnest(ARRAY[1, 2]); -- Expands the array [1, 2] into individual rows

Explanation:

This SQL query uses the unnest() function to expand the array [1, 2] into individual rows.

Each element of the array will occupy its own row in the result set, resulting in two rows:

one for the element 1 and

another for the element 2.

Sample Output:

 unnest
--------
      1
      2
(2 rows)

In this example, the unnest() function expands the integer array [1, 2] into individual rows with each element occupying its own row.

Advanced Examples:

Expanding Array Columns from a Table

Suppose we have a table named test:

Table : test

create table test(
p_name text[],
p_id varchar(14),
p_email varchar(50));


Insert records:


INSERT INTO test (p_name, p_id, p_email)
VALUES (ARRAY['Peter Mont', 'Derak Powel'], 'PEMO-7894-OMEP', '[email protected]');


INSERT INTO test (p_name, p_id, p_email)
VALUES (ARRAY['Devid Hogg', 'Lusi Nail', 'Ben Knot'], 'DELU-8529-HONA', '[email protected]');

Data:

p_name                               |p_id          |p_email               |
-------------------------------------+--------------+----------------------+
{"Peter Mont","Derak Powel"}         |PEMO-7894-OMEP|[email protected] |
{"Devid Hogg","Lusi Nail","Ben Knot"}|DELU-8529-HONA|[email protected]|

To expand the p_name array column the following code can be used:

code:


-- Selecting the p_id column and unnesting the p_name array column
SELECT 
    p_id,         -- Selects the p_id column
    unnest(p_name) -- Expands the p_name array column into individual rows
FROM 
    test;          -- From the test table

Explanation:

This SQL query selects the p_id column and uses the unnest() function to expand the p_name array column from the test table.

Each element of the p_name array will occupy its own row, resulting in rows that combine p_id with each individual name from the p_name array.

Sample Output:

p_id          |unnest     |
--------------+-----------+
PEMO-7894-OMEP|Peter Mont |
PEMO-7894-OMEP|Derak Powel|
DELU-8529-HONA|Devid Hogg |
DELU-8529-HONA|Lusi Nail  |
DELU-8529-HONA|Ben Knot   |

In this example, the unnest() function is used to expand the pname array column from the test table. The resulting rows will include the p_id column along with each individual element of the p_name array.

Combining unnest() with Other Functions

To combine unnest() with string_to_array() the following code can be used:

code:


-- Using the unnest() function to expand an array created by the string_to_array() function
SELECT 
    unnest(                  -- Expands the array into individual rows
        string_to_array(     -- Converts a comma-separated string into an array
            'water,land,air', -- The comma-separated string to be converted
            ','              -- The delimiter used to split the string
        )
    );

Explanation:

This SQL query converts the comma-separated string 'water,land,air' into an array using the string_to_array() function, and then uses the unnest() function to expand this array into individual rows.

Each element of the array ('water', 'land', and 'air') will be displayed in its own row.

Sample Output:

unnest|
------+
water |
land  |
air   |

Here, the string_to_array() function converts a comma-separated string into an array, which is then passed to the unnest() function. This results in separate rows for each element of the array.

Using UNNEST() with Multiple Arrays

You can unnest multiple arrays together using the UNNEST() function, ensuring that each array has the same length:

code:


-- Selecting all columns
SELECT * 
FROM 
    -- Using the unnest() function to expand multiple arrays into individual rows
    unnest(
        ARRAY[1, 2, 3],        -- The first array to expand
        ARRAY['one', 'two', 'three'] -- The second array to expand
    ) 
    -- Assigning column aliases to the expanded arrays
    AS t(num, word);           -- num for the first array, word for the second array

Explanation:

This SQL query uses the unnest() function to expand two parallel arrays (ARRAY[1, 2, 3] and ARRAY['one', 'two', 'three']) into individual rows.

Each element of the first array is paired with the corresponding element of the second array.

The results are given column aliases num and word for the first and second arrays, respectively. The SELECT * statement retrieves all columns from this expanded result set.

Sample Output:

num|word |
---+-----+
  1|one  |
  2|two  |
  3|three|

This example demonstrates unnesting two arrays simultaneously, creating a table with two columns, num and word, each derived from the respective arrays.

Using UNNEST() with JSON Arrays

If you have a JSON array, you can use json_array_elements_text() to convert it to a set of rows, similar to unnest():

code:


-- Selecting the JSON array elements as text and assigning them an alias
SELECT 
    json_array_elements_text(
        '[{"name": "Alice"}, {"name": "Bob"}]' -- JSON array to expand
    ) 
    AS element; -- Alias for the expanded JSON elements

Explanation:

This SQL query uses the json_array_elements_text() function to expand a JSON array ([{"name": "Alice"}, {"name": "Bob"}]) into individual text elements.

Each element of the JSON array is returned as a separate row.

The results are given the alias element, representing each JSON object as a text element in the resulting set.

Sample Output:

element          |
-----------------+
{"name": "Alice"}|
{"name": "Bob"}  |

Common Use Cases

Transforming Array Data for Analysis: Use unnest() to break down array data into individual elements for detailed analysis.

Joining Tables with Array Columns: Combine unnest() with joins to relate array data with other tables.

Filtering and Aggregating Array Elements: Use unnest() to filter and aggregate specific elements within arrays.

Performance Considerations

Large Arrays: Unnesting very large arrays can be resource-intensive. Consider indexing and other optimizations.

Query Complexity: Combining unnest() with complex joins and subqueries might impact performance. Ensure your queries are optimized.

Integration with Other PostgreSQL Functions

Array Functions: Combine unnest() with array functions like array_agg(), array_length(), and array_remove().

String Functions: Use string_to_array() to convert strings to arrays before unnesting.

JSON Functions: Convert JSON arrays to SQL arrays for unnesting using jsonb_array_elements_text().

Frequently Asked Questions (FAQ) - PostgreSQL UNNEST() Function

1. What is the PostgreSQL UNNEST() function?

The UNNEST() function in PostgreSQL is used to expand an array into a set of rows. It takes an array as input and returns a new table where each element of the array occupies a separate row.

2. Why is the PostgreSQL UNNEST() function useful?

UNNEST() is particularly useful for normalizing denormalized data stored in array formats and for performing operations that require each array element to be processed individually.

3. What is the return type of the PostgreSQL UNNEST() function?

The UNNEST() function returns a set of anyelement, meaning it returns a set of rows, each containing one element from the input array.

4. Since which PostgreSQL version is the UNNEST() function available?

The UNNEST() function has been available since PostgreSQL 9.3.

5. What are common use cases for the PostgreSQL UNNEST() function?

Transforming Array Data for Analysis: Breaking down array data into individual elements for detailed analysis.

Joining Tables with Array Columns: Relating array data with other tables using joins.

Filtering and Aggregating Array Elements: Filtering and aggregating specific elements within arrays.

6. What are some advanced examples of using the PostgreSQL UNNEST() function?

Expanding Array Columns from a Table: Use UNNEST() to expand an array column from a table into individual rows.

Combining UNNEST() with Other Functions: Combine UNNEST() with functions like string_to_array() to convert strings to arrays before expanding.

Using UNNEST() with Multiple Arrays: Unnest multiple arrays together, ensuring each array has the same length.

7. Can the PostgreSQL UNNEST() function be used with JSON arrays?

Yes, for JSON arrays, the json_array_elements_text() function can be used to convert the JSON array to a set of rows, similar to UNNEST().

8. What are the performance considerations when using the PostgreSQL UNNEST() function?

Large Arrays: Unnesting very large arrays can be resource-intensive. Consider indexing and other optimizations.

Query Complexity: Combining UNNEST() with complex joins and subqueries might impact performance. Ensure your queries are optimized.

9. How can PostgreSQL UNNEST() be integrated with other PostgreSQL functions?

Array Functions: Combine UNNEST() with array functions like array_agg(), array_length(), and array_remove().

String Functions: Use string_to_array() to convert strings to arrays before unnesting.

JSON Functions: Convert JSON arrays to SQL arrays for unnesting using jsonb_array_elements_text().

Previous: STRING_TO_ARRAY function
Next: Introduction to JOIN