JSON aggregation with JSON_AGG in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to aggregate all employee names into a JSON array using JSON_AGG.

PostgreSQL

SELECT JSON_AGG([1]) AS employee_names FROM employees;

Drag options to blanks, or click blank then click option'

Aname

Bsalary

Cdepartment

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Using a numeric column like salary instead of name.

Forgetting to use JSON_AGG and just selecting the column.

The JSON_AGG function collects values into a JSON array. Here, we want to aggregate the name column.

2fill in blank

medium

Complete the code to aggregate employee names grouped by their department.

PostgreSQL

SELECT department, JSON_AGG([1]) AS employees FROM employees GROUP BY department;

Drag options to blanks, or click blank then click option'

Adepartment

Bname

Csalary

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Aggregating the department column instead of names.

Not grouping by department.

We want to collect employee names per department, so name is the correct column to aggregate.

3fill in blank

hard

Fix the error in the code to aggregate employee names as JSON objects with their salaries.

PostgreSQL

SELECT JSON_AGG(JSON_BUILD_OBJECT('name', name, 'salary', [1])) AS employee_info FROM employees;

Drag options to blanks, or click blank then click option'

Adepartment

Bid

Cname

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column like department or id for salary.

Swapping keys and values.

The JSON_BUILD_OBJECT needs the salary value for the 'salary' key, so the correct column is salary.

4fill in blank

hard

Fill both blanks to aggregate employee names and salaries grouped by department as JSON arrays.

PostgreSQL

SELECT department, JSON_AGG(JSON_BUILD_OBJECT('name', [1], 'salary', [2])) AS employees FROM employees GROUP BY department;

Drag options to blanks, or click blank then click option'

Aname

Bsalary

Cdepartment

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping name and salary columns.

Using department or id instead of name or salary.

The first blank is the employee name, the second is salary. Grouping should be by department, but the blank for grouping is not present here, so only fill the first two blanks.

5fill in blank

hard

Fill all three blanks to create a JSON object with department as key and aggregated employee names as value.

PostgreSQL

SELECT JSON_OBJECT_AGG([1], [2]) AS department_employees FROM (SELECT [3], JSON_AGG(name) AS names FROM employees GROUP BY [3]) sub;

Drag options to blanks, or click blank then click option'

Adepartment

Bnames

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Using salary instead of department as key.

Using wrong alias for aggregated names.

The JSON_OBJECT_AGG needs the department as key and the aggregated names as value. The subquery groups by department.

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