JSON and JSONB in PostgreSQL

# CHAPTER 23

JSON and JSONB in PostgreSQL #

1. Introduction #

2. Learning Objectives #

• Understand the difference between JSON and JSONB data types.

• Insert JSON payloads into a table.

• Extract specific keys and values from deeply nested JSON objects.

• Query tables by filtering against internal JSON values.

• Understand how to index JSONB for lightning-fast lookups.

3. JSON vs JSONB #

• JSON: Stores the exact string of text you send it. It is fast to insert, but incredibly slow to query because Postgres has to re-parse the text every time you read it.

• JSONB (JSON Binary): When you insert data, Postgres parses the JSON, strips out whitespace, and converts it into a highly optimized binary format on the hard drive. It is slightly slower to insert, but lightning fast to query and index.

*The Golden Rule: Always use JSONB in production applications. Never use standard JSON.*

4. Creating a JSONB Table #

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    preferences JSONB -- The Magic Column!
);

5. Inserting JSONB Data #

INSERT INTO users (email, preferences) 
VALUES (
    'john@example.com', 
    '{"theme": "dark", "notifications": {"email": true, "sms": false}, "role": "admin"}'
);

6. Querying JSONB (The Extraction Operators) #

• -> Returns the data as a JSON object.

• ->> Returns the data extracted as raw Text (This is the one you will use 99% of the time).

-- Extract the 'theme' value for all users
SELECT email, preferences->>'theme' AS ui_theme 
FROM users;
-- Output: john@example.com | dark

-- Traverse deeply nested JSON! (Get the SMS notification preference)
SELECT email, preferences->'notifications'->>'sms' AS wants_sms 
FROM users;

7. Filtering via JSONB inside the WHERE Clause #

-- Find ALL users who have dark mode enabled
SELECT email FROM users 
WHERE preferences->>'theme' = 'dark';

-- Find ALL users who are administrators
SELECT email FROM users 
WHERE preferences->>'role' = 'admin';

8. Mini Project: The E-Commerce Payload #

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    total NUMERIC,
    metadata JSONB
);

-- Insert a complex order
INSERT INTO orders (total, metadata) VALUES (
    150.00, 
    '{"gift_wrap": true, "message": "Happy Birthday!", "tags": ["urgent", "vip"]}'
);

-- Query: Find all orders tagged as 'urgent' using the JSONB Contains Operator (@>)
SELECT order_id FROM orders 
WHERE metadata @> '{"tags": ["urgent"]}';

9. Common Mistakes #

• Overusing JSONB: If you have data that is structured and relational (like firstname or dateofbirth), DO NOT put it in a JSONB column! JSONB is for unstructured, variable data. Relational columns are significantly smaller, faster, and benefit from strict NOT NULL and CHECK constraints.

SELECT product_name 
FROM products 
WHERE attributes->>'color' = 'red';

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the difference between the -> and ->> JSON extraction operators in PostgreSQL.

• Q: Describe an architectural scenario where storing data in a JSONB column is vastly superior to normalizing that data into a strict, traditional relational table format.

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #