Use MySQL JSON Column Type for Flexible Metadata in WordPress Plugin Tables

MySQL 5.7.8 introduced a native JSON column type that stores JSON documents in a binary format optimized for efficient access — unlike storing JSON as a VARCHAR or TEXT column, the native JSON type validates the document on insert (rejecting malformed JSON), supports JSON-specific functions and operators for querying and modifying individual fields without deserializing the entire document in PHP, and allows defining generated columns or virtual indexes on specific JSON paths so that WHERE clauses on JSON field values can use an index rather than a full table scan. For WordPress plugins that need to store variable-shape metadata — survey responses with different fields per question type, product configuration options that vary by product category, or user preference objects that grow over time — a JSON column in a custom plugin table is a cleaner alternative to the WordPress post meta pattern (which stores one key-value pair per row, requiring many JOIN operations) and more flexible than a fixed-column table schema. The JSON path syntax uses -> (extract as JSON) and ->> (extract as plain text, equivalent to JSON_UNQUOTE(JSON_EXTRACT(...))): column->'$.key' extracts a value from a JSON object, column->'$.array[0]' extracts the first element of a JSON array. Modification functions: JSON_SET() updates or inserts a field, JSON_REMOVE() deletes a field, JSON_MERGE_PATCH() performs an RFC 7396 merge (nulls remove fields). Querying: JSON_CONTAINS() checks if a value exists anywhere in the JSON, JSON_SEARCH() returns the path to a matching value. Virtual generated columns enable indexing JSON paths: ALTER TABLE t ADD COLUMN extracted_field VARCHAR(100) GENERATED ALWAYS AS (data->>'$.field') VIRTUAL, ADD INDEX (extracted_field). The foreign key constraints post ensured referential integrity in plugin tables; JSON columns add flexible schema capability to those same tables.

Problem: A WordPress quiz plugin stores survey responses in a post meta table — each response has a different set of fields depending on the question type (multiple choice, text, rating, file upload), resulting in 50+ post meta rows per response and queries that require 8 JOINs to reconstruct a single response object, taking 200ms each.

Solution: Create a custom wp_quiz_responses table with a JSON column for the variable-shape response data — one row per response, with JSON path queries and a generated index on the score field for fast leaderboard queries.

-- Create the responses table with a JSON column
CREATE TABLE wp_quiz_responses (
    id           BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
    quiz_id      BIGINT UNSIGNED  NOT NULL,
    user_id      BIGINT UNSIGNED  DEFAULT NULL,
    session_id   VARCHAR(64)      DEFAULT NULL,
    submitted_at DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    data         JSON             NOT NULL,            -- variable-shape response data

    -- Generated virtual column to enable fast indexing on the score field
    score        DECIMAL(6,2)     GENERATED ALWAYS AS (data->>'$.score') VIRTUAL,

    PRIMARY KEY (id),
    KEY idx_quiz_id    (quiz_id),
    KEY idx_user_id    (user_id),
    KEY idx_score      (score),                        -- index on generated column
    KEY idx_submitted  (submitted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Example JSON structure stored in the 'data' column:
-- {
--   "score": 85.5,
--   "duration_seconds": 142,
--   "answers": {
--     "q1": { "type": "multiple_choice", "selected": "B", "correct": true },
--     "q2": { "type": "text", "value": "WordPress is a CMS", "score": 3.5 },
--     "q3": { "type": "rating", "value": 4 }
--   },
--   "metadata": { "device": "mobile", "browser": "Chrome 118" }
-- }

-- Insert a response
INSERT INTO wp_quiz_responses (quiz_id, user_id, data) VALUES (
    42, 3,
    JSON_OBJECT(
        'score',            85.5,
        'duration_seconds', 142,
        'answers', JSON_OBJECT(
            'q1', JSON_OBJECT('type', 'multiple_choice', 'selected', 'B', 'correct', TRUE),
            'q2', JSON_OBJECT('type', 'text', 'value', 'WordPress is a CMS', 'score', 3.5)
        ),
        'metadata', JSON_OBJECT('device', 'mobile')
    )
);

-- Query using JSON path — find all responses with score >= 80
SELECT id, user_id, data->>'$.score' AS score, submitted_at
FROM   wp_quiz_responses
WHERE  quiz_id = 42
  AND  score >= 80              -- uses the generated column index
ORDER BY score DESC
LIMIT  10;

-- Extract a specific answer field
SELECT user_id,
       data->>'$.answers.q1.selected' AS q1_answer,
       data->>'$.answers.q1.correct'  AS q1_correct
FROM   wp_quiz_responses
WHERE  quiz_id = 42;

-- Update a single JSON field without deserializing the whole document
UPDATE wp_quiz_responses
SET    data = JSON_SET(data, '$.reviewed', TRUE, '$.reviewer_id', 5)
WHERE  id = 1001;

-- Find responses where the user answered q1 incorrectly
SELECT COUNT(*) AS wrong_on_q1
FROM   wp_quiz_responses
WHERE  quiz_id = 42
  AND  data->>'$.answers.q1.correct' = 'false';

-- Aggregate: average score per quiz
SELECT quiz_id,
       AVG( data->>'$.score' ) AS avg_score,
       COUNT(*) AS total_responses
FROM   wp_quiz_responses
GROUP BY quiz_id;

NOTE: MySQL JSON columns are not a replacement for proper relational schema design — use JSON columns for genuinely variable-shape data where the set of fields is not known at table creation time, not as a workaround for avoiding schema migrations. Data that is always queried by value (e.g., status, category, user_id) belongs in a dedicated indexed column, not embedded in JSON. Querying inside JSON arrays with JSON_CONTAINS() cannot use an index efficiently — if you need to search for values inside a JSON array, consider a separate join table instead. Also, $wpdb does not have a JSON-specific escape method — use wp_json_encode() to serialize the PHP array before passing it to $wpdb->prepare() with a %s placeholder.