JSONB containment (@>) operator in PostgreSQL - Mini Project: Build & Apply

Using JSONB Containment (@>) Operator 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 that holds various attributes like genre, author, and publication year.

🎯 Goal: Learn how to use the JSONB containment operator @> to find books that contain specific JSON attributes.

📋 What You'll Learn

Create a table called books with an id column and a details column of type JSONB.

Insert sample book data with JSONB details including genre, author, and year.

Write a query using the @> operator to find books with a specific genre.

Write a query using the @> operator to find books by a specific author.

💡 Why This Matters

🌍 Real World

Many modern applications store flexible data in JSONB columns in PostgreSQL. Using the @> operator helps quickly find records matching specific JSON criteria.

💼 Career

Understanding JSONB queries is valuable for backend developers, data engineers, and database administrators working with PostgreSQL and semi-structured data.

1

Create the books table with JSONB column

Create a table called books with two columns: id as an integer primary key and details as a JSONB type.

PostgreSQL

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

Need a hint?

Use CREATE TABLE with id SERIAL PRIMARY KEY and details JSONB.

2

Insert sample book data with JSONB details

Insert three rows into the books table with details JSONB values exactly as follows: {"genre": "fiction", "author": "Alice", "year": 2020}, {"genre": "non-fiction", "author": "Bob", "year": 2018}, and {"genre": "fiction", "author": "Charlie", "year": 2021}.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    details JSONB
);
-- Insert three rows with specified JSONB details
-- Your code here

Need a hint?

Use INSERT INTO books (details) VALUES with JSON strings in single quotes.

3

Query books with genre 'fiction' using @> operator

Write a SELECT query to get all columns from books where the details JSONB column contains the key-value pair {"genre": "fiction"} using the @> operator.

PostgreSQL

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

INSERT INTO books (details) VALUES
    ('{"genre": "fiction", "author": "Alice", "year": 2020}'),
    ('{"genre": "non-fiction", "author": "Bob", "year": 2018}'),
    ('{"genre": "fiction", "author": "Charlie", "year": 2021}');

-- Select books where details contain {"genre": "fiction"}
-- Your code here

Need a hint?

Use WHERE details @> '{"genre": "fiction"}' to filter JSONB rows.

4

Query books by author 'Bob' using @> operator

Write a SELECT query to get all columns from books where the details JSONB column contains the key-value pair {"author": "Bob"} using the @> operator.

PostgreSQL

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

INSERT INTO books (details) VALUES
    ('{"genre": "fiction", "author": "Alice", "year": 2020}'),
    ('{"genre": "non-fiction", "author": "Bob", "year": 2018}'),
    ('{"genre": "fiction", "author": "Charlie", "year": 2021}');

SELECT * FROM books WHERE details @> '{"genre": "fiction"}';

-- Select books where details contain {"author": "Bob"}
-- Your code here

Need a hint?

Use WHERE details @> '{"author": "Bob"}' to filter JSONB rows by author.