MySQL 8中从JSON字段对象数组提取数据的技术问询

原SQL返回NULL的原因 #

你的原查询存在两个关键问题：

• JSON路径错误：scores是JSON数组，直接用->"$.type"会尝试读取数组顶层的type键，而数组本身没有这个键，所以返回NULL。需要遍历数组内的对象才能获取对应字段。
• 条件字段名错误：WHERE子句里的poliste并非JSON对象中的字段，你实际要匹配的是JSON对象里的id字段。

使用JSON_TABLE实现需求（推荐） #

JSON_TABLE可以将JSON数组转换为关系型数据表，方便后续过滤、查询和生成结构化结果。以下是满足你需求的示例SQL：

SELECT 
    t.json_id,
    t.tr,
    t.tt,
    t.type,
    t.week,
    adresse.id AS table_id,
    adresse.game_at
FROM adresse
JOIN JSON_TABLE(
    adresse.scores,
    '$[*]' COLUMNS(
        json_id INT PATH '$.id',
        tr VARCHAR(255) PATH '$.tr',
        tt VARCHAR(255) PATH '$.tt',
        type VARCHAR(255) PATH '$.type',
        week INT PATH '$.week'
    )
) AS t
WHERE t.json_id = 67 AND t.type = 'alpha';

说明： #

• JSON_TABLE(adresse.scores, '$[*]' ...)：将scores数组中的每个对象拆分为独立的行数据。
• COLUMNS子句定义要提取的JSON字段及对应数据类型，PATH指定字段在JSON对象中的路径。
• 通过JOIN关联原表与JSON生成的临时表，再通过WHERE子句过滤出json_id=67且type='alpha'的记录，最终得到结构化结果。

关于SUBSTRING_INDEX的局限性 #

SUBSTRING_INDEX仅适合处理简单字符串分割，并不适配JSON结构（尤其是嵌套或复杂数组）。如果硬要用它处理固定格式的JSON数组，仅能在极端场景下生效，维护性差且易出错，不推荐用于JSON数据提取。

以下是仅作演示的示例（不建议在实际场景使用）：

SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(scores, '"id":', -1), ',', 1) AS json_id,
    SUBSTRING_INDEX(SUBSTRING_INDEX(scores, '"type":', -1), '"', 2) AS type
FROM adresse
WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(scores, '"id":', -1), ',', 1) = '67'
AND SUBSTRING_INDEX(SUBSTRING_INDEX(scores, '"type":', -1), '"', 2) = '"alpha"';

这种方式依赖JSON字符串格式完全固定，一旦字段顺序、空格或格式发生变化，查询就会失效。

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