JSONB existence (?) operator in PostgreSQL - Mini Project: Build & Apply

Using the JSONB Existence (?) Operator in PostgreSQL

📖 Scenario: You are managing a PostgreSQL database for an online bookstore. Each book's details are stored in a table with a JSONB column that holds various attributes like author, genre, and publication year.

🎯 Goal: Build a query that finds all books where a specific key exists in the JSONB column using the ? operator.

📋 What You'll Learn

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

Insert three rows with specific JSONB data for the details column.

Write a query that selects all rows where the JSONB column details contains the key 'author' using the ? operator.

Add a query that selects all rows where the JSONB column details contains the key 'publisher' using the ? operator.

💡 Why This Matters

🌍 Real World

Many modern applications store flexible data in JSONB columns in PostgreSQL. Checking if certain keys exist helps filter and analyze this data efficiently.

💼 Career

Understanding JSONB and its operators is valuable for database developers and backend engineers working with PostgreSQL in real-world projects.

1

Create the books table with JSONB column

Write a SQL statement to 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 INTEGER PRIMARY KEY and details JSONB.

2

Insert three rows with JSONB data into books

Insert three rows into the books table with these exact values: (1, '{"author": "Alice", "genre": "Fiction"}'), (2, '{"author": "Bob", "year": 2020}'), and (3, '{"genre": "Non-fiction", "publisher": "XYZ Press"}').

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    details JSONB
);
-- Insert the three rows below
-- Your code here

Need a hint?

Use INSERT INTO books (id, details) VALUES with the exact JSONB strings.

3

Query books where JSONB column has key 'author'

Write a SQL query to select all columns from books where the details JSONB column contains the key 'author' using the ? operator.

PostgreSQL

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

INSERT INTO books (id, details) VALUES
(1, '{"author": "Alice", "genre": "Fiction"}'),
(2, '{"author": "Bob", "year": 2020}'),
(3, '{"genre": "Non-fiction", "publisher": "XYZ Press"}');

-- Select rows where details has key 'author'
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE details ? 'author' to find rows with the key.

4

Query books where JSONB column has key 'publisher'

Write a SQL query to select all columns from books where the details JSONB column contains the key 'publisher' using the ? operator.

PostgreSQL

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

INSERT INTO books (id, details) VALUES
(1, '{"author": "Alice", "genre": "Fiction"}'),
(2, '{"author": "Bob", "year": 2020}'),
(3, '{"genre": "Non-fiction", "publisher": "XYZ Press"}');

SELECT * FROM books WHERE details ? 'author';

-- Select rows where details has key 'publisher'
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE details ? 'publisher' to find rows with the key.