如何用SQL拆分JSON格式字段为3列并删除原列？

问题说明 #

有一个存储JSON字符串的列，需要将其拆分为rich_result_type、Items、Issues三列，操作完成后删除原列。此前尝试用split_part函数仅能提取出richResultType部分，无法正确获取其他字段。

示例JSON数据 #

[{"richResultType": "Breadcrumbs", "items": [{"name": "Unnamed item"}]}, {"richResultType": "FAQ", "items": [{"name": "Unnamed item"}]}, {"richResultType": "Product snippets", "items": [{"name": "Elevate Vaillant Langarmhemd", "issues": [{"issueMessage": "Missing field \"aggregateRating\"", "severity": "WARNING"}, {"issueMessage": "Missing field \"review\"", "severity": "WARNING"}, {"issueMessage": "Missing field \"highPrice\"", "severity": "WARNING"}]}]}]

尝试过的错误SQL #

select split_part('richResultType', ' ', 1) || ' ' || split_part('richResultType', ' ', 2)

期望输出 #

正确SQL实现（以PostgreSQL为例） #

由于数据是JSON数组结构，需先将数组拆分为单个JSON对象，再提取对应字段，同时处理嵌套数组的展开：

-- 1. 先验证拆分结果
WITH json_data AS (
    SELECT 
        json_array_elements(your_json_column::json) AS json_obj -- 拆分JSON数组为单个对象
    FROM your_table
)
SELECT
    json_obj->>'richResultType' AS rich_result_type,
    json_obj->'items'->0->>'name' AS Items,
    -- 从issueMessage中提取缺失的字段名
    regexp_replace(issue->>'issueMessage', 'Missing field "(.+)"', '\1') AS Issues
FROM json_data,
     json_array_elements(coalesce(json_obj->'items'->0->'issues', '[]'::json)) AS issue;

-- 2. 生成新表并替换原表（按需执行）
CREATE TABLE new_table AS
SELECT
    -- 保留原表其他需要的字段，替换成实际字段名
    id, other_column,
    json_obj->>'richResultType' AS rich_result_type,
    json_obj->'items'->0->>'name' AS Items,
    regexp_replace(issue->>'issueMessage', 'Missing field "(.+)"', '\1') AS Issues
FROM your_table,
     json_array_elements(your_json_column::json) AS json_obj,
     json_array_elements(coalesce(json_obj->'items'->0->'issues', '[]'::json)) AS issue;

-- 删除原表并更名新表（可选）
DROP TABLE your_table;
ALTER TABLE new_table RENAME TO your_table;

关键函数说明 #

• json_array_elements：展开JSON数组，将数组内每个元素转为单独行
• ->>：提取JSON字段的文本值
• coalesce(..., '[]'::json)：处理无Issues的场景，避免空值报错
• regexp_replace：匹配Missing field "xxx"格式字符串，提取其中的字段名

注意事项 #

• 上述代码适配PostgreSQL，不同数据库的JSON函数语法不同（如MySQL用JSON_TABLE、JSON_EXTRACT），需根据实际数据库调整
• 操作前备份原表数据，避免意外丢失
• 若原表有其他业务字段，务必在查询中保留

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