PostgreSQL UNNEST Function: Syntax, Examples, and Explanation

PostgreSQL UNNEST Function: A Detailed Guide

The UNNEST function in PostgreSQL is used to expand an array into a set of rows. This is particularly useful when dealing with arrays in a table column or when you want to normalize array data into tabular form for querying.

Syntax of UNNEST

UNNEST(array_expression)

• array_expression: The array to be expanded into rows.

Examples of Using UNNEST

Example 1: Basic UNNEST Usage

Code:

-- Expanding an array into rows
SELECT UNNEST(ARRAY['Apple', 'Banana', 'Cherry']);

Result:

unnest
Apple
Banana
Cherry

Example 2: Using UNNEST with a Table Column

Code:

-- Creating a table with an array column
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    tags TEXT[]
);

-- Inserting data into the table
INSERT INTO products (tags) VALUES (ARRAY['Electronics', 'Sale']);

-- Expanding the array column into rows
SELECT id, UNNEST(tags) AS tag FROM products;

Result:

id	tag
1	Electronics
1	Sale

Example 3: Using UNNEST with Multiple Arrays

Code:

-- Expanding two arrays into rows simultaneously
SELECT * 
FROM UNNEST(ARRAY[1, 2, 3], ARRAY['A', 'B', 'C']) AS t(num, letter);

Result:

num	letter
1	A
2	B
3	C

Explanation of code Examples:

1. Expanding Arrays: The UNNEST function breaks an array into individual rows, making it easier to process each element.

2. Array Columns in Tables: When working with array columns, UNNEST allows querying each array element as a separate row.

3. Multiple Arrays: When passing multiple arrays, PostgreSQL pairs elements from the arrays positionally.

Combining UNNEST with Other Functions

Filtering Unnested Rows:

Code:

-- Filtering rows from an array
SELECT UNNEST(ARRAY['Red', 'Green', 'Blue']) AS color
WHERE color = 'Green';

Result

color
Green

Using UNNEST in a Join

Code:

-- Joining an array with a table
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name TEXT
);

INSERT INTO categories (name) VALUES ('A'), ('B'), ('C');

-- Joining with an array
SELECT c.name, t.num 
FROM categories c
JOIN UNNEST(ARRAY[10, 20, 30]) AS t(num) ON c.id = t.num / 10;

Result

name	Num
A	10
B	20
C	30

Best Practices and Considerations

• Normalization: Consider normalizing your data instead of using arrays when relationships are complex.
• Performance: Be mindful of performance when working with large arrays. GIN indexes can be helpful for array columns.
• Data Integrity: Ensure that arrays being unnested are consistent in size and values.

All PostgreSQL Questions, Answers, and Code Snippets Collection.