SQL
Query and Update JSONB Fields in PostgreSQL
Discover how to effectively query and update data stored within JSONB columns in PostgreSQL, crucial for flexible schema management in modern web applications.
-- Insert example data
-- INSERT INTO products (id, details) VALUES (1, '{"name": "Laptop", "specs": {"cpu": "i7", "ram": "16GB"}, "tags": ["electronics", "portable"]}');
-- INSERT INTO products (id, details) VALUES (2, '{"name": "Mouse", "specs": {"type": "wireless"}, "tags": ["accessories"]}');
-- Query products where CPU is 'i7'
SELECT
id,
details->>'name' AS product_name,
details->'specs'->>'cpu' AS cpu_spec
FROM
products
WHERE
details->'specs'->>'cpu' = 'i7';
-- Update a specific JSONB field
UPDATE
products
SET
details = jsonb_set(details, '{specs,ram}', '"32GB"', true)
WHERE
id = 1;
-- Add a new tag to the tags array
UPDATE
products
SET
details = jsonb_insert(details, '{tags,999}', '"gaming"', true)
WHERE
id = 1;
How it works: This snippet demonstrates fundamental operations on PostgreSQL's `JSONB` data type. It shows how to extract specific values from JSONB fields using the `->` (JSON object field) and `->>` (JSON object field as text) operators. The `WHERE` clause can then filter records based on these extracted values. It also illustrates how to modify JSONB data: `jsonb_set` updates or inserts a value at a specified path (creating it if not exists), and `jsonb_insert` adds an element to a JSON array or object. These functions are vital for managing semi-structured data within a relational database.