JSON / JSONB

-- 1) Schema with a jsonb column
CREATE TABLE events (
    id      bigserial PRIMARY KEY,
    type    text NOT NULL,
    payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    ts      timestamptz NOT NULL DEFAULT now()
);

-- 2) Insert
INSERT INTO events (type, payload) VALUES
    ('signup',   '{"user_id":42,"plan":"pro","referrer":"google"}'),
    ('purchase', '{"user_id":42,"order_id":1001,"total":99.95,"items":[{"sku":"A-100","qty":1}]}'),
    ('login',    '{"user_id":42,"ip":"203.0.113.7","ua":"chrome"}');

-- 3) Access — -> returns jsonb, ->> returns text
SELECT payload->'user_id'  FROM events;     -- jsonb ("42")
SELECT payload->>'user_id' FROM events;     -- text ("42")
SELECT (payload->>'user_id')::int FROM events;   -- cast

SELECT payload->'items'->0->>'sku' FROM events WHERE type = 'purchase';
-- Drill: payload -> items array -> first element -> sku text

-- 4) Filtering — equality + containment
SELECT * FROM events WHERE payload->>'user_id' = '42';
SELECT * FROM events WHERE payload @> '{"user_id":42,"plan":"pro"}'::jsonb;
-- @> is "contains" — perfect for indexed lookups

-- Existence of a key
SELECT * FROM events WHERE payload ? 'order_id';            -- exists
SELECT * FROM events WHERE payload ?| ARRAY['plan','tier']; -- any of
SELECT * FROM events WHERE payload ?& ARRAY['user_id','ip'];-- all of

-- 5) Path queries (jsonb_path_exists / jsonpath)
SELECT * FROM events
WHERE jsonb_path_exists(payload, '$.items[*] ? (@.qty > 5)');

-- 6) Extract arrays
SELECT jsonb_array_elements(payload->'items') FROM events WHERE type='purchase';

-- 7) Aggregate jsonb
SELECT jsonb_agg(payload) FROM events;
SELECT jsonb_object_agg(type, count(*)) FROM events GROUP BY type;

-- 8) Modify — concatenation + jsonb_set
UPDATE events SET payload = payload || '{"verified":true}'::jsonb WHERE id = 1;

UPDATE events SET payload = jsonb_set(payload, '{plan}', '"enterprise"') WHERE id = 1;

-- Delete a key
UPDATE events SET payload = payload - 'ip' WHERE type = 'login';

-- Delete by path
UPDATE events SET payload = payload #- '{items,0,qty}' WHERE id = 2;

-- 9) Indexes — these are the unlock
-- GIN index for fast containment + key existence
CREATE INDEX idx_events_payload ON events USING gin (payload);

-- jsonb_path_ops — smaller, faster for @> only
CREATE INDEX idx_events_payload_pathops ON events USING gin (payload jsonb_path_ops);

-- BTREE on extracted scalar — for equality / range
CREATE INDEX idx_events_user_id ON events ((payload->>'user_id'));
CREATE INDEX idx_events_total   ON events (((payload->>'total')::numeric));

-- 10) Read indexed query plan — confirm the right index gets used
EXPLAIN ANALYZE
SELECT * FROM events WHERE payload @> '{"user_id":42}'::jsonb;

-- 11) Common patterns

-- Top events per user (jsonb + window)
SELECT (payload->>'user_id')::int AS user_id,
       type, ts,
       row_number() OVER (PARTITION BY (payload->>'user_id') ORDER BY ts DESC) AS rn
FROM events;

-- Sum a jsonb numeric field
SELECT SUM((payload->>'total')::numeric)
FROM events
WHERE type = 'purchase' AND ts >= now() - interval '30 days';

-- Find events where a nested array has a matching element
SELECT * FROM events
WHERE payload @> '{"items":[{"sku":"A-100"}]}'::jsonb;

-- 12) jsonb vs json — pick jsonb (almost always)
-- jsonb : binary, sorted keys, indexed, slower writes
-- json  : raw text, preserves key order + whitespace, no indexes

-- 13) Don't put EVERYTHING in jsonb
-- Schema relational columns for fields you query frequently or join on.
-- Use jsonb for the genuinely variable / sparse data (event payloads, custom fields).

-- 14) Constraint — enforce structure on a jsonb field
ALTER TABLE events ADD CONSTRAINT events_payload_user CHECK (
    payload ? 'user_id' AND jsonb_typeof(payload->'user_id') = 'number'
);

-- Store + query JSON
UPDATE users SET prefs = '{"theme":"dark","lang":"en"}'::jsonb WHERE id = 1;
SELECT prefs->>'theme' AS theme FROM users WHERE prefs @> '{"lang":"en"}';