JSON + PostgreSQL

Laboratory Activity
Working with JSONB in PostgreSQL

Student Name:

Course / Section:

Date:

Laboratory No.: JSON-PG-01

Laboratory Objectives
By the end of this activity, students will be able to:
1. Use -> and ->> operators to read fields from a JSONB column
2. Filter rows by JSONB field values in WHERE clauses
3. Navigate nested JSON objects using chained operators
4. Search JSON arrays using the @> containment operator
5. Update specific JSONB fields using jsonb_set()
6. Aggregate numeric values stored inside JSONB
7. Insert new rows containing valid JSONB data

Database Setup
Run the following SQL to create and populate the table before starting the exercises:

CREATE TABLE products (

id SERIAL PRIMARY KEY,
name TEXT,
details JSONB
);

INSERT INTO products (name, details) VALUES

('Bluetooth Speaker', '{
"price": 29.99, "category": "electronics",
"tags": ["sale","wireless"], "stock": 120,
"specs": {"brand": "TechCo", "color": "black"}
}'),
('SQL Mastery Book', '{
"price": 9.50, "category": "books",
"tags": ["bestseller"], "stock": 0,
"specs": {"brand": "PubHouse", "color": null}
}'),

JSON + PostgreSQL Laboratory Activity | Page 1

 ('Noise-Cancel Headphones', '{
"price": 149.00, "category": "electronics",
"tags": ["premium","wireless"], "stock": 45,
"specs": {"brand": "TechCo", "color": "silver"}
'}');

Operator Quick Reference

Operator Returns Use when
-> JSONB Navigating to a nested key or getting array elements by
index
->> TEXT Extracting a scalar value for display or WHERE
comparisons
@> BOOLEAN Checking if a JSON array or object contains a specific
value
jsonb_set() JSONB Updating one key without overwriting the entire JSONB
object
::numeric / number Casting text values for arithmetic and aggregation
::int functions

EXERCISE 1 — CONCEPT

Reading JSON fields with -> and ->>

Objective: Use the ->> operator to extract a field value as text from a JSONB column.

Key Concept
-> returns a JSON value (strings include quotes). ->> returns plain TEXT — use it for display and
comparisons.

Example SQL:
-- Extract 'category' as text from each product
SELECT id, name, details->>'category' AS category
FROM products;

Your Task
Write a SELECT query that returns id, name, and the price field (as text) from all products.
Expected result: 3 rows with columns id, name, and price.

JSON + PostgreSQL Laboratory Activity | Page 2

Write your SQL here:

Query output / result:

JSON + PostgreSQL Laboratory Activity | Page 3

 EXERCISE 2 — QUERY

Filtering with JSON values in WHERE

Objective: Filter rows using a JSONB field value in a WHERE clause.

Key Concept
Use ->> in a WHERE clause just like a regular column. Because ->> returns TEXT, compare with
string literals.

Example SQL:
-- Find all products in the 'electronics' category
SELECT id, name, details->>'price' AS price
FROM products
WHERE details->>'category' = 'electronics';

Your Task
Write a query that returns products where stock > 0. Cast stock to integer: (details->>'stock')::int > 0
Expected result: 2 rows — the two products with stock > 0.

Write your SQL here:

Query output / result:

JSON + PostgreSQL Laboratory Activity | Page 4

 EXERCISE 3 — QUERY

Navigating nested JSON objects

Objective: Use chained operators to access values inside nested JSON objects.

Key Concept
Chain -> to navigate into nested objects. Use ->> only for the final (leaf) field to get text.
Pattern: column -> 'outer_key' ->> 'inner_key'

Example SQL:
-- Get brand and color from the nested 'specs' object
SELECT name,
details -> 'specs' ->> 'brand' AS brand,
details -> 'specs' ->> 'color' AS color
FROM products;

Your Task
Write a query that returns name and brand, but only for products made by 'TechCo'.
Expected result: 2 rows — Bluetooth Speaker and Noise-Cancel Headphones.

Write your SQL here:

Query output / result:

JSON + PostgreSQL Laboratory Activity | Page 5

 EXERCISE 4 — QUERY

Searching JSON arrays with @>

Objective: Use the containment operator @> to find rows where a JSON array includes a
specific value.

