Right, so you’ve got this array. It’s sitting there, all neat and tidy, a perfect little list. But now you want to spread it out. You want to see each element on its own row, maybe to join it with another table, or because your boss wants a “flat list” and you’re not about to argue. This is where unnest() comes in. It’s the array equivalent of taking a packed suitcase and dumping its contents all over your hotel room bed.

The core idea is simple: unnest() takes an array and returns a table with one row for each element in the array. It’s a set-returning function (SRF), which is a fancy way of saying it gives back multiple rows from a single input. This is your primary tool for breaking arrays back into a sane, relational format.

Let’s start with the basics. You’ll most often see it in the FROM clause, which is where the magic of turning one row into many happens.

SELECT unnest(ARRAY['cat', 'dog', 'parrot']) AS animal;

 animal
---------
 cat
 dog
 parrot
(3 rows)

See? We went from one row containing an array to three individual rows. The database engine handles the expansion seamlessly.

Using unnest() in the FROM clause

This is the most common and powerful way to use it. When you place it in the FROM clause, it behaves like a table. This is crucial because it allows you to join the exploded array back to the original row or other tables. Let’s use a realistic example with a user table.

-- Imagine a table where we, for some questionable reason, stored roles in an array.
CREATE TEMP TABLE users (
    id serial PRIMARY KEY,
    name text,
    roles text[]
);

INSERT INTO users (name, roles) VALUES
('Alice', ARRAY['admin', 'user']),
('Bob', ARRAY['user']),
('Carol', NULL);

-- Now, let's unnest those roles alongside the user's name.
SELECT u.id, u.name, r.role
FROM users u
CROSS JOIN unnest(u.roles) AS r(role);

Whoa, hold on. Did you see what I did there? I used u.roles (a column from the users table) as the direct input to the unnest() function inside the FROM clause. This is the secret sauce. For each row from users, PostgreSQL will call unnest() on that specific array and then join the resulting set of rows back. The CROSS JOIN is implied here; LATERAL is also happening automatically, which is the bit of SQL magic that lets the inner unnest() see the value of u.roles from the outer table.

The result from our example would be:

 id | name  | role
----+-------+-------
  1 | Alice | admin
  1 | Alice | user
  2 | Bob   | user
(3 rows)

Notice what’s missing? Carol. Because her roles field was NULL, unnest(NULL) returns… nothing. Zero rows. It doesn’t return a NULL row; it returns an empty set. This is a common gotcha.

Handling NULLs and Empty Arrays

This brings us to a critical point: unnest()’s behavior with edge cases. You need to understand this to avoid losing data.

-- Let's see what happens with our different cases.
SELECT
    u.name,
    u.roles AS original_array,
    r.role
FROM users u
LEFT JOIN unnest(u.roles) AS r(role) ON true;

 name  |  original_array   | role
-------+-------------------+-------
 Alice | {admin,user}      | admin
 Alice | {admin,user}      | user
 Bob   | {user}            | user
 Carol | NULL              | NULL
(4 rows)

Aha! Using a LEFT JOIN instead of a CROSS JOIN ensures that Carol from our users table isn’t dropped. Since there’s no row from unnest() to match her NULL array, the join still returns her row, but with NULL in the role column. This is almost always what you want. An empty array '{}' would behave differently—it would return one row from the user with an empty set from the unnest(), which the LEFT JOIN would preserve as a NULL role. Test it yourself. I’ll wait.

Using unnest() in the SELECT clause

You can also use unnest() directly in the SELECT list. This works, but I find its behavior… let’s call it “surprising” and rarely what you actually want.

SELECT name, unnest(roles) AS role FROM users;

 name  | role
-------+-------
 Alice | admin
 Alice | user
 Bob   | user
(3 rows)

Look at that output. It looks like the first query, but there’s a subtle and terrible difference: the other columns (id) are gone. When you put a set-returning function in the SELECT clause, it expands the entire result set. The name column from the original row is just repeated for every resulting array element. It’s not a “row-by-row” operation in the way you might think; it’s more like the function output sets the number of rows for the whole query. This syntax is older and generally less flexible than the FROM clause method. Avoid it for anything serious. You lose the ability to do outer joins, making it impossible to handle those NULL arrays properly.

The Power of Multiple unnests()

Here’s a party trick. You can unnest multiple arrays in parallel. Why would you do this? Imagine you have two arrays that are meant to be related, like keys and values.

SELECT unnest(ARRAY['a', 'b', 'c']) AS letter, unnest(ARRAY[1, 2, 3]) AS number;

 letter | number
--------+--------
 a      |      1
 b      |      2
 c      |      3
(3 rows)

This works only if the arrays are the same length. If they’re not, PostgreSQL will happily truncate the result to the length of the shortest array and just merrily discard the extra elements in the longer one. No error. Nothing. It’s the most silent of data murders. Always, always make sure your parallel arrays are the same length before doing this. It’s a fragile pattern, but when you need it, it’s incredibly useful. For anything more permanent, you should probably question your schema design and consider a proper related table instead.