使用MySQL的json_table将JSON数据转换为SQL表单行记录

你的SQL存在三个核心错误，导致返回全null且无法满足需求：

1. 路径逻辑错误：'$.*'是遍历JSON对象的所有键值对，但此时当前节点是每个键对应的数值数组，你在columns里用'$."A"'试图取当前节点下的"A"键，而数组本身没有这个键，所以返回null。
2. 未拆分数组元素：你的需求是把数组中的每个值单独拆分行，但当前SQL仅拿到了整个数组，没有遍历数组内的元素。
3. 未关联必要字段：没有获取JSON的键名，也没有关联原表的id、otherData字段，完全偏离了数据迁移的目标结构。

正确SQL示例（MySQL 8.0+适用） #

SELECT 
    t.id,
    j.key_name,
    jv.value,
    t.otherData
FROM my_table t,
     -- 第一步：提取JSON对象的所有键名，拆分为单独行
     JSON_TABLE(
         JSON_KEYS(t.`json`),
         '$[*]' COLUMNS(
             key_name VARCHAR(255) PATH '$'
         )
     ) j,
     -- 第二步：根据键名提取对应数组，拆分数组内的每个值为单独行
     JSON_TABLE(
         JSON_EXTRACT(t.`json`, CONCAT('$.', j.key_name)),
         '$[*]' COLUMNS(
             value INT PATH '$'
         )
     ) jv;

代码说明 #

• JSON_KEYS(t.json)：获取原JSON对象的所有键名，比如原JSON是{"a": [1,2], "b": [3]}，会得到["a","b"]。
• 第一个JSON_TABLE：把键名数组拆分为每行一个键名（key_name字段）。
• JSON_EXTRACT(...)：根据键名动态提取对应的数值数组。
• 第二个JSON_TABLE：把每个数值数组拆分为每行一个值（value字段）。
• 最终关联原表的id和otherData，得到你需要的id、key、value、otherData结构。

内容的提问来源于stack exchange，提问作者Adrian Görisch