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

Need a 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;

Need a 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;

Need a 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;

Need a hint?

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