MariaDB中TEXT格式类JSON数据转行列的SQL查询方案求助

针对你存储在My_Table表JSON_Column列中的类JSON文本数据，以下是两种可靠的转换方案，最终可得到Key、New Value、Old Value的行列格式：

方案一：递归CTE拆分法（推荐，不依赖键值对顺序） #

该方法通过字符串提取+递归CTE拆分多键值对，适配单键/多键场景，且不要求New/Old部分的键顺序一致。

完整SQL代码 #

WITH RECURSIVE split_data AS (
    -- 初始步骤：提取New/Old部分的核心内容，拆分第一个键值对
    SELECT
        -- 若表无主键，用ROW_NUMBER生成唯一标识
        ROW_NUMBER() OVER () AS row_id,
        TRIM(SUBSTRING_INDEX(new_content, ',', 1)) AS new_kv,
        TRIM(SUBSTRING_INDEX(old_content, ',', 1)) AS old_kv,
        TRIM(SUBSTRING(new_content, LENGTH(SUBSTRING_INDEX(new_content, ',', 1)) + 2)) AS remaining_new,
        TRIM(SUBSTRING(old_content, LENGTH(SUBSTRING_INDEX(old_content, ',', 1)) + 2)) AS remaining_old
    FROM (
        SELECT
            JSON_Column,
            -- 提取New Values大括号内的内容
            REGEXP_SUBSTR(JSON_Column, '\\[New Values: \\{(.*?)\\}\\]', 1, 1, 's', 1) AS new_content,
            -- 提取Old Values大括号内的内容
            REGEXP_SUBSTR(JSON_Column, '\\[Old Values: \\{(.*?)\\}\\]', 1, 1, 's', 1) AS old_content
        FROM My_Table
    ) AS extracted
    WHERE new_content IS NOT NULL AND old_content IS NOT NULL

    UNION ALL

    -- 递归步骤：拆分剩余的键值对
    SELECT
        row_id,
        TRIM(SUBSTRING_INDEX(remaining_new, ',', 1)) AS new_kv,
        TRIM(SUBSTRING_INDEX(remaining_old, ',', 1)) AS old_kv,
        TRIM(SUBSTRING(remaining_new, LENGTH(SUBSTRING_INDEX(remaining_new, ',', 1)) + 2)) AS remaining_new,
        TRIM(SUBSTRING(remaining_old, LENGTH(SUBSTRING_INDEX(remaining_old, ',', 1)) + 2)) AS remaining_old
    FROM split_data
    WHERE remaining_new != '' AND remaining_old != ''
)
-- 最终拆分键与对应新旧值
SELECT
    TRIM(SUBSTRING_INDEX(new_kv, '=', 1)) AS `Key`,
    TRIM(SUBSTRING_INDEX(new_kv, '=', -1)) AS `New Value`,
    TRIM(SUBSTRING_INDEX(old_kv, '=', -1)) AS `Old Value`
FROM split_data
ORDER BY row_id, `Key`;

关键说明 #

• 若表已有主键（如id），可替换ROW_NUMBER() OVER () AS row_id为实际主键字段
• 假设键值对以逗号+空格分隔，若你的数据仅用逗号分隔，需将代码中+2改为+1
• 适配值中不含逗号、等号的场景（符合你的示例数据格式）

方案二：JSON_TABLE转换法（简洁，依赖键顺序） #

该方法将类JSON文本转换为标准JSON格式，再通过JSON_TABLE展开，代码更简洁，但要求New/Old部分的键值对顺序完全一致。

完整SQL代码 #

SELECT
    jt.key_name AS `Key`,
    jt.new_val AS `New Value`,
    jo.old_val AS `Old Value`
FROM My_Table
JOIN JSON_TABLE(
    -- 将New部分转换为标准JSON数组
    CONCAT('[{"', REPLACE(REPLACE(REGEXP_SUBSTR(JSON_Column, '\\[New Values: \\{(.*?)\\}\\]', 1, 1, 's', 1), '=', '":"'), ',', '","'), '"}]'),
    '$[*].*' COLUMNS (
        key_name VARCHAR(255) PATH '$',
        new_val TEXT PATH '$'
    )
) AS jt
JOIN JSON_TABLE(
    -- 将Old部分转换为标准JSON数组
    CONCAT('[{"', REPLACE(REPLACE(REGEXP_SUBSTR(JSON_Column, '\\[Old Values: \\{(.*?)\\}\\]', 1, 1, 's', 1), '=', '":"'), ',', '","'), '"}]'),
    '$[*].*' COLUMNS (
        key_name VARCHAR(255) PATH '$',
        old_val TEXT PATH '$'
    )
) AS jo ON jt.key_name = jo.key_name;

关键说明 #

• 仅适用于New/Old部分键顺序完全匹配的场景
• 值中不能包含双引号或逗号，否则会导致JSON转换失败

输出结果示例 #

运行上述任一方案后，你的4行示例数据将输出如下格式：

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