JSONB modification functions in PostgreSQL - Mini Project: Build & Apply

Working with JSONB Modification Functions in PostgreSQL

📖 Scenario: You are managing a PostgreSQL database for a small online bookstore. Each book's details are stored in a table with a jsonb column called details. This column holds information like the author, price, and stock status in JSON format.Your task is to learn how to update and modify this JSONB data using PostgreSQL's JSONB modification functions.

🎯 Goal: Build SQL queries that modify the details JSONB column by adding, updating, and removing keys using JSONB modification functions.

📋 What You'll Learn

Create a table called books with columns id (integer) and details (jsonb).

Insert a book record with specific JSONB data into the books table.

Write a query to update the price key inside the details JSONB column using the jsonb_set function.

Write a query to remove the stock key from the details JSONB column using the - operator.

💡 Why This Matters

🌍 Real World

Many modern applications store flexible data in JSONB columns in PostgreSQL. Knowing how to modify JSONB data directly in the database helps keep data consistent and reduces application complexity.

💼 Career

Database developers and backend engineers often need to update JSONB data efficiently. This skill is useful for maintaining product catalogs, user profiles, and other semi-structured data.

1

Create the books table and insert initial JSONB data

Create a table called books with columns id as integer and details as jsonb. Then insert one row with id 1 and details containing the JSON object: {"author": "Jane Austen", "price": 20, "stock": true}.

PostgreSQL

-- Create the books table and insert one row with JSONB details
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the JSONB data as a string.

2

Set a variable for the book ID to update

Create a variable called book_id and set it to 1. This will help us target the book record to update.

PostgreSQL

CREATE TABLE books (id INTEGER, details JSONB);

INSERT INTO books (id, details) VALUES (1, '{"author": "Jane Austen", "price": 20, "stock": true}');

-- Create a variable book_id and set it to 1
-- Your code here

Need a hint?

Use the psql command \set to create a variable for the book ID.

3

Update the price key in the JSONB column using jsonb_set

Write an UPDATE query that changes the price key inside the details JSONB column to 25 for the book with id equal to :book_id. Use the jsonb_set function with the path {"price"} and the new value as a JSON string.

PostgreSQL

CREATE TABLE books (id INTEGER, details JSONB);

INSERT INTO books (id, details) VALUES (1, '{"author": "Jane Austen", "price": 20, "stock": true}');

\set book_id 1

-- Update the price key in details to 25 for the book with id = :book_id
-- Your code here

Need a hint?

Use jsonb_set(details, '{price}', '25'::jsonb) to update the price key.

4

Remove the stock key from the JSONB column using the - operator

Write an UPDATE query that removes the stock key from the details JSONB column for the book with id equal to :book_id. Use the - operator to delete the key.

PostgreSQL

CREATE TABLE books (id INTEGER, details JSONB);

INSERT INTO books (id, details) VALUES (1, '{"author": "Jane Austen", "price": 20, "stock": true}');

\set book_id 1

UPDATE books
SET details = jsonb_set(details, '{price}', '25'::jsonb)
WHERE id = :book_id;

-- Remove the stock key from details for the book with id = :book_id
-- Your code here

Need a hint?

Use details = details - 'stock' to remove the key.