Array aggregation with ARRAY_AGG in PostgreSQL - Step-by-Step Execution

Concept Flow - Array aggregation with ARRAY_AGG

Start Query

↓

Select rows

↓

Extract column values

↓

Aggregate values into array

↓

Return single row with array

↓

End Query

The query selects rows, extracts values from a column, aggregates them into an array, and returns the array as a single result.

Execution Sample

PostgreSQL

SELECT ARRAY_AGG(name) FROM employees;

This query collects all 'name' values from the 'employees' table into a single array.

Execution Table

Step	Action	Input Row	Aggregated Array State	Output
1	Start aggregation	None	{}	No output yet
2	Read row	{id:1, name:'Alice'}	{'Alice'}	No output yet
3	Read row	{id:2, name:'Bob'}	{'Alice', 'Bob'}	No output yet
4	Read row	{id:3, name:'Charlie'}	{'Alice', 'Bob', 'Charlie'}	No output yet
5	Finish aggregation	None	{'Alice', 'Bob', 'Charlie'}	Return array ['Alice', 'Bob', 'Charlie']

💡 All rows processed; aggregation complete and array returned.

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
aggregated_array	{}	['Alice']	['Alice', 'Bob']	['Alice', 'Bob', 'Charlie']	['Alice', 'Bob', 'Charlie']

Key Moments - 3 Insights

Why does ARRAY_AGG return a single row with an array instead of multiple rows?

ARRAY_AGG combines multiple input rows into one array, so the output is a single row containing that array, as shown in execution_table row 5.

What happens if the input column has NULL values?

By default, ARRAY_AGG includes NULLs in the array. You can exclude them using FILTER or WHERE clauses. This is not shown in the current example but is important to know.

Can ARRAY_AGG preserve the order of rows?

Yes, by using ORDER BY inside ARRAY_AGG, you can control the order of elements in the array. Without ORDER BY, the order is arbitrary.

Visual Quiz - 3 Questions

Look at the execution_table at step 3. What is the state of the aggregated array?

A ['Bob', 'Charlie']

B ['Alice']

C ['Alice', 'Bob']

D ['Alice', 'Charlie']

💡 Hint

Check the 'Aggregated Array State' column at step 3 in the execution_table.

At which step does the query return the final array?

A Step 2

B Step 5

C Step 4

D Step 1

💡 Hint

Look for the step where 'Output' shows the array being returned in the execution_table.

If the employees table had no rows, what would ARRAY_AGG return?

A NULL

B An empty array {}

C An error

D An array with one NULL element

💡 Hint

ARRAY_AGG returns NULL when no input rows exist; this is standard behavior in PostgreSQL.

Concept Snapshot

ARRAY_AGG(expression) aggregates multiple input values into a single array.
Returns one row with an array of all values.
Includes NULLs unless filtered.
Use ORDER BY inside ARRAY_AGG() to control element order.
Returns NULL if no input rows.
Useful for collecting column values into arrays in SQL.

Full Transcript

This visual execution shows how the PostgreSQL function ARRAY_AGG works. The query reads each row from the employees table, extracts the 'name' column, and adds it to an array. Step by step, the array grows from empty to include 'Alice', then 'Bob', then 'Charlie'. After all rows are processed, the query returns a single row containing the array of all names. Key points include that ARRAY_AGG returns one row with an array, includes NULLs by default, and returns NULL if no rows are found. This helps beginners see how aggregation collects multiple values into one array result.