JSON and JSONB
Storing and querying semi-structured data with jsonb operators and GIN indexes
Skills taught: pg.jsonb, pg.jsonb-operators, pg.gin-index, pg.json-vs-jsonb
json vs jsonb — always use jsonb
-- json: stored as text, validated, not indexed
-- jsonb: stored as binary, can be indexed with GIN, faster to query
-- Always use jsonb unless you need to preserve key order or duplicate keys
CREATE TABLE products (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
metadata JSONB -- dynamic attributes: color, size, material, etc.
);
INSERT INTO products (name, metadata) VALUES
('T-Shirt', '{"color": "red", "size": "M", "tags": ["casual", "summer"]}'),
('Laptop', '{"brand": "ThinkPad", "ram_gb": 16, "storage": {"type": "SSD", "gb": 512}}');
JSONB operators
-- -> returns JSON (object or array)
SELECT metadata -> 'color' FROM products WHERE id = 1; -- "red" (JSON string)
-- ->> returns TEXT
SELECT metadata ->> 'color' FROM products WHERE id = 1; -- red (plain text)
-- Nested access
SELECT metadata -> 'storage' ->> 'type' FROM products WHERE id = 2; -- SSD
-- Filter by JSON value
SELECT * FROM products WHERE metadata ->> 'color' = 'red';
SELECT * FROM products WHERE (metadata ->> 'ram_gb')::INTEGER > 8;
-- @> contains (most important for indexing)
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
SELECT * FROM products WHERE metadata @> '{"tags": ["summer"]}';
-- ? key exists
SELECT * FROM products WHERE metadata ? 'brand';
-- Update a JSONB field
UPDATE products
SET metadata = metadata || '{"price": 29.99}'
WHERE id = 1;
-- jsonb_set for nested updates:
UPDATE products
SET metadata = jsonb_set(metadata, '{storage, gb}', '1024')
WHERE id = 2;
GIN index for JSONB
-- GIN index enables fast @> (contains) queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
-- This query will now use the GIN index:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
-- For specific key access (faster, smaller index)
CREATE INDEX idx_products_color ON products ((metadata ->> 'color'));
-- Supports: WHERE metadata ->> 'color' = 'red'
Frequently Asked Questions
Should I use JSONB instead of separate columns?
Use columns for structured, frequently-queried data (email, price, status). Use JSONB for dynamic/optional attributes that vary by row (product metadata, user preferences, third-party API responses). JSONB is not a replacement for proper schema design — it is a complement for when the schema needs to be flexible.