标准SQL中如何使用JSON_EXTRACT提取JSON数组内的指定字段值

失效原因 #

• 你存储的目标内容第一层是数组结构，原有代码的JSON路径$.name是直接从根节点匹配name字段，和实际结构不匹配，所以无法返回结果。
• 如果你的列中实际存储的是多段独立JSON数组用空格拼接的非标准格式（即你给出的示例[{"name": "my_name_1"}] [{"name": "my_name_2"}]...为真实存储内容），整段内容不属于合法JSON结构，也会导致JSON解析类函数执行失败。

对应解决方案 #

场景1：列中仅存储单段数组[{"name": "my_name_1"}]（示例中后续内容为测试冗余） #

直接修改JSON路径，指定读取数组第0位元素的name属性即可：
JSON_EXTRACT_SCALAR(column_name, "$[0].name")

场景2：列中确实是多段JSON数组拼接的字符串 #

先通过字符串函数截取第一段完整的JSON数组，再做解析，以MySQL语法为例，其他SQL引擎替换对应字符串函数即可：

SELECT JSON_EXTRACT_SCALAR(
    -- 截取第一个完整的JSON数组段并补全闭合符号
    CONCAT(SUBSTRING_INDEX(column_name, ']', 1), ']'),
    "$[0].name"
) AS first_name 
FROM your_table;

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