SQL Academy · Lesson

UNNEST and Aggregating

Turn arrays into rows and back.

UNNEST and Aggregating is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is UNNEST?

PostgreSQL lets you store arrays inside a single column. But sometimes you need to work with each element individually — that is where UNNEST comes in.

UNNEST is a set-returning function that expands an array into a set of rows, one row per element. Think of it as the opposite of aggregation: instead of collapsing many rows into one, it explodes one value into many rows.

Basic UNNEST Example

The simplest use of UNNEST is passing an array literal directly. Each element becomes its own row in the result set.

Here we expand a plain array of integers into individual rows:

SELECT UNNEST(ARRAY[10, 20, 30, 40]) AS value;

UNNEST with Text Arrays

UNNEST works with any array type, including text. This is useful when you have a column that stores tags, categories, or comma-separated-style data encoded as a PostgreSQL array.

SELECT UNNEST(ARRAY['apple', 'banana', 'cherry']) AS fruit;

UNNEST from a Table Column

The real power of UNNEST appears when you use it on an actual table column. Each row in the table can have a different-length array, and UNNEST will expand all of them into individual rows.

In this example a products table has a tags text array column:

CREATE TABLE products (
  id   SERIAL PRIMARY KEY,
  name TEXT,
  tags TEXT[]
);

INSERT INTO products (name, tags) VALUES
  ('Laptop',  ARRAY['electronics', 'computing', 'portable']),
  ('Shirt',   ARRAY['clothing', 'casual']),
  ('Blender', ARRAY['kitchen', 'electronics']);

SELECT name, UNNEST(tags) AS tag
FROM products;

Counting Tag Occurrences

Once you unnest an array column, you can treat the results like any other rows and apply aggregate functions. Here we count how many products are associated with each tag.

The pattern is: UNNEST in a subquery or CTE, then GROUP BY the unnested value:

SELECT tag, COUNT(*) AS product_count
FROM (
  SELECT UNNEST(tags) AS tag
  FROM products
) AS expanded
GROUP BY tag
ORDER BY product_count DESC;

UNNEST with Ordinality

Sometimes the position of an element inside the array matters. PostgreSQL provides WITH ORDINALITY to attach a row number to each expanded element, so you know its original index in the array (1-based).

SELECT val, pos
FROM UNNEST(ARRAY['first', 'second', 'third']) WITH ORDINALITY AS t(val, pos);

Using Ordinality on a Table

WITH ORDINALITY is especially helpful when you store ordered lists in an array. For example, a playlist table where track order matters:

CREATE TABLE playlists (
  id     SERIAL PRIMARY KEY,
  title  TEXT,
  tracks TEXT[]
);

INSERT INTO playlists (title, tracks) VALUES
  ('Morning Mix', ARRAY['Song A', 'Song B', 'Song C']);

SELECT p.title, track, position
FROM playlists p,
     UNNEST(p.tracks) WITH ORDINALITY AS t(track, position)
ORDER BY p.id, position;

Filtering After UNNEST

Because UNNEST turns array elements into rows, you can filter them with a normal WHERE clause. This lets you find all rows whose array contains a specific value without using the array-contains operator.

SELECT DISTINCT name
FROM products,
     UNNEST(tags) AS tag
WHERE tag = 'electronics';

Aggregating Back into an Array

The reverse of UNNEST is ARRAY_AGG. After expanding rows and transforming or filtering them, you can collect the results back into an array. This round-trip pattern — unnest, process, re-aggregate — is a common PostgreSQL idiom.

SELECT ARRAY_AGG(tag ORDER BY tag) AS sorted_tags
FROM (
  SELECT UNNEST(ARRAY['cherry', 'apple', 'banana']) AS tag
) AS t;

Deduplicating Array Elements

A practical use of the unnest-aggregate round-trip is removing duplicate elements from an array. UNNEST the array, apply DISTINCT, then re-collect with ARRAY_AGG:

SELECT ARRAY_AGG(DISTINCT tag ORDER BY tag) AS unique_tags
FROM UNNEST(ARRAY['sql', 'database', 'sql', 'postgresql', 'database']) AS tag;

Combining Multiple Array Columns

You can UNNEST multiple arrays in parallel in the FROM clause. PostgreSQL pairs up elements by position. If the arrays have different lengths, the shorter one produces NULLs for the extra positions of the longer array.

SELECT key, value
FROM UNNEST(
  ARRAY['name',    'city',      'role'],
  ARRAY['Alice',   'Istanbul',  'DBA']
) AS t(key, value);

Knowledge Check

Test your understanding of UNNEST and aggregating arrays in PostgreSQL.

Lesson Recap

In this lesson you learned how to turn arrays into rows and back again using PostgreSQL built-in functions:

UNNEST — expands an array into one row per element, works on literals and table columns alike.
WITH ORDINALITY — attaches a positional index to each unnested element so you know its original order in the array.
Filtering — after unnesting you can use WHERE just like on any regular rows.
ARRAY_AGG — the reverse of UNNEST; collects rows back into an array, optionally with ORDER BY or DISTINCT.
Parallel UNNEST — multiple arrays in the FROM clause are expanded side by side, paired by position.

Mastering this unnest-process-reaggregate pattern lets you handle array data with the full expressive power of SQL.

Frequently asked questions

Is the “UNNEST and Aggregating” lesson free?

Yes — the full text of “UNNEST and Aggregating” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “UNNEST and Aggregating”?

Turn arrays into rows and back. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “UNNEST and Aggregating” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy