JSON aggregation with JSON_AGG in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the PostgreSQL function JSON_AGG do?

JSON_AGG collects multiple rows into a single JSON array. It helps group data as JSON objects inside one array.

beginner

How do you use JSON_AGG to aggregate rows from a table?

You select the columns you want as JSON objects and wrap them inside JSON_AGG. For example: SELECT JSON_AGG(row_to_json(t)) FROM (SELECT * FROM table_name) t;

intermediate

Can JSON_AGG be combined with GROUP BY?

Yes. You can group rows by a column and aggregate related rows as JSON arrays for each group.

intermediate

What is the difference between JSON_AGG and ARRAY_AGG?

JSON_AGG returns a JSON array, which can contain JSON objects. ARRAY_AGG returns a SQL array of values, not JSON formatted.

beginner

Why use row_to_json inside JSON_AGG?

row_to_json converts a table row into a JSON object. Using it inside JSON_AGG creates an array of JSON objects instead of simple values.

What type of data does JSON_AGG return?

A A JSON array

B A SQL array

C A JSON object

D A text string

JSON_AGG returns a JSON array containing aggregated JSON values.

Which function is commonly used inside JSON_AGG to convert rows to JSON objects?

A JSON_BUILD_OBJECT

B TO_JSON

C ROW_TO_JSON

D JSON_OBJECT_AGG

ROW_TO_JSON converts a whole row into a JSON object, perfect for use inside JSON_AGG.

Can JSON_AGG be used with GROUP BY to create grouped JSON arrays?

A Only with <code>ORDER BY</code>

B Yes

C No

D Only with <code>HAVING</code>

JSON_AGG works well with GROUP BY to aggregate rows per group into JSON arrays.

What does this query return?
SELECT JSON_AGG(name) FROM users;

A A JSON array of user names

B A JSON object of user names

C A SQL array of user names

D A single user name

This query returns a JSON array containing all user names from the users table.

Which is true about JSON_AGG compared to ARRAY_AGG?

A <code>JSON_AGG</code> returns SQL arrays, <code>ARRAY_AGG</code> returns JSON arrays

B Both return SQL arrays

C Both return JSON arrays

D <code>JSON_AGG</code> returns JSON arrays, <code>ARRAY_AGG</code> returns SQL arrays

JSON_AGG returns JSON formatted arrays, while ARRAY_AGG returns SQL native arrays.

Explain how to use JSON_AGG to create a JSON array of objects from a table.

Think about how to turn each row into JSON and then collect them.

Describe a scenario where combining GROUP BY with JSON_AGG is useful.

Imagine grouping orders by customer and returning JSON arrays of orders.

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