PostgreSQL JSON operators and functions

Need to query, filter, and transform JSON data stored in PostgreSQL JSONB columns.

Essential JSONB operations:

-- Access nested fields
SELECT data->>'name' AS name FROM users; -- text
SELECT data->'address'->>'city' AS city FROM users; -- nested text
SELECT data->'tags'->0 FROM users; -- array element

-- Filter by JSON field
SELECT * FROM users WHERE data->>'role' = 'admin';
SELECT * FROM users WHERE (data->>'age')::int > 25;

-- Check key existence
SELECT * FROM users WHERE data ? 'email'; -- has key
SELECT * FROM users WHERE data ?| array['a','b']; -- has any
SELECT * FROM users WHERE data ?& array['a','b']; -- has all

-- Containment
SELECT * FROM users WHERE data @> '{"role": "admin"}';

-- Update JSON fields
UPDATE users SET data = jsonb_set(data, '{address,city}', '"NYC"');
UPDATE users SET data = data || '{"verified": true}'::jsonb; -- merge
UPDATE users SET data = data - 'temp_field'; -- remove key

-- Aggregate to JSON
SELECT jsonb_agg(name) FROM users; -- array of names
SELECT jsonb_object_agg(id, name) FROM users; -- {id: name}

-- Expand JSON array
SELECT * FROM users, jsonb_array_elements(data->'tags') AS tag;

-- Index for fast queries
CREATE INDEX ON users USING GIN (data);
CREATE INDEX ON users ((data->>'email')); -- expression index

PostgreSQL JSONB combines the flexibility of document databases with relational database reliability and SQL querying power.