Arrow operators (-> and ->>) in PostgreSQL - Mini Project: Build & Apply

Exploring Arrow Operators (-> and ->>) in PostgreSQL JSON Data

📖 Scenario: You work at a small online bookstore. The store keeps book details in a PostgreSQL table using JSON data type to store extra information about each book.Your task is to practice extracting data from JSON columns using PostgreSQL's arrow operators -> and ->>.

🎯 Goal: Build SQL queries that extract JSON objects and JSON text values from a books table using the -> and ->> operators.

📋 What You'll Learn

Create a books table with an id column and a details column of type JSON

Insert three rows with specific JSON data for each book

Write a query using the -> operator to extract the JSON object for the author key

Write a query using the ->> operator to extract the text value of the title key

💡 Why This Matters

🌍 Real World

Many modern applications store flexible data in JSON columns inside relational databases like PostgreSQL. Knowing how to extract and query JSON data is essential for working with such databases.

💼 Career

Database developers and backend engineers often need to query JSON data efficiently. Understanding arrow operators helps in writing clear and performant SQL queries.

1

Create the books table with JSON data

Create a table called books with two columns: id as an integer primary key, and details as a JSON column.

PostgreSQL

-- Create the books table with id and details columns
-- Your code here

Need a hint?

Use SERIAL for auto-incrementing id and JSON as the data type for details.

2

Insert three rows with JSON book details

Insert three rows into books with these exact JSON details in the details column:
1. {"title": "The Hobbit", "author": {"first_name": "J.R.R.", "last_name": "Tolkien"}, "year": 1937}
2. {"title": "1984", "author": {"first_name": "George", "last_name": "Orwell"}, "year": 1949}
3. {"title": "To Kill a Mockingbird", "author": {"first_name": "Harper", "last_name": "Lee"}, "year": 1960}

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    details JSON
);
-- Insert the three rows with exact JSON details below
-- Your code here

Need a hint?

Use INSERT INTO books (details) VALUES (...), (...), (...); with JSON strings exactly as shown.

3

Query to extract the author JSON object using ->

Write a SQL query that selects id and extracts the author JSON object from the details column using the -> operator. Name the extracted column author_info.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    details JSON
);

INSERT INTO books (details) VALUES
('{"title": "The Hobbit", "author": {"first_name": "J.R.R.", "last_name": "Tolkien"}, "year": 1937}'),
('{"title": "1984", "author": {"first_name": "George", "last_name": "Orwell"}, "year": 1949}'),
('{"title": "To Kill a Mockingbird", "author": {"first_name": "Harper", "last_name": "Lee"}, "year": 1960}');

-- Select id and author JSON object using -> operator
-- Your code here

Need a hint?

Use details->'author' to get the JSON object for the author.

4

Query to extract the title text using ->>

Write a SQL query that selects id and extracts the title text value from the details column using the ->> operator. Name the extracted column book_title.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    details JSON
);

INSERT INTO books (details) VALUES
('{"title": "The Hobbit", "author": {"first_name": "J.R.R.", "last_name": "Tolkien"}, "year": 1937}'),
('{"title": "1984", "author": {"first_name": "George", "last_name": "Orwell"}, "year": 1949}'),
('{"title": "To Kill a Mockingbird", "author": {"first_name": "Harper", "last_name": "Lee"}, "year": 1960}');

SELECT id, details->'author' AS author_info FROM books;

-- Select id and title text using ->> operator
-- Your code here

Need a hint?

Use details->>'title' to get the text value of the title.