MariaDB 10.2.14中JSON列数据提取优化方案问询

嘿，我来给你几个更优雅的解决方案，适配你用的MariaDB 10.2.14版本——这个版本已经支持不少实用的JSON函数了，完全不用再依赖容易出错的正则表达式硬抠数据！

方案1：用JSON_TABLE拆分JSON数组（最清晰健壮） #

这个方法直接把JSON数组拆成结构化的行数据，然后通过关联筛选和匹配，逻辑一目了然，完全基于JSON结构处理，不怕字符串格式变化：

WITH JT1 AS (
    SELECT 1 AS ID , '[{"name":"x","score":2},{"name":"y", "score":8},{"name":"z","score":3}]' AS REPLY UNION ALL
    SELECT 2 AS ID , '[{"name":"x","score":5},{"name":"y", "score":4},{"name":"z","score":3}]' AS REPLY UNION ALL
    SELECT 3 AS ID , '[{"name":"x","score":2},{"name":"y", "score":2},{"name":"z","score":6}]' AS REPLY UNION ALL
    SELECT 4 AS ID , '[{"name":"x","score":5},{"name":"y", "score":8},{"name":"z","score":6}]' AS REPLY
)
SELECT 
    jt1.ID,
    y_entry->>'$.score' AS y_score
FROM JT1
-- 先筛选出x的score等于5的条目，同时记录它在数组中的位置（idx）
JOIN JSON_TABLE(
    JT1.REPLY,
    '$[*]' COLUMNS(
        name VARCHAR(10) PATH '$.name',
        score INT PATH '$.score',
        idx FOR ORDINALITY -- 生成数组元素的序号
    )
) x_entries ON x_entries.name = 'x' AND x_entries.score = 5
-- 通过相同的序号找到同个数组里的y条目
JOIN JSON_TABLE(
    JT1.REPLY,
    '$[*]' COLUMNS(
        name VARCHAR(10) PATH '$.name',
        score INT PATH '$.score',
        idx FOR ORDINALITY
    )
) y_entries ON y_entries.idx = x_entries.idx AND y_entries.name = 'y';

方案2：用JSON_CONTAINS+JSON_SEARCH快速定位（更简洁） #

如果你的JSON数组里x和y的位置不固定，但只需要简单匹配提取，这个方案更紧凑：

WITH JT1 AS (
    SELECT 1 AS ID , '[{"name":"x","score":2},{"name":"y", "score":8},{"name":"z","score":3}]' AS REPLY UNION ALL
    SELECT 2 AS ID , '[{"name":"x","score":5},{"name":"y", "score":4},{"name":"z","score":3}]' AS REPLY UNION ALL
    SELECT 3 AS ID , '[{"name":"x","score":2},{"name":"y", "score":2},{"name":"z","score":6}]' AS REPLY UNION ALL
    SELECT 4 AS ID , '[{"name":"x","score":5},{"name":"y", "score":8},{"name":"z","score":6}]' AS REPLY
)
SELECT 
    ID,
    -- 提取y的score：先找到y的路径，再拼接成提取表达式
    JSON_UNQUOTE(JSON_EXTRACT(REPLY, CONCAT(JSON_SEARCH(REPLY, 'one', 'y'), '.score'))) AS y_score
FROM JT1
-- 直接判断数组中是否包含{"name":"x","score":5}这个元素
WHERE JSON_CONTAINS(REPLY, '{"name":"x","score":5}');

为什么这两个方案比正则好？ #

• 健壮性：正则依赖字符串的格式细节（比如空格、引号位置），一旦JSON的格式化方式变化（比如多了个空格），正则就会失效；而JSON函数是解析JSON的结构，不受这些细节影响。
• 可读性：谁看都能明白是在处理JSON里的x和y元素，不用费劲猜正则的匹配规则。
• 可维护性：如果后续要提取z的score，或者修改匹配条件，调整JSON函数比改正则简单太多。

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