如何跨两表统计JSON选项对应响应数并映射至对应键？

需求说明 #

现有Questions和Responses两张表，需通过question_id关联，实现两类题型的响应处理：

• 选择题（multiple_choice）：统计每个选项的响应数，以JSON键值对格式存入mcq_response字段
• 文本输入题（text_input）：将所有响应拼接为逗号分隔字符串存入text_response字段

已完成文本输入题的SQL实现，但不清楚如何处理选择题的JSON格式响应统计，可调整数据库设计，寻求技术方案。

已实现的text_response SQL #

SELECT Questions.*, IF(Questions.question_type = "text_input", GROUP_CONCAT(Responses.question_response), null) as text_response 
FROM Questions  
LEFT JOIN Responses
ON (Questions.question_id = Responses.response_id)
group by Questions.question_id

解决方案 #

方案1：基于现有JSON格式选项（MySQL 8.0+） #

假设Questions表的mcq_choices字段存储JSON格式的选项，分两种情况处理：

情况A：mcq_choices为JSON对象（键为选项标识，值为选项文本）

例如{"opt1":"选项A", "opt2":"选项B"}，可通过JSON_KEYS拆分选项键，再关联统计：

SELECT
    q.*,
    -- 文本输入题响应拼接
    IF(q.question_type = 'text_input', GROUP_CONCAT(r.question_response), NULL) AS text_response,
    -- 选择题响应统计JSON
    IF(q.question_type = 'multiple_choice',
       JSON_OBJECTAGG(choice_key, COALESCE(COUNT(r.question_response), 0)),
       NULL) AS mcq_response
FROM Questions q
LEFT JOIN Responses r 
    ON q.question_id = r.response_id 
    AND q.question_type = 'multiple_choice'
    AND r.question_response = choice_key
-- 拆分选择题的选项键
LEFT JOIN JSON_TABLE(
    JSON_KEYS(q.mcq_choices),
    '$[*]' COLUMNS(choice_key VARCHAR(255) PATH '$')
) AS choices
    ON q.question_type = 'multiple_choice'
GROUP BY q.question_id, q.question_type, q.mcq_choices;

情况B：mcq_choices为JSON数组（仅存选项文本）

例如["选项A", "选项B"]，需用JSON_TABLE生成索引作为选项键：

SELECT
    q.*,
    IF(q.question_type = 'text_input', GROUP_CONCAT(r.question_response), NULL) AS text_response,
    IF(q.question_type = 'multiple_choice',
       JSON_OBJECTAGG(choice_idx, COALESCE(COUNT(r.question_response), 0)),
       NULL) AS mcq_response
FROM Questions q
LEFT JOIN Responses r 
    ON q.question_id = r.response_id 
    AND q.question_type = 'multiple_choice'
    AND r.question_response = choice_text
LEFT JOIN JSON_TABLE(
    q.mcq_choices,
    '$[*]' COLUMNS(
        choice_idx INT PATH '$index',
        choice_text VARCHAR(255) PATH '$'
    )
) AS choices
    ON q.question_type = 'multiple_choice'
GROUP BY q.question_id, q.question_type, q.mcq_choices;

方案2：优化数据库设计（更高效稳定） #

若JSON处理性能不足或低版本MySQL不支持JSON_TABLE，建议新增QuestionChoices表存储选择题选项：

-- 创建QuestionChoices表
CREATE TABLE QuestionChoices (
    choice_id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT NOT NULL,
    choice_key VARCHAR(255) NOT NULL, -- 选项唯一标识（如opt1）
    choice_text VARCHAR(255) NOT NULL, -- 选项显示文本
    FOREIGN KEY (question_id) REFERENCES Questions(question_id)
);

此时统计SQL更简洁，且性能更优：

SELECT
    q.*,
    -- 文本输入题响应拼接
    IF(q.question_type = 'text_input', GROUP_CONCAT(r.question_response), NULL) AS text_response,
    -- 选择题响应统计JSON
    IF(q.question_type = 'multiple_choice',
       (SELECT JSON_OBJECTAGG(c.choice_key, COALESCE(rc.response_count, 0))
        FROM QuestionChoices c
        LEFT JOIN (
            SELECT response_id, question_response, COUNT(*) AS response_count
            FROM Responses
            GROUP BY response_id, question_response
        ) rc 
            ON c.question_id = rc.response_id 
            AND c.choice_key = rc.question_response
        WHERE c.question_id = q.question_id),
       NULL) AS mcq_response
FROM Questions q
LEFT JOIN Responses r ON q.question_id = r.response_id
GROUP BY q.question_id;

关键注意点 #

• 确保Responses表的question_response存储选择题的选项标识（如opt1）而非文本，避免文本不一致导致统计错误
• 使用COALESCE处理无响应的选项，保证JSON包含所有选择题选项（即使响应数为0）
• 若使用低版本MySQL（<8.0），可自定义函数拆分JSON数组/对象，或直接采用方案2的表结构优化

内容的提问来源于stack exchange，提问作者rexorsist