Why JSON support matters in PostgreSQL - See It in Action

Why JSON Support Matters in PostgreSQL

📖 Scenario: You are working for a small online store that wants to store product details. Some products have different attributes, so a flexible way to store data is needed.

🎯 Goal: Build a simple PostgreSQL table that uses JSON to store flexible product details. Learn how to insert and query JSON data.

📋 What You'll Learn

Create a table called products with columns id (integer) and details (JSON)

Insert three products with different JSON details

Write a query to select products where the JSON category is 'electronics'

Write a query to select products where the JSON price is less than 100

💡 Why This Matters

🌍 Real World

Many modern applications store flexible or semi-structured data. JSON support in PostgreSQL allows storing such data without rigid table schemas.

💼 Career

Understanding JSON in databases is valuable for backend developers, data engineers, and anyone working with modern data storage and APIs.

1

Create the products table with JSON column

Create a table called products with two columns: id as integer primary key and details as JSON type.

PostgreSQL

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

Need a hint?

Use CREATE TABLE with id INTEGER PRIMARY KEY and details JSON.

2

Insert products with JSON details

Insert three rows into products with id 1, 2, 3 and details JSON values: {"name": "Laptop", "category": "electronics", "price": 1200}, {"name": "Coffee Mug", "category": "kitchen", "price": 15}, and {"name": "Headphones", "category": "electronics", "price": 80}.

PostgreSQL

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  details JSON
);
-- Insert three products with JSON details
-- Your code here

Need a hint?

Use INSERT INTO products (id, details) VALUES (...) with JSON strings for details.

3

Query products in the electronics category

Write a SQL query to select all columns from products where the JSON category in details is 'electronics'. Use the ->> operator to extract the category value.

PostgreSQL

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  details JSON
);

INSERT INTO products (id, details) VALUES
(1, '{"name": "Laptop", "category": "electronics", "price": 1200}'),
(2, '{"name": "Coffee Mug", "category": "kitchen", "price": 15}'),
(3, '{"name": "Headphones", "category": "electronics", "price": 80}');

-- Select products where category is 'electronics'
-- Your code here

Need a hint?

Use details->>'category' to get the category text from JSON.

4

Query products with price less than 100

Write a SQL query to select all columns from products where the JSON price in details is less than 100. Use (details->>'price')::int to cast the price to integer for comparison.

PostgreSQL

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  details JSON
);

INSERT INTO products (id, details) VALUES
(1, '{"name": "Laptop", "category": "electronics", "price": 1200}'),
(2, '{"name": "Coffee Mug", "category": "kitchen", "price": 15}'),
(3, '{"name": "Headphones", "category": "electronics", "price": 80}');

SELECT * FROM products WHERE details->>'category' = 'electronics';

-- Select products where price is less than 100
-- Your code here

Need a hint?

Cast the JSON price string to integer using ::int for numeric comparison.