Key Concept
@> checks if the left JSONB contains the right JSONB.
To check if an array contains 'wireless': details->'tags' @> '["wireless"]'
Both sides of @> must be JSONB — use -> (not ->>) for the left side.

Example SQL:
-- Find products tagged as 'wireless'
SELECT name, details->>'category' AS category
FROM products
WHERE details->'tags' @> '["wireless"]';

Your Task
Write a query to find all products tagged as 'sale'. Return name and price.
Expected result: 1 row — Bluetooth Speaker.

Write your SQL here:

Query output / result:

JSON + PostgreSQL Laboratory Activity | Page 6

 EXERCISE 5 — UPDATE

Updating JSONB with jsonb_set()

Objective: Update a specific field inside a JSONB column without overwriting the entire object.

Key Concept
jsonb_set(column, '{path}', 'new_value') updates one key surgically.
For nested paths use: '{specs,color}'. New string values need extra quotes: '"text"'

Example SQL:
-- Set the stock of the book (id=2) to 50
UPDATE products
SET details = jsonb_set(details, '{stock}', '50')
WHERE id = 2;

-- Verify the change

SELECT name, details->>'stock' AS stock FROM products WHERE id = 2;

Your Task
Write an UPDATE that changes the color of product id=1 (inside specs) to 'red'.
UPDATE 1. The [Link] for id=1 should now be 'red'.

Write your SQL here:

Query output / result:

JSON + PostgreSQL Laboratory Activity | Page 7

 EXERCISE 6 — AGGREGATE

Aggregating JSON numeric values

Objective: Cast JSONB text values to numbers to use aggregate functions like AVG and SUM.

Key Concept
->> always returns TEXT. Cast before aggregating: (details->>'price')::numeric or (details-
>>'stock')::int
Then use standard SQL functions: AVG(), SUM(), MAX(), MIN(), COUNT()

Example SQL:
-- Average price and total stock across all products
SELECT
ROUND(AVG((details->>'price')::numeric), 2) AS avg_price,
SUM((details->>'stock')::int) AS total_stock
FROM products;

Your Task
Write a query that groups products by category and returns: category, count of products, and max
price per category.
Expected result: 2 rows — electronics (2 products) and books (1 product).

Write your SQL here:

Query output / result:

JSON + PostgreSQL Laboratory Activity | Page 8

 EXERCISE 7 — INSERT

Inserting rows with JSONB

Objective: Insert a new product row with a valid JSONB value in the details column.

Key Concept
Pass JSONB as a JSON string literal in single quotes.
The JSON must be valid: double-quoted keys, no trailing commas.
PostgreSQL casts the string to JSONB automatically when the column type is JSONB.

Example SQL:
-- Insert a new clothing product
INSERT INTO products (name, details)
VALUES (
'Classic T-Shirt',
'{"price": 19.99, "category": "clothing",
"tags": ["sale", "new"],
"stock": 200,
"specs": {"brand": "FashionCo", "color": "blue"}}'
);

Your Task
Write your own INSERT for a new product of any category. Include: price, category, tags (array),
stock, and specs (nested object with brand and color).
INSERT 0 1. Validate with: SELECT * FROM products WHERE id = 4;

Write your SQL here:

Query output / result:

JSON + PostgreSQL Laboratory Activity | Page 9

Reflection Questions

1. What is the difference between -> and ->> in PostgreSQL? When would you choose
one over the other?

2. Why must you cast JSONB values with ::numeric or ::int before using aggregate
functions like AVG() or SUM()?

3. What are the advantages of storing data as JSONB instead of separate relational
columns? What are the potential drawbacks?

4. Describe a real-world scenario where using JSONB in PostgreSQL would be a better

design choice than a fully normalized schema.

JSON + PostgreSQL Laboratory Activity | Page 10

JSON + PostgreSQL Laboratory Activity | Page 11
Grading Rubric

Exercise 1 — Reading fields 10

Exercise 2 — Filtering with WHERE 10

Exercise 3 — Nested JSON 15

Exercise 4 — Array search with @> 15

Exercise 5 — Update with jsonb_set() 15

Exercise 6 — Aggregation 15

Exercise 7 — INSERT with JSONB 10

Reflection Questions (4 x 2.5 pts) 10

TOTAL 100

JSON + PostgreSQL Laboratory Activity | Page 12