PostgreSQL JSON/JSONB querying and indexing

Need to efficiently query, filter, and index JSONB columns in PostgreSQL.

JSONB operations and indexing:

-- Sample table
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  data JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- INSERT
INSERT INTO events (data) VALUES
  ('{"type": "click", "page": "/home", "user": {"id": 1, "name": "Alice"}}'),
  ('{"type": "view", "page": "/about", "tags": ["nav", "info"]}');

-- ACCESS OPERATORS
SELECT
  data->>'type' AS type,           -- text value (->>' for text)
  data->'user'->>'name' AS user,   -- nested access
  data#>>'{user,name}' AS user2,   -- path access (text)
  data->'tags'->0 AS first_tag,    -- array index (jsonb)
  data->'tags'->>0 AS first_tag_text -- array index (text)
FROM events;

-- FILTER
SELECT * FROM events WHERE data->>'type' = 'click';
SELECT * FROM events WHERE data->'user'->>'id' = '1';
SELECT * FROM events WHERE (data->>'count')::int > 5;

-- CONTAINMENT (@>)
SELECT * FROM events WHERE data @> '{"type": "click"}';
SELECT * FROM events WHERE data @> '{"user": {"id": 1}}';

-- EXISTENCE (?)
SELECT * FROM events WHERE data ? 'tags';       -- has key
SELECT * FROM events WHERE data ?| ARRAY['type','page']; -- has any
SELECT * FROM events WHERE data ?& ARRAY['type','page']; -- has all

-- ARRAY OPERATIONS
SELECT * FROM events WHERE data->'tags' ? 'nav';  -- array contains

-- INDEXING

-- GIN index for containment and existence operators
CREATE INDEX idx_events_data ON events USING GIN (data);
-- Supports: @>, ?, ?|, ?&

-- GIN with jsonb_path_ops (smaller, faster for @> only)
CREATE INDEX idx_events_data_path ON events
  USING GIN (data jsonb_path_ops);
-- Only supports @>, but 2-3x faster

-- B-tree index on specific JSON field
CREATE INDEX idx_events_type ON events ((data->>'type'));
-- For: WHERE data->>'type' = 'click'

-- JSONB_EACH: Expand to rows
SELECT key, value FROM events, jsonb_each(data) WHERE id = 1;

-- JSONB_ARRAY_ELEMENTS: Expand array
SELECT elem FROM events, jsonb_array_elements(data->'tags') AS elem;

-- UPDATE specific field
UPDATE events SET data = jsonb_set(data, '{user,name}', '"Bob"');

-- REMOVE key
UPDATE events SET data = data - 'temporary_field';

JSONB combines schema flexibility with query performance. Proper indexing (GIN for containment, B-tree for specific fields) makes JSONB queries fast even on large tables.

PostgreSQL with semi-structured data