MySQL 5.7如何从JSON列中提取所有数组形式的答题记录？

MySQL 5.7 不支持 JSON_TABLE，但可以通过数字辅助表+JSON函数的方式实现展开JSON数组所有元素的需求，具体方案如下：

核心思路 #

生成一组连续的数字序列（对应JSON数组的索引，从0开始），将其与原表关联，通过动态拼接JSON路径的方式，逐个提取数组中每个元素的字段。

具体SQL实现 #

1. 基础版（提取所有答题记录） #

SELECT
    sp.user_uuid,
    sp.stub,
    -- 转换毫秒级时间戳为可读格式
    FROM_UNIXTIME(JSON_EXTRACT(sp.`value`, CONCAT('$.quiz_scores[', nums.idx, '].quiz_date')) / 1000) AS quiz_date,
    JSON_EXTRACT(sp.`value`, CONCAT('$.quiz_scores[', nums.idx, '].account_age_in_days')) AS account_age_in_days,
    JSON_EXTRACT(sp.`value`, CONCAT('$.quiz_scores[', nums.idx, '].symptom_improvement_score')) AS symptom_improvement_score,
    JSON_EXTRACT(sp.`value`, CONCAT('$.quiz_scores[', nums.idx, '].coping_skill_acquisition_score')) AS coping_skill_acquisition_score,
    JSON_EXTRACT(sp.`value`, CONCAT('$.quiz_scores[', nums.idx, '].increased_self_awareness_score')) AS increased_self_awareness_score
FROM
    series_progress sp
-- 关联数字辅助表，生成0到100的索引（可根据数组最大长度调整）
JOIN (
    SELECT (a.N + b.N * 10 + c.N * 100) AS idx
    FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
    (SELECT 0 AS N UNION ALL SELECT 1) c
) nums 
-- 确保索引不超过数组实际长度
ON nums.idx < JSON_LENGTH(sp.`value`->>'$.quiz_scores')
WHERE
    sp.stub = 'questionnaire_my_progress';

2. 带筛选条件的版本（匹配原需求的三个score为true、account_age>1） #

SELECT
    sp.user_uuid,
    sp.stub,
    FROM_UNIXTIME((sp.`value`->>CONCAT('$.quiz_scores[', nums.idx, '].quiz_date')) / 1000) AS quiz_date,
    sp.`value`->>CONCAT('$.quiz_scores[', nums.idx, '].account_age_in_days') AS account_age_in_days,
    sp.`value`->>CONCAT('$.quiz_scores[', nums.idx, '].symptom_improvement_score') AS symptom_improvement_score,
    sp.`value`->>CONCAT('$.quiz_scores[', nums.idx, '].coping_skill_acquisition_score') AS coping_skill_acquisition_score,
    sp.`value`->>CONCAT('$.quiz_scores[', nums.idx, '].increased_self_awareness_score') AS increased_self_awareness_score
FROM
    series_progress sp
JOIN (
    SELECT (a.N + b.N * 10 + c.N * 100) AS idx
    FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
    (SELECT 0 AS N UNION ALL SELECT 1) c
) nums 
ON nums.idx < JSON_LENGTH(sp.`value`->>'$.quiz_scores')
WHERE
    sp.stub = 'questionnaire_my_progress'
    -- 注意：使用->>提取布尔值时返回字符串，需与'true'比较
    AND (sp.`value`->>CONCAT('$.quiz_scores[', nums.idx, '].symptom_improvement_score')) = 'true'
    AND (sp.`value`->>CONCAT('$.quiz_scores[', nums.idx, '].coping_skill_acquisition_score')) = 'true'
    AND (sp.`value`->>CONCAT('$.quiz_scores[', nums.idx, '].increased_self_awareness_score')) = 'true'
    AND (sp.`value`->>CONCAT('$.quiz_scores[', nums.idx, '].account_age_in_days')) > 1;

注意事项 #

• 数字辅助表范围：示例生成了0到100的索引，若quiz_scores数组可能超过100条，可扩展数字生成逻辑（比如增加d表扩大范围）。
• 类型匹配：->>运算符提取布尔值时返回字符串格式（'true'/'false'），筛选时需注意类型一致。
• 性能优化：数据量较大时，建议预先创建物理数字表（存储0到足够大的数字），避免每次查询生成临时序列。

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