PostgreSQL JSONB: Schema flessibile, indicizzazione e quando usarlo

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"}');

-- Ottieni il tipo di evento come jsonb (stringa con virgolette nell'output)
SELECT payload -> 'type' FROM events;

-- Ottieni il tipo di evento come testo semplice
SELECT payload ->> 'type' FROM events;

-- Naviga il path annidato: user.email
SELECT payload #>> '{user, email}' FROM events;

-- Filtra per valore annidato
SELECT * FROM events
WHERE payload ->> 'type' = 'order.created'
  AND (payload #>> '{user, id}')::int = 42;

-- Trova tutti gli eventi dove lo user id è 42
SELECT * FROM events
WHERE payload @> '{"user": {"id": 42}}';

-- Trova eventi di tipi specifici
SELECT * FROM events
WHERE payload @> '{"type": "order.created"}';

-- Trova eventi che hanno un campo tracking
SELECT * FROM events
WHERE payload ? 'tracking';

-- Trova eventi dove il tipo è uno di un insieme
SELECT * FROM events
WHERE payload ->> 'type' = ANY(ARRAY['order.created', 'order.shipped']);

-- GIN predefinito con jsonb_ops: supporta @>, ?, ?|, ?&
CREATE INDEX idx_events_payload_gin ON events USING gin (payload);

-- GIN con jsonb_path_ops: indice più piccolo, supporta solo @>
-- Meglio quando si hanno solo query di contenimento
CREATE INDEX idx_events_payload_path_ops ON events USING gin (payload jsonb_path_ops);

-- Indice B-tree su un valore estratto specifico (per uguaglianza + range su un campo)
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)
-- Aggiorna user.email in loco
UPDATE events
SET payload = jsonb_set(payload, '{user, email}', '"bob@example.com"', false)
WHERE id = 1;

-- jsonb_insert(target, path, new_value, insert_after)
-- Inserisce un nuovo elemento nell'array items alla posizione 0
UPDATE events
SET payload = jsonb_insert(payload, '{items, 0}', '{"sku": "B2", "qty": 1}', false)
WHERE id = 1;

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

-- Rimuovi una chiave con l'operatore -
UPDATE events
SET payload = payload - 'tracking'
WHERE id = 2;

-- Rimuovi un path annidato con l'operatore #-
UPDATE events
SET payload = payload #- '{user, email}'
WHERE id = 1;

-- Espandi un array JSONB in righe
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"}';

-- Espandi tutte le coppie chiave-valore di un oggetto JSONB in righe
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,          -- estratto per indicizzazione
    version     int         NOT NULL,
    payload     jsonb       NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- Indice parziale: trova tutti gli eventi di spedizione a basso costo
CREATE INDEX idx_domain_events_shipment
  ON domain_events (agg_id, version)
  WHERE event_type = 'order.shipped';

-- GIN per query sugli attributi nel payload
CREATE INDEX idx_domain_events_payload
  ON domain_events USING gin (payload jsonb_path_ops);

-- Riproduci tutti gli eventi per un ordine specifico
SELECT event_type, version, payload
FROM domain_events
WHERE aggregate = 'order' AND agg_id = 42
ORDER BY version;

-- Trova tutti gli ordini spediti a una citta specifica
SELECT agg_id, payload
FROM domain_events
WHERE event_type = 'order.shipped'
  AND payload @> '{"address": {"city": "Milan"}}';