PostgreSQL unnest Function

The unnest() function in PostgreSQL expands an array into a set of rows, with one row per array element. It's essential for converting arrays to relational format, analyzing array contents, and joining array data with other tables.

Syntax

-- Expand array into rows
unnest(array)

-- Expand multiple arrays (parallel expansion)
unnest(array1, array2, ...)

-- With table reference
SELECT * FROM unnest(array) AS element

-- With ordinality (position)
SELECT * FROM unnest(array) WITH ORDINALITY AS t(element, position)

Official Documentation

Example usage

-- Basic unnest
SELECT unnest(ARRAY[1, 2, 3, 4, 5]);
-- Result: 5 rows with values 1, 2, 3, 4, 5

-- Unnest text array
SELECT unnest(ARRAY['apple', 'banana', 'orange']) AS fruit;

-- Unnest array from table
SELECT
    product_id,
    product_name,
    unnest(tags) AS tag
FROM products;

-- Count array elements by unnesting
SELECT
    tag,
    COUNT(*) AS product_count
FROM products, unnest(tags) AS tag
GROUP BY tag
ORDER BY product_count DESC;

-- WITH ORDINALITY to get positions
SELECT element, position
FROM unnest(ARRAY['first', 'second', 'third'])
     WITH ORDINALITY AS t(element, position);
-- Result:
-- first  | 1
-- second | 2
-- third  | 3

-- Unnest multiple arrays in parallel
SELECT *
FROM unnest(
    ARRAY['Alice', 'Bob', 'Charlie'],
    ARRAY[25, 30, 35]
) AS people(name, age);
-- Result:
-- Alice   | 25
-- Bob     | 30
-- Charlie | 35

-- Filter unnested values
SELECT product_id, tag
FROM products, unnest(tags) AS tag
WHERE tag LIKE '%electronic%';

-- Join unnested array with another table
SELECT
    p.product_name,
    c.category_name
FROM products p,
     unnest(p.category_ids) AS cat_id
JOIN categories c ON c.id = cat_id;

-- Find distinct array values
SELECT DISTINCT unnest(tags) AS unique_tag
FROM products
ORDER BY unique_tag;

-- Aggregate after unnesting
SELECT
    user_id,
    AVG(score) AS avg_score
FROM user_activity,
     unnest(daily_scores) AS score
GROUP BY user_id;

-- Unnest with WHERE on table
SELECT unnest(friend_ids) AS friend_id
FROM users
WHERE user_id = 123;

-- Create normalized table from arrays
INSERT INTO product_tags (product_id, tag)
SELECT product_id, unnest(tags)
FROM products;

-- Unnest multidimensional array (flattens all levels)
SELECT unnest(ARRAY[[1,2], [3,4], [5,6]]);
-- Result: 1, 2, 3, 4, 5, 6 (6 rows)

-- Combine with window functions
SELECT
    product_id,
    tag,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY tag) AS tag_rank
FROM products,
     unnest(tags) AS tag;

-- Update using unnested values
UPDATE products
SET featured = true
WHERE id IN (
    SELECT DISTINCT product_id
    FROM products, unnest(tags) AS tag
    WHERE tag = 'featured'
);

Common issues

1. Cartesian product: unnest in FROM clause creates one row per element; be careful with multiple unnest calls.
2. NULL arrays: unnest of NULL array returns zero rows, not one row with NULL.
3. Empty arrays: unnest of empty array returns zero rows.
4. Multidimensional arrays: unnest flattens all dimensions into single column.

Best practices

1. Use unnest for converting arrays to relational format for analysis.
2. Combine with WITH ORDINALITY when you need element positions.
3. For parallel expansion of multiple arrays, pass them all to one unnest call.
4. Use CROSS JOIN LATERAL for more complex unnesting scenarios.

Frequently Asked Questions

Q: What's the difference between unnest and array_agg?
A: unnest expands an array into rows (array → rows), while array_agg aggregates rows into an array (rows → array). They're inverse operations.

Q: How do I unnest multiple arrays and keep them aligned?
A: Pass multiple arrays to unnest in one call: unnest(array1, array2, array3). This expands them in parallel. Separate unnest calls would create a Cartesian product.

Q: What does unnest return for NULL or empty arrays?
A: Both return zero rows. If you need at least one row, use COALESCE with a default array: unnest(COALESCE(array_col, ARRAY[default_value])).

Q: How do I get the array index/position with unnest?
A: Use WITH ORDINALITY: SELECT * FROM unnest(array) WITH ORDINALITY AS t(value, position). The position starts at 1 (1-indexed).

Q: Can I unnest arrays from multiple columns simultaneously?
A: Yes, use: SELECT * FROM table_name, unnest(array_col1) AS col1, unnest(array_col2) AS col2. But beware: separate unnest calls create a Cartesian product. For parallel expansion, use unnest(array_col1, array_col2).