PostgreSQL JSONB: Flexible Schema, Indexing, and When to Use It

CREATE TABLE events (
    id      bigserial PRIMARY KEY,
    payload jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO events (payload) VALUES
  ('{"type": "order.created", "user": {"id": 42, "email": "alice@example.com"}, "items": [{"sku": "A1", "qty": 2}]}'),
  ('{"type": "order.shipped", "user": {"id": 42, "email": "alice@example.com"}, "tracking": "1Z999"}');

-- Get the event type as jsonb (quoted string in output)
SELECT payload -> 'type' FROM events;

-- Get the event type as plain text
SELECT payload ->> 'type' FROM events;

-- Navigate nested path: user.email
SELECT payload #>> '{user, email}' FROM events;

-- Filter by nested value
SELECT * FROM events
WHERE payload ->> 'type' = 'order.created'
  AND (payload #>> '{user, id}')::int = 42;

-- Find all events where the user id is 42
SELECT * FROM events
WHERE payload @> '{"user": {"id": 42}}';

-- Find events of specific types
SELECT * FROM events
WHERE payload @> '{"type": "order.created"}';

-- Find events that have a tracking field at all
SELECT * FROM events
WHERE payload ? 'tracking';

-- Find events where type is one of a set
SELECT * FROM events
WHERE payload ->> 'type' = ANY(ARRAY['order.created', 'order.shipped']);

-- Default GIN with jsonb_ops: supports @>, ?, ?|, ?&
CREATE INDEX idx_events_payload_gin ON events USING gin (payload);

-- GIN with jsonb_path_ops: smaller index, only supports @>
-- Better when you only need containment queries
CREATE INDEX idx_events_payload_path_ops ON events USING gin (payload jsonb_path_ops);

-- B-tree index on a specific extracted value (for equality + range on one field)
CREATE INDEX idx_events_type ON events ((payload ->> 'type'));
CREATE INDEX idx_events_user_id ON events (((payload #>> '{user, id}')::int));

-- jsonb_set(target, path, new_value, create_missing)
-- Update user.email in place
UPDATE events
SET payload = jsonb_set(payload, '{user, email}', '"bob@example.com"', false)
WHERE id = 1;

-- jsonb_insert(target, path, new_value, insert_after)
-- Insert a new element into the items array at position 0
UPDATE events
SET payload = jsonb_insert(payload, '{items, 0}', '{"sku": "B2", "qty": 1}', false)
WHERE id = 1;

-- Merge / shallow merge with the || operator
UPDATE events
SET payload = payload || '{"status": "processed", "processed_at": "2025-01-01"}'
WHERE id = 1;

-- Remove a key with the - operator
UPDATE events
SET payload = payload - 'tracking'
WHERE id = 2;

-- Remove a nested path with the #- operator
UPDATE events
SET payload = payload #- '{user, email}'
WHERE id = 1;

-- Expand a JSONB array into rows
SELECT
  e.id,
  item ->> 'sku'      AS sku,
  (item ->> 'qty')::int AS qty
FROM events e,
  jsonb_array_elements(e.payload -> 'items') AS item
WHERE e.payload @> '{"type": "order.created"}';

-- Expand all key-value pairs of a JSONB object into rows
SELECT key, value
FROM events,
  jsonb_each(payload)
WHERE id = 1;

CREATE TABLE domain_events (
    id          bigserial PRIMARY KEY,
    aggregate   text        NOT NULL,
    agg_id      bigint      NOT NULL,
    event_type  text        NOT NULL,          -- extracted for indexing
    version     int         NOT NULL,
    payload     jsonb       NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- Partial index: find all shipment events cheaply
CREATE INDEX idx_domain_events_shipment
  ON domain_events (agg_id, version)
  WHERE event_type = 'order.shipped';

-- GIN for attribute queries inside payload
CREATE INDEX idx_domain_events_payload
  ON domain_events USING gin (payload jsonb_path_ops);

-- Replay all events for a specific order
SELECT event_type, version, payload
FROM domain_events
WHERE aggregate = 'order' AND agg_id = 42
ORDER BY version;

-- Find all orders shipped to a specific address
SELECT agg_id, payload
FROM domain_events
WHERE event_type = 'order.shipped'
  AND payload @> '{"address": {"city": "Milan"}}';