JSON aggregation with JSON_AGG in PostgreSQL - Deep Dive

Overview - JSON aggregation with JSON_AGG

What is it?

JSON_AGG is a function in PostgreSQL that collects multiple rows of data into a single JSON array. It helps you combine many rows into one JSON list, making it easier to work with grouped data. This is useful when you want to return complex data structures from your database in a simple JSON format.

Why it matters

Without JSON_AGG, combining multiple rows into a JSON array would require manual processing outside the database, which is slow and error-prone. JSON_AGG lets the database do this work efficiently, saving time and reducing complexity in applications. It makes APIs and data exchange smoother by providing ready-to-use JSON arrays directly from queries.

Where it fits

Before learning JSON_AGG, you should understand basic SQL queries, SELECT statements, and simple aggregation functions like COUNT or SUM. After mastering JSON_AGG, you can explore more advanced JSON functions in PostgreSQL, such as JSON_BUILD_OBJECT or JSONB operations, and learn how to optimize JSON queries for performance.

Mental Model

Core Idea

JSON_AGG gathers multiple rows into one JSON array, turning many pieces into a single, organized list.

Think of it like...

Imagine collecting different colored beads (rows) and putting them all into one clear jar (JSON array) so you can carry and use them together easily.

┌───────────────┐
│ Multiple Rows │
│  (data items) │
└──────┬────────┘
       │
       ▼
┌─────────────────────┐
│   JSON_AGG Function  │
│  (collects rows into │
│    one JSON array)   │
└─────────┬───────────┘
          │
          ▼
┌─────────────────────┐
│  JSON Array Result   │
│ [item1, item2, ...] │
└─────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Basic SQL Aggregation

Concept: Learn how SQL aggregates data using functions like COUNT and SUM.

Aggregation functions combine multiple rows into a single value. For example, COUNT counts rows, SUM adds numbers. These functions help summarize data but return single values, not lists.

Result

You can get totals or counts from many rows, like total sales or number of users.

Understanding simple aggregation is key because JSON_AGG builds on this idea but returns a list instead of a single value.

2

Foundation

Introduction to JSON Data Type in PostgreSQL

Concept: PostgreSQL supports JSON data type to store and manipulate JSON documents directly.

JSON stores data as text but with structure. PostgreSQL lets you query and build JSON data inside the database, enabling flexible data formats beyond tables.

Result

You can store and retrieve JSON objects and arrays directly in your database columns.

Knowing JSON support in PostgreSQL prepares you to use JSON_AGG to create JSON arrays from query results.

3

Intermediate

Using JSON_AGG to Collect Rows

🤔Before reading on: do you think JSON_AGG returns a JSON object or a JSON array? Commit to your answer.

Concept: JSON_AGG aggregates multiple rows into a JSON array, combining values into a list format.

Example: SELECT JSON_AGG(name) FROM users; This returns a JSON array of all user names like ["Alice", "Bob", "Carol"]. It collects all rows from the query into one JSON array.

Result

["Alice", "Bob", "Carol"]

Understanding that JSON_AGG returns an array helps you design queries that produce structured JSON lists directly from the database.

4

Intermediate

Combining JSON_AGG with JSON_BUILD_OBJECT

🤔Before reading on: do you think JSON_AGG can aggregate complex JSON objects or only simple values? Commit to your answer.

Concept: You can use JSON_BUILD_OBJECT inside JSON_AGG to create arrays of JSON objects, not just simple values.

Example: SELECT JSON_AGG(JSON_BUILD_OBJECT('id', id, 'name', name)) FROM users; This returns an array of objects like [{"id":1,"name":"Alice"}, {"id":2,"name":"Bob"}].

Result

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

Knowing you can build complex JSON objects inside JSON_AGG lets you create rich, nested JSON structures in one query.

5

Intermediate

Grouping Rows with JSON_AGG

🤔Before reading on: do you think JSON_AGG works with GROUP BY to create grouped JSON arrays? Commit to your answer.

