JSON Support in MySQL

-- Create table with JSON
CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  data JSON NOT NULL
);

-- Insert JSON
INSERT INTO events (data) VALUES
  ('{"user": "john", "action": "login"}'),
  (JSON_OBJECT('user', 'jane', 'action', 'purchase'));

-- Query JSON
SELECT data->>'$.user' AS username
FROM events
WHERE data->>'$.action' = 'login';

-- Update JSON field
UPDATE events
SET data = JSON_SET(data, '$.status', 'processed')
WHERE id = 1;

-- DETAILED_TAB:
-- JSON functions
SELECT 
  JSON_EXTRACT(data, '$.user') AS user,
  JSON_TYPE(data),
  JSON_VALID(data),
  JSON_LENGTH(data),
  JSON_KEYS(data)
FROM events;

-- JSON array operations
SELECT data->>'$.items[0]' AS first_item
FROM events
WHERE JSON_CONTAINS(data, '"book"', '$.items');

-- Create generated column from JSON
ALTER TABLE events
ADD COLUMN username VARCHAR(255) 
  GENERATED ALWAYS AS (data->>'$.user') STORED,
ADD INDEX idx_username(username);

-- JSON aggregation
SELECT JSON_ARRAYAGG(data) AS all_events
FROM events;