PostgreSQL: Flatten Nested Arrays

I’ve encountered data structures where arrays are nested inside arrays—maybe a list of projects, each containing a list of skills required, and you need a single comprehensive list of all skills. Or you have a two-dimensional matrix of data that you need to convert to a single dimension for processing. Flattening nested arrays is a common transformation you’ll do when working with complex data structures.

PostgreSQL’s UNNEST function lets you expand nested arrays one level at a time, and with ARRAY_AGG, you can reassemble them into a flat structure. It’s elegant once you understand the pattern.

Quick Answer: Use UNNEST with ARRAY_AGG

To flatten nested arrays:

SELECT
    emp_id,
    skills_by_year,
    array_agg(skill) as all_skills_flattened
FROM employees,
LATERAL (
    SELECT UNNEST(UNNEST(skills_by_year)) as skill
) sub
WHERE skills_by_year IS NOT NULL
GROUP BY emp_id, skills_by_year;

UNNEST twice flattens two levels.

How It Works

Each UNNEST expands one level of nesting. ARRAY_AGG reassembles into single array.

Different Approaches & When to Use Them

Approach 1: UNNEST + ARRAY_AGG

SELECT array_agg(elem)
FROM UNNEST(nested_array) AS elem;

For 2D arrays.

Approach 2: Recursive Approach (Deep Nesting)

CREATE OR REPLACE FUNCTION flatten_array(arr ANYARRAY)
RETURNS SETOF ANYTYPE AS $$
BEGIN
    RETURN QUERY SELECT UNNEST(arr);
END;
$$ LANGUAGE plpgsql;

Approach 3: Extract and Combine

SELECT
    array_cat(skills[1], array_cat(skills[2], skills[3])) as flattened
FROM table_name;

For known dimensions.

Real-World Example

SELECT
    project_id,
    array_agg(skill) as all_required_skills
FROM projects,
LATERAL UNNEST(UNNEST(skill_categories)) as skill
GROUP BY project_id;

FAQ

Q: Can I flatten more than 2 levels? A: Yes, add more UNNEST: UNNEST(UNNEST(UNNEST(...))).

Q: Performance? A: O(n) where n = total elements. Fast.

Q: Does order matter? A: No, flattened results are unordered.

Wrapping Up

Flatten with UNNEST. Chain for depth. ARRAY_AGG rebuilds as single array.