JSON aggregation with JSON_AGG in PostgreSQL - Mini Project: Build & Apply

JSON aggregation with JSON_AGG

📖 Scenario: You are managing a small online bookstore database. You want to collect all book titles by each author into a single JSON array for easy display on your website.

🎯 Goal: Create a query that groups books by author and aggregates their titles into a JSON array using JSON_AGG.

📋 What You'll Learn

Create a table called books with columns id (integer), author (text), and title (text).

Insert exactly these rows into books: (1, 'Alice Walker', 'The Color Purple'), (2, 'George Orwell', '1984'), (3, 'Alice Walker', 'Meridian'), (4, 'George Orwell', 'Animal Farm').

Write a query that selects author and a JSON array of their title values using JSON_AGG.

Group the results by author.

💡 Why This Matters

🌍 Real World

Aggregating related data into JSON arrays is useful for APIs and web applications that consume JSON data.

💼 Career

Database developers and backend engineers often use JSON aggregation to prepare data for frontend applications.

1

Create the books table and insert data

Create a table called books with columns id as integer, author as text, and title as text. Then insert these rows exactly: (1, 'Alice Walker', 'The Color Purple'), (2, 'George Orwell', '1984'), (3, 'Alice Walker', 'Meridian'), (4, 'George Orwell', 'Animal Farm').

PostgreSQL

-- Create the books table and insert the rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Write the basic SELECT query

Write a SELECT query that retrieves the author and title columns from the books table.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  author TEXT,
  title TEXT
);

INSERT INTO books (id, author, title) VALUES
  (1, 'Alice Walker', 'The Color Purple'),
  (2, 'George Orwell', '1984'),
  (3, 'Alice Walker', 'Meridian'),
  (4, 'George Orwell', 'Animal Farm');

-- Select author and title from books
SELECT author, title FROM books;

Hint

Use SELECT author, title FROM books to get the data.

3

Aggregate titles by author using JSON_AGG

Modify the SELECT query to group by author and use JSON_AGG(title) to collect all titles for each author into a JSON array. Select author and the aggregated JSON array as titles.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  author TEXT,
  title TEXT
);

INSERT INTO books (id, author, title) VALUES
  (1, 'Alice Walker', 'The Color Purple'),
  (2, 'George Orwell', '1984'),
  (3, 'Alice Walker', 'Meridian'),
  (4, 'George Orwell', 'Animal Farm');

-- Select author and JSON aggregate of titles grouped by author
SELECT author, JSON_AGG(title) AS titles
FROM books
GROUP BY author;

Hint

Use JSON_AGG(title) and GROUP BY author to group titles by author.

4

Complete the query with ordering

Add an ORDER BY author clause at the end of the query to sort the results alphabetically by author.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  author TEXT,
  title TEXT
);

INSERT INTO books (id, author, title) VALUES
  (1, 'Alice Walker', 'The Color Purple'),
  (2, 'George Orwell', '1984'),
  (3, 'Alice Walker', 'Meridian'),
  (4, 'George Orwell', 'Animal Farm');

-- Add ORDER BY author to sort results
SELECT author, JSON_AGG(title) AS titles
FROM books
GROUP BY author
ORDER BY author;

Hint

Use ORDER BY author to sort the output by author name.

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