MySQL中使用JSON_EXTRACT提取JSON列数据遇问题求助

问题核心 #

你的JSON结构键为纯数字，MySQL的JSON路径语法对这类键有特殊要求；同时你需要将提取结果的部分字符串修正为首字母大写格式。

修正后的SQL代码 #

SELECT
    qq.extradata AS 'column 0',
    -- 处理数字键提取+首字母大写
    CONCAT(
        UPPER(SUBSTRING(JSON_UNQUOTE(JSON_EXTRACT(qq.extradata, '$."1"')), 1, 1)),
        SUBSTRING(JSON_UNQUOTE(JSON_EXTRACT(qq.extradata, '$."1"')), 2)
    ) AS 'column 1',
    JSON_UNQUOTE(JSON_EXTRACT(qq.extradata, '$."2"')) AS 'column 2',
    JSON_UNQUOTE(JSON_EXTRACT(qq.extradata, '$."3"')) AS 'column 3',
    JSON_UNQUOTE(JSON_EXTRACT(qq.extradata, '$."4"')) AS 'column 4',
    CONCAT(
        UPPER(SUBSTRING(JSON_UNQUOTE(JSON_EXTRACT(qq.extradata, '$."5"')), 1, 1)),
        SUBSTRING(JSON_UNQUOTE(JSON_EXTRACT(qq.extradata, '$."5"')), 2)
    ) AS 'column 5'
FROM questionnaire_question AS qq

关键说明 #

1. 数字键路径写法：当JSON键是纯数字时，必须用双引号包裹键名，写成$."1"而非$.1，MySQL才能正确匹配对应键值。
2. 去除引号：JSON_EXTRACT返回结果自带双引号，JSON_UNQUOTE可去除引号，得到纯文本内容。
3. 首字母大写处理：针对需要修正大小写的内容，用UPPER(SUBSTRING(...,1,1))提取首字母转大写，再拼接剩余字符串部分。

如果你的MySQL版本≥5.7.13，可使用简写语法->>替代JSON_UNQUOTE(JSON_EXTRACT(...))，代码更简洁：

SELECT
    qq.extradata AS 'column 0',
    CONCAT(UPPER(SUBSTRING(qq.extradata->>"$."1"", 1, 1)), SUBSTRING(qq.extradata->>"$."1"", 2)) AS 'column 1',
    qq.extradata->>"$."2"" AS 'column 2',
    qq.extradata->>"$."3"" AS 'column 3',
    qq.extradata->>"$."4"" AS 'column 4',
    CONCAT(UPPER(SUBSTRING(qq.extradata->>"$."5"", 1, 1)), SUBSTRING(qq.extradata->>"$."5"", 2)) AS 'column 5'
FROM questionnaire_question AS qq

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