JSONB in PostgreSQL

-- Create table with JSONB
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  data JSONB NOT NULL
);

-- Insert JSON data
INSERT INTO events (data) VALUES
  ('{"user": "john", "action": "login", "timestamp": "2024-01-01"}'),
  ('{"user": "jane", "action": "purchase", "items": ["book", "pen"]}');

-- Query JSON fields
SELECT data->>'user' AS username       -- Get as text
FROM events
WHERE data->>'action' = 'login';

SELECT data->'items'->0                -- Get first array item
FROM events
WHERE data ? 'items';                  -- Has key

-- DETAILED_TAB:
-- Advanced JSONB operations
-- Update JSON fields
UPDATE events
SET data = jsonb_set(data, '{status}', '"completed"')
WHERE id = 1;

-- Query nested JSON
SELECT * FROM events
WHERE data @> '{"user": "john"}';      -- Contains

-- JSON aggregation
SELECT jsonb_agg(data) AS all_events
FROM events
WHERE data->>'action' = 'purchase';

-- Create index on JSON field
CREATE INDEX idx_user ON events((data->>'user'));