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

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

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

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

Hint

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

Practice

1. Why is JSON support important in PostgreSQL?

easy

A. It makes PostgreSQL faster than all other databases.

B. It allows storing flexible and varying data structures in one column.

C. It replaces the need for any relational tables.

D. It automatically converts JSON to XML format.

Solution

1. Step 1: Understand JSON flexibility

   JSON allows storing data that can change structure without altering the table schema.

2. Step 2: Compare with relational tables

   Relational tables require fixed columns, but JSON columns can hold different keys and values per row.

3. Final Answer:

   It allows storing flexible and varying data structures in one column. -> Option B

4. Quick Check:

   JSON = flexible data storage [OK]

Hint: JSON stores flexible data in one column easily [OK]

Common Mistakes:

• Thinking JSON makes PostgreSQL faster than all others
• Believing JSON replaces relational tables completely
• Confusing JSON with XML conversion

2. Which of the following is the correct way to declare a JSON column in a PostgreSQL table?

easy

A. CREATE TABLE data (info XML);

B. CREATE TABLE data (info TEXT[]);

C. CREATE TABLE data (info VARCHAR);

D. CREATE TABLE data (info JSON);

Solution

1. Step 1: Identify JSON data type syntax

   PostgreSQL uses the JSON or JSONB data type to store JSON data.

2. Step 2: Check each option

   Only CREATE TABLE data (info JSON); uses JSON type correctly; others use unrelated types.

3. Final Answer:

   CREATE TABLE data (info JSON); -> Option D

4. Quick Check:

   JSON column = JSON type [OK]

Hint: Use JSON or JSONB type for JSON columns [OK]

Common Mistakes:

• Using TEXT[] or VARCHAR for JSON data
• Confusing JSON with XML data type
• Missing JSON keyword in column definition

3. Given the table users with a JSON column profile, what does this query return?

SELECT profile->>'name' FROM users WHERE profile->>'age' = '30';

medium

A. An error because ->> cannot be used in WHERE.

B. All JSON profiles where age is 30.

C. Names of users whose age is 30 as text values.

D. Age values of users named '30'.

Solution

1. Step 1: Understand JSON operators

   The operator ->> extracts JSON object field as text. Here, profile->>'age' gets age as text.

2. Step 2: Analyze query logic

   The WHERE clause filters rows where age equals '30'. The SELECT returns the name field as text for those rows.

3. Final Answer:

   Names of users whose age is 30 as text values. -> Option C

4. Quick Check:

   ->> extracts text field = names for age 30 [OK]

Hint: ->> extracts text; WHERE filters JSON text fields [OK]

Common Mistakes:

• Thinking ->> cannot be used in WHERE clause
• Expecting full JSON objects instead of text
• Confusing age and name fields

4. What is wrong with this query that tries to filter JSON data?

SELECT * FROM orders WHERE details->'price' = 100;

medium

A. Using -> returns JSON, so comparison with integer 100 fails.

B. The table name 'orders' is invalid in PostgreSQL.

C. The operator -> cannot be used in WHERE clause.

D. JSON data cannot be filtered in PostgreSQL.

Solution

1. Step 1: Understand JSON operators and types

   The operator -> returns JSON type, not a plain value, so comparing it directly to integer 100 causes a type mismatch.

2. Step 2: Correct usage for comparison

   Use ->> to extract text and then cast to integer for numeric comparison.

3. Final Answer:

   Using -> returns JSON, so comparison with integer 100 fails. -> Option A

4. Quick Check:

   -> returns JSON, not scalar, so comparison fails [OK]

Hint: Use ->> to get text before comparing JSON values [OK]

Common Mistakes:

• Comparing JSON object directly to integer
• Assuming -> works like ->>
• Believing JSON cannot be filtered in WHERE

5. You have a table events with a JSONB column data storing event details. You want to find all events where the nested JSON key location.city is 'Paris'. Which query correctly achieves this?

hard

A. SELECT * FROM events WHERE data->'location'->>'city' = 'Paris';

B. SELECT * FROM events WHERE data->>'location.city' = 'Paris';

C. SELECT * FROM events WHERE data->'location.city' = 'Paris';

D. SELECT * FROM events WHERE data->'location'->'city' = 'Paris';

Solution

1. Step 1: Access nested JSON keys correctly

   To get nested keys, chain -> for JSON objects and ->> for text extraction. Here, data->'location' gets the location object, then ->>'city' extracts city as text.

2. Step 2: Check each option

   data->>'location.city' treats 'location.city' as a single key. data->'location.city' does the same with ->. data->'location'->'city' returns JSON for 'city', causing type mismatch when compared to 'Paris'. Only data->'location'->>'city' extracts text correctly.

3. Final Answer:

   SELECT * FROM events WHERE data->'location'->>'city' = 'Paris'; -> Option A

4. Quick Check:

   Nested keys need chained -> and ->> [OK]

Hint: Chain -> for objects, ->> for text extraction in JSON [OK]

Common Mistakes:

• Using dot notation inside ->> operator
• Comparing JSON object directly to string
• Not chaining operators for nested keys