JSON aggregation with JSON_AGG in PostgreSQL - Time & Space Complexity

Time Complexity: JSON aggregation with JSON_AGG

O(n)

Understanding Time Complexity

When we use JSON_AGG in PostgreSQL, we combine many rows into one JSON array. Understanding how long this takes helps us know how it will behave as data grows.

We want to find out how the time to create this JSON array changes when we have more rows.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT department_id, JSON_AGG(employee_name) AS employees
FROM employees
GROUP BY department_id;
    

This query groups employees by their department and collects their names into a JSON array for each department.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning all employee rows and adding each employee name to a JSON array per department.
• How many times: Once for each employee row in the table.

How Execution Grows With Input

As the number of employees grows, the work to add each name to the JSON array grows linearly.

Input Size (n)	Approx. Operations
10	About 10 additions to JSON arrays
100	About 100 additions to JSON arrays
1000	About 1000 additions to JSON arrays

Pattern observation: The time grows directly with the number of employees; doubling employees roughly doubles the work.

Final Time Complexity

Time Complexity: O(n)

This means the time to build the JSON arrays grows in a straight line as the number of rows increases.

Common Mistake

[X] Wrong: "JSON_AGG runs in constant time no matter how many rows there are."

[OK] Correct: Each row must be processed and added to the JSON array, so more rows mean more work and more time.

Interview Connect

Understanding how aggregation functions like JSON_AGG scale helps you explain query performance clearly and shows you know how databases handle data growth.

Self-Check

"What if we used JSON_AGG on a filtered subset of employees instead of all employees? How would the time complexity change?"