MySQL JSON字段按键拆分多行时的问题求助

问题1：第三行key为空的原因 #

原查询中，JSON_TABLE的FOR ORDINALITY生成的idx是从1开始的序数，但JSON数组的索引是从0开始的。当处理3个元素的JSON时，idx会是1、2、3，而JSON_KEYS返回的数组索引仅为0、1、2，因此JSON_EXTRACT(JSON_KEYS(...), '$[3]')会超出数组范围，返回null，导致第三行key为空。

修正后的查询语句 #

WITH Jsonified AS (
    SELECT 
        test_json_data,
        -- 转换单引号包裹的JSON为标准双引号格式
        CAST(REPLACE(test_json_data, '\'', '"') AS JSON) AS json_data
    FROM vt_raw_data
    WHERE full_url = 'test'
)
SELECT 
    j.json_data,
    kt.`key`,
    -- 提取对应key的value，使用简化语法（MySQL 8.0+支持）
    j.json_data->>CONCAT('$.', kt.`key`) AS `value`
FROM Jsonified j
CROSS JOIN JSON_TABLE(
    JSON_KEYS(j.json_data),
    '$[*]' COLUMNS (
        `key` VARCHAR(255) PATH '$'  -- 直接提取JSON数组中的每个key值
    )
) AS kt;

关键修改说明 #

• 直接获取key值：在JSON_TABLE中通过PATH '$'直接提取JSON_KEYS数组里的每个key，彻底避免了序数索引与JSON数组索引不匹配的问题。
• 简化value提取：使用->>运算符直接提取并去除JSON字符串的引号，比嵌套JSON_UNQUOTE(JSON_EXTRACT(...))写法更简洁高效。
• 优化JSON转换：如果你的数据中没有转义的单引号，直接用REPLACE(test_json_data, '\'', '"')即可将非标准JSON转换为MySQL可识别的格式。

测试结果 #

针对示例数据{'provider1': 'malicious', 'provider2': 'malware', 'provider3': 'malware'}，执行修正后的查询会返回：

既解决了第三行key为空的问题，也成功提取了每个key对应的value。

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