Postgres JSONB Extraction

Project typed columns out of a JSONB payload and into a clean view. Lineage tracks every typed column back to the JSONB source.

The lineage

The SQL

CREATE TABLE webhook_events (
  event_id BIGSERIAL PRIMARY KEY,
  payload JSONB,
  received_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE VIEW v_stripe_events AS
SELECT
  event_id,
  payload->>'id'                 AS stripe_event_id,
  payload->>'type'               AS event_type,
  (payload->'data'->'object'->>'amount')::int AS amount_cents,
  (payload->'data'->'object'->>'customer')    AS customer_id,
  received_at
FROM webhook_events
WHERE payload->>'type' LIKE 'invoice.%';

What the lineage shows

• Every projected column traces back to webhook_events.payload.
• Casting JSONB to int does not break lineage — it's still a derived column.
• Useful for auditing event-shape drift on a single source column.