MySQL解析JSON数据数组：提取所有对象字段的全表关联法

准备条件与原理分析

假设原始数据为JSON数组格式的字符串，例如：
[{"itemId":3101,"itemName":"空滤器"},{"itemId":3102,"itemName":"水管"}]

我们需要达到的效果是将这一行数据展开为多行，每行包含一个JSON对象，并从中提取itemId和itemName等字段。
由于MySQL内置的JSON函数（如JSON_EXTRACT）主要处理单个JSON值，需先将数组“扁平化”。

第1步：创建数字辅助表

为了生成足够数量的行以匹配数组中的元素个数，需要一个连续的数字序列表。可以使用系统表mysql.help_topic，或自行创建一张简单的数字表keyid。

注意：如果使用自建表keyid，其最大ID值必须大于JSON数组中可能存在的最大元素个数。若使用mysql.help_topic，需确保有访问该表的权限，且最多支持700个元素的拆分。

第2步：预处理JSON字符串标识符

标准的JSON数组格式为[{...},{...}]，难以直接利用分隔符切割。需要将其转换为以分号;分隔的形式：{...};{...}。
操作顺序如下：

1. 去除开头的[和结尾的]。
2. 将元素间的},{替换为;{（即保留第一个{，移除中间的}，添加分隔符）。注：原文逻辑是将},{整体替换，此处修正为更精准的替换策略，即先去掉括号，再将内部连接符替换。

预处理SQL示例：

SELECT replace(replace(replace(json_column, '},{', ';{'), ']', ''), '[', '') AS cleaned_json
FROM your_table;

第3步：关联生成多行数据

利用辅助表b.id与预处理后的字符串进行JOIN。通过SUBSTRING_INDEX的双重嵌套调用，截取第N个分号前的内容，再反向截取最后一个分号后的部分，从而获得第N个独立的JSON对象字符串。

关键公式：
SUBSTRING_INDEX( SUBSTRING_INDEX( str, ';', b.id + 1 ), ';', -1 )

连接条件确保只生成有效数量的行：
b.id < (长度差异计算出的元素个数)

解析JSON字段

当每一行都变成了独立的JSON对象字符串（如{"itemId":3101,...}）后，即可使用JSON_EXTRACT函数提取具体字段。

完整实现代码

以下代码整合了清洗、拆分、解析全过程，适用于自建keyid表的情况：

SELECT 
    a.cleaned_json,
    -- 当前行的JSON对象字符串
    SUBSTRING_INDEX(SUBSTRING_INDEX(a.cleaned_json, ';', b.id + 1), ';', -1) AS json_obj_str,
    b.id AS row_index,
    -- 提取字段：数值型字段通常无需去引号
    JSON_EXTRACT(
        SUBSTRING_INDEX(SUBSTRING_INDEX(a.cleaned_json, ';', b.id + 1), ';', -1), 
        '$.itemId' 
    ) AS itemId,
    -- 提取字段：字符串型字段JSON_EXTRACT会带双引号，需REPLACE去除
    REPLACE(
        JSON_EXTRACT(
            SUBSTRING_INDEX(SUBSTRING_INDEX(a.cleaned_json, ';', b.id + 1), ';', -1), 
            '$.itemName'
        ), '"', ''
    ) AS itemName
FROM 
    (SELECT replace(replace(replace(jsonarr, '},{', ';{'), ']', ''), '[', '') AS cleaned_json FROM maptest) a
JOIN keyid b ON b.id < (
    LENGTH(a.cleaned_json) - LENGTH(REPLACE(a.cleaned_json, ';', '')) + 1
);

替代方案：若拥有mysql.help_topic表权限，可将上述代码中的keyid b替换为mysql.help_topic b，并将b.id替换为b.help_topic_id，其余逻辑不变。

注意事项与总结

• 字符串引号处理：JSON_EXTRACT对JSON字符串类型返回的结果始终包含双引号（如"value"），务必在外层包裹REPLACE(..., '"', '')以清理数据。
• 性能考量：此方法基于标量函数的迭代，适合中小规模数据或非高频实时查询。若数据量极大，建议在应用层解析或通过ETL过程将JSON展平后存入关系表。
• 兼容性：该语法适用于MySQL 5.7及以上版本（支持JSON函数）。

以上就是MySQL解析JSON数据组获取数据组所有字段的详细内容，更多关于MySQL JSON解析技巧的资料请关注本站其它相关文章！