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.
Example of use
-- Selecting and unnesting an array of integers
SELECT
unnest(ARRAY[1, 2]); -- Expands the array [1, 2] into individual rows
Source: https://www.w3resource.com/PostgreSQL/postgresql_unnest-function.php