SQL
Querying and Extracting Data from JSON/JSONB Columns (PostgreSQL)
Learn to query JSON or JSONB columns in PostgreSQL, extracting specific values and filtering records based on nested JSON data. Essential for modern web applications.
-- Sample table with a JSONB column
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
details JSONB
);
-- Insert sample data
INSERT INTO products (name, details) VALUES
('Laptop Pro', '{"brand": "TechCorp", "specs": {"cpu": "i7", "ram_gb": 16, "storage_gb": 512}, "available": true, "tags": ["electronics", "premium"]}'),
('Desk Monitor', '{"brand": "DisplayCo", "specs": {"resolution": "4K", "size_inches": 27}, "available": true, "tags": ["electronics"]}'),
('External SSD', '{"brand": "SpeedyDrive", "specs": {"capacity_gb": 1000}, "available": false, "tags": ["storage", "portable"]}');
-- Query products where brand is 'TechCorp'
SELECT id, name, details->>'brand' AS brand
FROM products
WHERE details->>'brand' = 'TechCorp';
-- Query products with more than 500GB storage
SELECT id, name, details->'specs'->>'storage_gb' AS storage
FROM products
WHERE (details->'specs'->>'storage_gb')::INT > 500;
-- Query products tagged as 'premium'
SELECT id, name, details->'tags' AS tags
FROM products
WHERE details->'tags' ? 'premium';
How it works: This snippet demonstrates how to interact with `JSONB` columns in PostgreSQL. It shows how to extract string values using `->>` (for text) and `->` (for JSON object/array), filter records based on nested JSON properties, and check for the presence of elements within a JSON array using the `?` operator. This is crucial for applications storing flexible, semi-structured data.