Concept: JSON_AGG can be combined with GROUP BY to create JSON arrays for each group of rows.

Example: SELECT department, JSON_AGG(name) FROM employees GROUP BY department; This returns one JSON array of employee names per department.

Result

[{"department":"Sales","json_agg":["Alice","Bob"]}, {"department":"HR","json_agg":["Carol"]}]

Using JSON_AGG with GROUP BY lets you organize data hierarchically, matching real-world grouping needs.

6

Advanced

Handling NULLs and Ordering in JSON_AGG

🤔Before reading on: do you think JSON_AGG includes NULL values by default or skips them? Commit to your answer.

Concept: JSON_AGG includes NULL values unless filtered, and you can specify order inside JSON_AGG for consistent output.

Example: SELECT JSON_AGG(name ORDER BY name) FROM users WHERE name IS NOT NULL; This returns a sorted JSON array without NULLs. You can filter NULLs using WHERE or inside JSON_AGG with FILTER clause.

Result

["Alice", "Bob", "Carol"]

Knowing how to control NULLs and order in JSON_AGG prevents unexpected results and ensures predictable JSON output.

7

Expert

Performance and Internal Behavior of JSON_AGG

🤔Before reading on: do you think JSON_AGG builds the JSON array incrementally or collects all rows first? Commit to your answer.

Concept: JSON_AGG builds the JSON array incrementally during query execution using internal memory structures, but large datasets can impact performance.

PostgreSQL processes JSON_AGG by accumulating each row's JSON value into an internal array structure. For large data, this can consume memory and slow queries. Using indexes, filtering early, or limiting rows helps performance. Also, JSON_AGG returns JSON type, while JSONB_AGG returns binary JSON for faster processing.

Result

Efficient JSON arrays for moderate data sizes; performance considerations for large sets.

Understanding JSON_AGG internals helps optimize queries and choose between JSON and JSONB aggregation for production systems.

Under the Hood

JSON_AGG works by iterating over each row in the query result and appending the row's value (or JSON object) to an internal array structure. This array is built incrementally in memory during query execution. Once all rows are processed, the array is serialized into a JSON text or JSONB binary format and returned as a single JSON array value.

Why designed this way?

PostgreSQL designed JSON_AGG to leverage its powerful aggregation framework, extending it to JSON data types. This approach allows combining multiple rows into complex JSON structures efficiently inside the database, reducing the need for external processing. Alternatives like manual string concatenation were error-prone and inefficient, so JSON_AGG provides a safe, optimized method.

┌───────────────┐
│ Query Result  │
│  Rows Stream  │
└──────┬────────┘
       │
       ▼
┌─────────────────────┐
│ JSON_AGG Aggregator  │
│  - Initialize array  │
│  - For each row:     │
│    append JSON value │
│  - After all rows:   │
│    serialize array   │
└─────────┬───────────┘
          │
          ▼
┌─────────────────────┐
│ JSON Array Output    │
│  (text or JSONB)    │
└─────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does JSON_AGG automatically remove duplicate values from the array? Commit to yes or no.

Common Belief: JSON_AGG removes duplicates and returns only unique values in the JSON array.

Reality: JSON_AGG includes all values from the rows, including duplicates, preserving the original order unless explicitly filtered.

Why it matters: Assuming duplicates are removed can cause bugs where repeated data appears unexpectedly, leading to incorrect application logic.

Quick: Does JSON_AGG return a JSON object or a JSON array? Commit to your answer.

Common Belief: JSON_AGG returns a JSON object that groups key-value pairs.

Reality: JSON_AGG returns a JSON array, which is an ordered list of values, not an object with keys.

Why it matters: Confusing arrays with objects can cause errors in parsing or using the JSON result in applications expecting a different structure.

Quick: Does JSON_AGG exclude NULL values by default? Commit to yes or no.

Common Belief: JSON_AGG automatically skips NULL values and does not include them in the output array.

Reality: JSON_AGG includes NULL values in the output array unless you filter them out explicitly.

