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.