-- Created on savesnippets.com · https://savesnippets.com/w0vpjKCdeX7Qy8
CREATE TABLE events (
    id          BIGSERIAL PRIMARY KEY,
    payload     JSONB NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO events (payload) VALUES
  ('{"type":"signup","user":{"id":1,"name":"Alice"},"tags":["pro","trial"]}'),
  ('{"type":"login", "user":{"id":2,"name":"Bob"},  "tags":["mobile"]}');

-- Extract fields
SELECT payload->'user'         AS user_obj,      -- jsonb
       payload->'user'->>'name' AS user_name,    -- text
       payload#>'{user,id}'    AS user_id_path   -- nested path
FROM   events;

-- Filter by content
SELECT * FROM events WHERE payload->>'type' = 'signup';
SELECT * FROM events WHERE payload @> '{"type":"login"}';
SELECT * FROM events WHERE payload->'tags' ? 'pro';      -- has key/array element

-- GIN index makes containment + key-existence queries fast
CREATE INDEX idx_events_payload_gin ON events USING gin (payload);

-- Expression index — fast filter on a specific field
CREATE INDEX idx_events_type ON events ((payload->>'type'));