Inserting JSON data in PostgreSQL - Mini Project: Build & Apply

Inserting JSON Data into PostgreSQL

📖 Scenario: You are working on a small online bookstore database. You want to store book details in a table using JSON format to keep the data flexible and easy to update.

🎯 Goal: Create a table to store book information as JSON data, then insert a JSON record representing a book into the table.

📋 What You'll Learn

Create a table named books with a column info of type jsonb

Insert a JSON record into the books table with specific book details

Use exact JSON keys and values as specified

💡 Why This Matters

🌍 Real World

Storing flexible and complex data like book details in JSON format allows easy updates and schema changes without altering the database structure.

💼 Career

Many modern applications use JSON data types in databases like PostgreSQL to handle semi-structured data efficiently, a valuable skill for backend developers and database administrators.

1

Create the books table with a jsonb column

Write a SQL statement to create a table called books with one column named info of type jsonb.

PostgreSQL

-- Write the CREATE TABLE statement here
-- Your code here

Need a hint?

Use CREATE TABLE books (info jsonb); to create the table.

2

Prepare the JSON data for insertion

Create a variable called book_json that holds the JSON data as a string with these exact key-value pairs: {"title": "The Great Gatsby", "author": "F. Scott Fitzgerald", "year": 1925}.

PostgreSQL

CREATE TABLE books (info jsonb);
-- Define the JSON string variable book_json here
-- Your code here

Need a hint?

Use \set book_json '{"title": "The Great Gatsby", "author": "F. Scott Fitzgerald", "year": 1925}' to define the JSON string variable.

3

Insert the JSON data into the books table

Write an INSERT statement to add the JSON data stored in book_json into the info column of the books table. Use the ::jsonb cast to convert the string to JSONB.

PostgreSQL

CREATE TABLE books (info jsonb);
\set book_json '{"title": "The Great Gatsby", "author": "F. Scott Fitzgerald", "year": 1925}'
-- Insert the JSON data into books table here
-- Your code here

Need a hint?

Use INSERT INTO books (info) VALUES (:book_json::jsonb); to insert the JSON data.

4

Verify the JSON data insertion

Write a SELECT statement to retrieve all rows from the books table to confirm the JSON data was inserted correctly.

PostgreSQL

CREATE TABLE books (info jsonb);
\set book_json '{"title": "The Great Gatsby", "author": "F. Scott Fitzgerald", "year": 1925}'
INSERT INTO books (info) VALUES (:book_json::jsonb);
-- Select all rows from books table here
-- Your code here

Need a hint?

Use SELECT * FROM books; to see the inserted JSON data.