JSONB Operations

Code

-- Insert JSONB
INSERT INTO events (data)
VALUES ('{"type":"login","user":{"id":1,"name":"Alice"},"ip":"10.0.0.1"}');

-- Extract fields
SELECT data->'user'->>'name' AS name, data->>'ip' AS ip
FROM events
WHERE data->>'type' = 'login';

-- Containment and existence operators
SELECT * FROM events WHERE data @> '{"type":"login"}';
SELECT * FROM events WHERE data ? 'ip';

-- Update a nested field
UPDATE events
SET data = jsonb_set(data, '{user,name}', '"Bob"')
WHERE id = 1;

-- GIN index for fast JSONB queries
CREATE INDEX idx_events_data ON events USING gin (data);

Explanation

JSONB stores JSON in a binary format that supports indexing and efficient containment checks. The -> operator returns JSONB while ->> returns text, ideal for projections. A GIN index accelerates @>, ?, and other operators, making JSONB a viable semi-structured document store inside Postgres.

More PostgreSQL Snippets