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.

Practice

1. What does the JSON_AGG function do in PostgreSQL?

easy

A. It collects multiple rows into a JSON array.

B. It converts a JSON array into separate rows.

C. It filters JSON data based on a condition.

D. It updates JSON data in a table.

Solution

1. Step 1: Understand JSON_AGG purpose

   JSON_AGG is an aggregate function that collects multiple rows into a single JSON array.

2. Step 2: Compare options with function behavior

   Only It collects multiple rows into a JSON array. correctly describes this behavior; others describe different functions or actions.

3. Final Answer:

   It collects multiple rows into a JSON array. -> Option A

4. Quick Check:

   JSON_AGG collects rows = It collects multiple rows into a JSON array [OK]

Hint: Remember: AGG means aggregate rows into one [OK]

Common Mistakes:

• Confusing JSON_AGG with JSON functions that extract data
• Thinking JSON_AGG filters rows
• Assuming JSON_AGG updates JSON data

2. Which of the following is the correct syntax to use JSON_AGG in a query?

easy

A. SELECT JSON_AGG(column_name) table_name;

B. SELECT JSON_AGG FROM table_name WHERE column_name;

C. SELECT JSON_AGG(column_name) WHERE table_name;

D. SELECT JSON_AGG(column_name) FROM table_name;

Solution

1. Step 1: Review correct SQL aggregate function syntax

   Aggregate functions like JSON_AGG require parentheses around the column and a FROM clause specifying the table.

2. Step 2: Check each option for syntax correctness

   SELECT JSON_AGG(column_name) FROM table_name; uses correct syntax: SELECT JSON_AGG(column_name) FROM table_name; Others miss FROM or parentheses or have misplaced WHERE.

3. Final Answer:

   SELECT JSON_AGG(column_name) FROM table_name; -> Option D

4. Quick Check:

   Correct aggregate syntax = SELECT JSON_AGG(column_name) FROM table_name; [OK]

Hint: Aggregate functions need parentheses and FROM clause [OK]

Common Mistakes:

• Omitting FROM clause
• Missing parentheses after JSON_AGG
• Misplacing WHERE clause

3. Given the table products with columns category and name, what is the output of this query?

SELECT category, JSON_AGG(name) FROM products GROUP BY category ORDER BY category;

medium

A. [{"category":"Books","json_agg":["Novel","Comics"]}, {"category":"Toys","json_agg":["Car","Doll"]}]

B. [{"category":"Books","json_agg":"Novel,Comics"}, {"category":"Toys","json_agg":"Car,Doll"}]

C. [{"category":"Books","json_agg":["Novel"]}, {"category":"Toys","json_agg":["Car"]}]

D. [{"category":"Books","json_agg":null}, {"category":"Toys","json_agg":null}]

Solution

1. Step 1: Understand GROUP BY with JSON_AGG

   The query groups rows by category and collects all product names per category into a JSON array.

2. Step 2: Analyze expected JSON_AGG output

   JSON_AGG returns an array of names per category, so each category has a JSON array of product names, not a string or null.

3. Final Answer:

   [{"category":"Books","json_agg":["Novel","Comics"]}, {"category":"Toys","json_agg":["Car","Doll"]}] -> Option A

4. Quick Check:

   JSON_AGG groups names into arrays = [{"category":"Books","json_agg":["Novel","Comics"]}, {"category":"Toys","json_agg":["Car","Doll"]}] [OK]

Hint: JSON_AGG returns arrays, not comma-separated strings [OK]

Common Mistakes:

• Expecting JSON_AGG to return strings instead of arrays
• Ignoring GROUP BY effect
• Assuming null results without data

4. Identify the error in this query:

SELECT category, JSON_AGG(name) FROM products GROUP category;

medium

A. Missing FROM clause

B. Incorrect GROUP BY syntax

C. JSON_AGG cannot be used with GROUP BY

D. Missing parentheses in JSON_AGG

Solution

1. Step 1: Check GROUP BY syntax

   The correct syntax is GROUP BY column_name, but the query uses GROUP category without BY.

2. Step 2: Verify other parts of the query

   FROM clause is present, JSON_AGG has parentheses, and it can be used with GROUP BY, so those are correct.

3. Final Answer:

   Incorrect GROUP BY syntax -> Option B

4. Quick Check:

   GROUP BY must include BY keyword = Incorrect GROUP BY syntax [OK]

Hint: GROUP BY always needs the BY keyword [OK]

Common Mistakes:

• Writing GROUP instead of GROUP BY
• Forgetting parentheses in functions
• Assuming JSON_AGG can't be grouped

5. You want to create a JSON object for each department with an array of employee names and their roles. Which query correctly uses JSON_AGG to achieve this?

hard

A. SELECT department, JSON_BUILD_OBJECT('employees', JSON_AGG(name, role)) FROM employees GROUP BY department;

B. SELECT department, JSON_AGG(name, role) FROM employees GROUP BY department;

C. SELECT department, JSON_AGG(JSON_BUILD_OBJECT('name', name, 'role', role)) FROM employees GROUP BY department;

D. SELECT department, JSON_AGG(name || role) FROM employees GROUP BY department;

Solution

1. Step 1: Understand JSON_AGG with JSON_BUILD_OBJECT

   To create JSON objects inside an array, use JSON_BUILD_OBJECT for each row, then aggregate with JSON_AGG.

2. Step 2: Evaluate each option

   SELECT department, JSON_AGG(JSON_BUILD_OBJECT('name', name, 'role', role)) FROM employees GROUP BY department; correctly builds JSON objects per employee and aggregates them by department. Options B and C misuse JSON_AGG arguments, and D concatenates strings incorrectly.

3. Final Answer:

   SELECT department, JSON_AGG(JSON_BUILD_OBJECT('name', name, 'role', role)) FROM employees GROUP BY department; -> Option C

4. Quick Check:

   Use JSON_BUILD_OBJECT inside JSON_AGG for objects [OK]

Hint: Use JSON_BUILD_OBJECT inside JSON_AGG for arrays of objects [OK]

Common Mistakes:

• Passing multiple columns directly to JSON_AGG
• Concatenating strings instead of building JSON objects
• Misusing JSON_BUILD_OBJECT outside aggregation