JSON aggregation with JSON_AGG in PostgreSQL - Step-by-Step Execution

Concept Flow - JSON aggregation with JSON_AGG

Start Query

↓

Select rows from table

↓

Extract JSON objects from each row

↓

Aggregate JSON objects into JSON array

↓

Return single JSON array as result

↓

End Query

The query selects rows, converts each row to a JSON object, then combines all these JSON objects into a single JSON array using JSON_AGG.

Execution Sample

PostgreSQL

SELECT JSON_AGG(row_to_json(t))
FROM (SELECT id, name FROM users) t;

This query collects all users' id and name into a JSON array of JSON objects.

Execution Table

Step	Action	Row Data	JSON Object	Aggregation State
1	Select first row	id=1, name='Alice'	{"id":1,"name":"Alice"}	[{"id":1,"name":"Alice"}]
2	Select second row	id=2, name='Bob'	{"id":2,"name":"Bob"}	[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]
3	Select third row	id=3, name='Carol'	{"id":3,"name":"Carol"}	[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"},{"id":3,"name":"Carol"}]
4	No more rows	N/A	N/A	Final JSON array returned

💡 All rows processed and aggregated into one JSON array.

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
Aggregation State	[]	[{"id":1,"name":"Alice"}]	[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]	[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"},{"id":3,"name":"Carol"}]	[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"},{"id":3,"name":"Carol"}]

Key Moments - 2 Insights

Why does JSON_AGG return a single JSON array instead of multiple rows?

Because JSON_AGG collects all JSON objects from each row into one array, it returns one row with the aggregated JSON array, as shown in execution_table step 4.

What happens if the table has no rows?

JSON_AGG returns NULL or an empty array depending on usage, since there are no JSON objects to aggregate. This is implied by the exit condition in execution_table step 4.

Visual Quiz - 3 Questions

Look at the execution table, what is the aggregation state after processing the second row?

A [{"id":1,"name":"Alice"}]

B [{"id":2,"name":"Bob"}]

C [{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]

D []

💡 Hint

Check the 'Aggregation State' column at step 2 in the execution_table.

At which step does the query finish aggregating all rows?

A Step 2

B Step 4

C Step 3

D Step 1

💡 Hint

Look for the step where 'No more rows' is indicated in the execution_table.

If a new row with id=4 and name='Dave' is added, what will be the aggregation state after processing it?

A [{"id":1,"name":"Alice"},{"id":2,"name":"Bob"},{"id":3,"name":"Carol"},{"id":4,"name":"Dave"}]

B [{"id":4,"name":"Dave"}]

C [{"id":1,"name":"Alice"},{"id":2,"name":"Bob"},{"id":3,"name":"Carol"}]

D []

💡 Hint

Refer to the pattern of aggregation in variable_tracker and add the new JSON object at the end.

Concept Snapshot

JSON_AGG aggregates multiple JSON objects into a single JSON array.
Use it with row_to_json to convert rows to JSON.
Syntax: SELECT JSON_AGG(row_to_json(t)) FROM (SELECT ...) t;
Returns one JSON array containing all row objects.
If no rows, returns NULL or empty array.

Full Transcript

This visual execution trace shows how the PostgreSQL function JSON_AGG works. The query selects rows from a table, converts each row into a JSON object using row_to_json, and then aggregates all these JSON objects into a single JSON array. Step by step, each row's data is turned into JSON and added to the growing JSON array. After all rows are processed, the final JSON array is returned as the query result. Key points include that JSON_AGG returns one row with the full JSON array, and if there are no rows, the result is NULL or empty. This helps beginners see how JSON_AGG collects data visually and understand the aggregation process clearly.