JSON Functions

-- 1) Schema with a JSON column
CREATE TABLE events (
    id      BIGINT AUTO_INCREMENT PRIMARY KEY,
    type    VARCHAR(50) NOT NULL,
    payload JSON NOT NULL,
    ts      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 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"}');

-- 3) Access — JSON_EXTRACT / -> / ->>
SELECT JSON_EXTRACT(payload, '$.user_id') FROM events;   -- json type
SELECT payload->'$.user_id'              FROM events;     -- same
SELECT payload->>'$.user_id'             FROM events;     -- text (unquoted)

-- Cast as needed
SELECT (payload->>'$.user_id') + 0 AS user_id FROM events;
SELECT CAST(payload->>'$.user_id' AS UNSIGNED) AS user_id FROM events;

-- 4) Drill into arrays + objects
SELECT payload->>'$.items[0].sku' FROM events WHERE type = 'purchase';
SELECT payload->>'$.address.city' FROM events;

-- 5) Filter rows by JSON
SELECT * FROM events WHERE payload->>'$.user_id' = '42';
SELECT * FROM events WHERE JSON_CONTAINS(payload->'$.tags', '"featured"');
SELECT * FROM events WHERE JSON_CONTAINS_PATH(payload, 'one', '$.items', '$.order_id');

-- 6) JSON_SEARCH / JSON_KEYS / JSON_LENGTH
SELECT JSON_KEYS(payload) FROM events WHERE type = 'signup';
SELECT JSON_LENGTH(payload, '$.items') FROM events WHERE type = 'purchase';
SELECT JSON_SEARCH(payload, 'one', 'A-100');

-- 7) Modify — JSON_SET / JSON_REPLACE / JSON_REMOVE
UPDATE events SET payload = JSON_SET(payload, '$.verified', true) WHERE id = 1;
UPDATE events SET payload = JSON_REPLACE(payload, '$.plan', 'enterprise') WHERE id = 1;
UPDATE events SET payload = JSON_REMOVE(payload, '$.ip') WHERE type = 'login';
UPDATE events SET payload = JSON_ARRAY_APPEND(payload, '$.tags', 'priority') WHERE id = 2;

-- 8) Build JSON
SELECT JSON_OBJECT(
    'id',    id,
    'type',  type,
    'user',  payload->>'$.user_id'
) FROM events;

SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'type', type)) FROM events;
SELECT JSON_OBJECTAGG(type, total) FROM (
    SELECT type, COUNT(*) AS total FROM events GROUP BY type
) t;

-- 9) Aggregate from a JSON array — JSON_TABLE (MySQL 8+)
SELECT id, item.sku, item.qty
FROM events,
JSON_TABLE(
    payload->'$.items',
    '$[*]' COLUMNS (
        sku VARCHAR(40) PATH '$.sku',
        qty INT          PATH '$.qty'
    )
) AS item
WHERE type = 'purchase';

-- 10) Indexes — generated columns + index
-- Direct index on JSON_EXTRACT isn't allowed; create a generated column.
ALTER TABLE events
    ADD COLUMN user_id INT AS (CAST(payload->>'$.user_id' AS UNSIGNED)) STORED,
    ADD INDEX idx_events_user_id (user_id);

SELECT * FROM events WHERE user_id = 42;   -- uses the index

-- 11) Functional indexes (MySQL 8.0.13+)
ALTER TABLE events ADD INDEX idx_events_total ((CAST(payload->>'$.total' AS DECIMAL(10,2))));

SELECT * FROM events WHERE CAST(payload->>'$.total' AS DECIMAL(10,2)) > 50;

-- 12) Validation — JSON_SCHEMA_VALID (MySQL 8.0.17+)
ALTER TABLE events ADD CONSTRAINT events_payload_chk
    CHECK (JSON_SCHEMA_VALID('{
        "type": "object",
        "required": ["user_id"],
        "properties": {
            "user_id": { "type": "integer" }
        }
    }', payload));

-- 13) Common patterns

-- Group by JSON value
SELECT payload->>'$.plan' AS plan, COUNT(*) c
FROM events
WHERE type = 'signup'
GROUP BY plan;

-- Sum from JSON
SELECT SUM(CAST(payload->>'$.total' AS DECIMAL(10,2)))
FROM events
WHERE type = 'purchase' AND ts >= NOW() - INTERVAL 30 DAY;

-- 14) Performance + best practices
--   • Don't store EVERYTHING as JSON — relational columns are still better for filters / joins
--   • Add a generated column + index for any field you frequently filter on
--   • Use JSON_TABLE for analytical workloads against arrays
--   • Validate shape with JSON_SCHEMA_VALID — catches bad inputs at write time
--   • Keep documents small — large JSON values inflate row size + BLOB I/O
--   • For heavy document workloads, consider Postgres jsonb or MongoDB

-- 15) JSON type vs TEXT/VARCHAR
-- JSON validates syntax, supports paths, indexes via generated columns.
-- TEXT/VARCHAR + manual parsing — only when you don't need server-side queries.

-- 16) Comparison to Postgres
-- MySQL JSON  = JSON (binary), good for hot paths with generated columns
-- PostgreSQL jsonb = richer (GIN indexes, more operators, JSONPath built-in)

UPDATE users
SET prefs = JSON_OBJECT('theme', 'dark', 'lang', 'en')
WHERE id = 1;
SELECT JSON_EXTRACT(prefs, '$.theme') AS theme FROM users;