Unwrap postgresql array into rows

Improving performance

In cases where you have to work with large arrays or need bulk data transformations, performance is key. The unnest() function comes to rescue, providing a faster and more efficient way to unwrap arrays in PostgreSQL. Substituting 'explode_array' with unnest() boosts your query's performance remarkably.

/* You know what they say...
   Winter is coming... And so is better performance with unnest(). */
SELECT unnest(array_column) FROM table_name;


If you are defining custom functions, use LANGUAGE plpgsql IMMUTABLE to optimize it further.

Getting advanced with unnest

Unwrap and maintain relations

Preserve relations with other columns while unwrapping arrays. Because, families stick together, right?

/* Remember, cousins should always stick together at family reunions */
SELECT id, unnest(array_column)
FROM table_name;


Cross join your arrays

Cross join arrays from different columns within one table or across tables. Because two is better (and more fun) than one.

/* Rock, paper, scissors, SHOOT! */
SELECT a.id, b.element
FROM table_name a
CROSS JOIN LATERAL unnest(a.array_column) AS b(element);


Handling the multi-layered monster: nested arrays

For arrays within arrays scenario, a recursive query or multiple unnest() help tame the monster.

WITH RECURSIVE r AS (
  SELECT unnest(array_column) AS value, other_column
  FROM table_name
  WHERE parent_id IS NULL
   /* "I am the parent. Everything the light touches, is my kingdom." */
   
  UNION ALL
  
 SELECT unnest(array_column), p.other_column
  FROM table_name p
  INNER JOIN r ON p.id = r.other_column
)
SELECT * FROM r;


Your first-aid kit for common problems

Data type mismatches

Always specify the INT[] data type for array columns, because one cannot put round pegs in square holes.

Watch out for row merging

Be careful! unnest() without proper joins can merge rows, just like socks in a laundry.

Type mismatches

Ensure casting array contents to the correct type, as not all shapes fit all holes.

Time for some action!

Let's start by creating tables

Just as "Rome wasn't built in a day", we first need the building blocks: tables with serial primary keys.

CREATE TABLE bookshelf (
  id SERIAL PRIMARY KEY,
  books INT[]
);


Inserting the arrays

Insert some books onto your bookshelf.

/* "Harry! Did yeh put yehr books in the shelf?" said Hagrid. */
INSERT INTO bookshelf (books) VALUES ('{1,2,3}'), ('{4,5,6}');


Unwrapping the arrays

Now it's time for the great unwrap.

/* Shhh... It's an unwrap party! */
SELECT id, unnest(books) FROM bookshelf;


The final reveal

This is how your table should look like now, with each array element in a separate row:

 id | unnest
----+--------
  1 |      1
  1 |      2
  1 |      3
  2 |      4
  2 |      5
  2 |      6