Why it matters: Unexpected NULLs in JSON arrays can cause application errors or require extra handling, so knowing this helps write correct queries.

Quick: Can JSON_AGG be used to aggregate data from multiple tables in a single call? Commit to yes or no.

Common Belief: JSON_AGG can aggregate data from multiple tables directly without JOINs or subqueries.

Reality: JSON_AGG aggregates rows from a single query result; to combine multiple tables, you must JOIN or use subqueries first.

Why it matters: Misunderstanding this leads to incorrect query design and unexpected empty or partial JSON arrays.

Expert Zone

1

JSON_AGG preserves the order of rows as they appear in the query unless ORDER BY is specified inside the function, which is crucial for predictable JSON output.

2

Using JSONB_AGG instead of JSON_AGG can improve performance and storage efficiency because JSONB stores data in a binary format optimized for indexing and searching.

3

When aggregating large datasets, memory consumption can spike because JSON_AGG builds the entire array in memory before returning, so streaming or pagination strategies may be needed.

When NOT to use

Avoid JSON_AGG when dealing with extremely large datasets that exceed memory limits or when you need partial streaming of JSON data. Instead, consider client-side aggregation, window functions, or breaking queries into smaller chunks. For simple scalar aggregations, use traditional aggregate functions like STRING_AGG or ARRAY_AGG.

Production Patterns

In production, JSON_AGG is often used to build nested JSON responses for APIs, such as grouping orders with their items or users with their roles. It is combined with JOINs and filtered subqueries to produce rich, hierarchical JSON documents directly from the database, reducing application-side processing.

Connections

MapReduce

Both aggregate multiple data items into a structured result by grouping and combining.

Understanding JSON_AGG as a form of aggregation helps grasp how MapReduce collects and reduces data across distributed systems.

Object Serialization

JSON_AGG serializes database rows into JSON arrays, similar to how object serialization converts in-memory objects into transferable formats.

Knowing JSON_AGG's role in serialization clarifies how data moves from databases to applications in a structured, standardized way.

Data Compression

JSONB_AGG, a variant of JSON_AGG, stores JSON data in a compressed binary format, linking aggregation with efficient storage.

Recognizing JSON_AGG's relation to compression techniques helps optimize storage and query speed in large-scale systems.

Common Pitfalls

#1 Including NULL values unintentionally in JSON arrays.

Wrong approach:

SELECT JSON_AGG(name) FROM users;

Correct approach:

SELECT JSON_AGG(name) FROM users WHERE name IS NOT NULL;

Root cause:

Not filtering NULLs before aggregation leads to NULL entries in the JSON array, which may cause errors in consuming applications.

#2 Assuming JSON_AGG returns a JSON object instead of an array.

Wrong approach:

SELECT JSON_AGG(JSON_BUILD_OBJECT('id', id, 'name', name)) FROM users;
-- Then trying to access keys directly as if it was a JSON object.

Correct approach:

SELECT JSON_AGG(JSON_BUILD_OBJECT('id', id, 'name', name)) FROM users;
-- Treat the result as a JSON array of objects.

Root cause:

Misunderstanding JSON_AGG's output structure causes misuse of the JSON result.

#3 Not specifying ORDER BY inside JSON_AGG leading to unpredictable order.

Wrong approach:

SELECT JSON_AGG(name) FROM users;

Correct approach:

SELECT JSON_AGG(name ORDER BY name) FROM users;

Root cause:

Without explicit ordering, the database may return rows in any order, causing inconsistent JSON arrays.

Key Takeaways

JSON_AGG collects multiple rows into a single JSON array, making complex data easier to handle.

It works well with GROUP BY to create grouped JSON arrays for hierarchical data.

You can build arrays of simple values or complex JSON objects using JSON_BUILD_OBJECT inside JSON_AGG.

Be mindful of NULL values and ordering to ensure clean and predictable JSON output.

Understanding JSON_AGG's internal behavior helps optimize performance and avoid common pitfalls.