PostgreSQL – UNNEST Function

ByAdmin

The unnest function in PostgreSQL is used to transform an array or a set of values into a set of rows. This is particularly useful when you have a column that stores arrays or sets and you want to work with the individual elements within those arrays as separate rows.

Below is the basic syntax of using the unnest function:

SELECT unnest(array_column) AS column_alias 
FROM your_table;

Where:

array_column is the name of the column that contains the arrays you want to unnest.

column_alias is an alias that you can provide to the unnested elements. This will be the name of the resulting column in the output.

Here’s an example:

Let’s say you have a table named orders with a column named product_ids that stores an array of product IDs for each order:

CREATE TABLE orders 
(order_id serial PRIMARY KEY, 
product_ids integer[] ); 

INSERT INTO orders (product_ids) 
VALUES 
('{101, 102, 103}'), 
('{104, 105}'), 
('{106}');

You can use the unnest function to retrieve each individual product ID as a separate row:

SELECT unnest(product_ids) AS product_id 
FROM orders;

The output would be:

product_id 
------------ 
101 
102 
103 
104 
105 
106 
(6 rows)

Similar Posts

PostgreSQL – STRING_TO_ARRAY Function

ByAdmin

The string_to_array function is used to split a string into an array of substrings based on a specified delimiter. It takes a string as input and returns an array of text elements as output. Below is the syntax for the string_to_array function: string_to_array(input_string, delimiter) where input_string is the string that you want to split into…

PostgreSQL – ARRAY_POSITION Function

ByAdmin

ARRAY_POSITION function in PostgreSQL is used to return the position of the first occurrence of an array element. Here is the basic syntax of using array_position function. SELECT array_position(your_array_column, int)) AS position FROM your_table ; Example of using array_position function: SELECT array_position(array(20, 10, 10, 30), 10) AS position; The output would be 2 ; as…