JSON aggregation with JSON_AGG in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this JSON_AGG query?

Given a table employees with columns id, name, and department, what is the output of the following query?

SELECT department, JSON_AGG(name ORDER BY name) AS employee_names FROM employees GROUP BY department ORDER BY department;

PostgreSQL

CREATE TABLE employees (id INT, name TEXT, department TEXT);
INSERT INTO employees VALUES
(1, 'Alice', 'HR'),
(2, 'Bob', 'IT'),
(3, 'Charlie', 'HR'),
(4, 'David', 'IT'),
(5, 'Eve', 'Finance');

SELECT department, JSON_AGG(name ORDER BY name) AS employee_names FROM employees GROUP BY department ORDER BY department;

A [{"department":"Finance","employee_names":["Eve"]},{"department":"HR","employee_names":["Alice","Charlie"]},{"department":"IT","employee_names":["Bob","David"]}]

B [{"department":"Finance","employee_names":["Eve"]},{"department":"HR","employee_names":["Charlie","Alice"]},{"department":"IT","employee_names":["David","Bob"]}]

C [{"department":"Finance","employee_names":["Eve"]},{"department":"HR","employee_names":["Alice","Charlie"]},{"department":"IT","employee_names":["David","Bob"]}]

D [{"department":"Finance","employee_names":["Eve"]},{"department":"HR","employee_names":["Charlie","Alice"]},{"department":"IT","employee_names":["Bob","David"]}]

💡 Hint

Remember that JSON_AGG with ORDER BY sorts the aggregated values inside the array.

The query groups employees by department and aggregates their names into JSON arrays sorted alphabetically. So HR has ["Alice", "Charlie"] and IT has ["Bob", "David"].

🧠 Conceptual

intermediate

What does JSON_AGG do in PostgreSQL?

Choose the best description of what the JSON_AGG function does in PostgreSQL.

A Aggregates JSON objects into a single JSON object by merging keys.

B Aggregates multiple rows into a single JSON array.

C Converts a JSON array into multiple rows.

D Converts a JSON string into a JSON object.

💡 Hint

Think about how aggregation functions combine multiple rows.

JSON_AGG collects values from multiple rows and returns them as a JSON array.

📝 Syntax

advanced

Which query correctly aggregates JSON objects per department?

You want to aggregate employee data as JSON objects per department with keys id and name. Which query is correct?

PostgreSQL

Table employees(id INT, name TEXT, department TEXT)

A SELECT department, JSON_AGG(JSON_BUILD_OBJECT('id', id, 'name', name)) AS employees FROM employees GROUP BY department;

B SELECT department, JSON_AGG(ROW(id, name)) AS employees FROM employees GROUP BY department;

C SELECT department, JSON_OBJECT_AGG(id, name) AS employees FROM employees GROUP BY department;

D SELECT department, JSON_AGG({'id': id, 'name': name}) AS employees FROM employees GROUP BY department;

💡 Hint

Remember JSON_BUILD_OBJECT creates JSON objects from key-value pairs.

Option A uses JSON_BUILD_OBJECT to create JSON objects per row, then aggregates them into a JSON array per department. Option A aggregates SQL ROW types, which is not JSON. Option A aggregates key-value pairs but requires unique keys and returns a JSON object, not array. Option A uses invalid syntax for JSON object creation.

🔧 Debug

advanced

Why does this JSON_AGG query raise an error?

Consider this query:

SELECT department, JSON_AGG(name ORDER BY salary) FROM employees GROUP BY department;

Assuming salary is a column in employees, why does this query raise an error?

A Because ORDER BY inside JSON_AGG requires a subquery.

B Because JSON_AGG does not support ORDER BY inside its parentheses.

C Because <code>name</code> is not a valid column in employees.

D Because <code>salary</code> is not in the GROUP BY clause or an aggregate function.

💡 Hint

Check what columns are allowed in SELECT when using GROUP BY.

The error occurs because salary is used inside ORDER BY in JSON_AGG but is neither grouped nor aggregated, violating SQL rules.

❓ optimization

expert

How to optimize JSON_AGG for large datasets?

You have a large sales table with millions of rows. You want to aggregate sales data per region into JSON arrays using JSON_AGG. Which approach is best to optimize performance?

A Use a CROSS JOIN to combine all regions and sales before aggregation.

B Use JSON_AGG without any filtering or indexing to get all data at once.

C Use a subquery to pre-filter rows by date before applying JSON_AGG.

D Use JSON_AGG inside a window function partitioned by region.

💡 Hint

Filtering data early reduces the amount of data JSON_AGG processes.

Pre-filtering with a subquery reduces rows before aggregation, improving performance. CROSS JOIN and window functions here increase data volume or complexity. No filtering causes unnecessary processing.