SQL中提取JSON格式字段内多个name值并拆分为多行的问题

原始数据表 #

期望结果 #

尝试过的代码 #

right((SUBSTRING(col, LEN(LEFT(col, CHARINDEX ('"name":"', col))) + 1, LEN(col) - LEN(LEFT(col, 
CHARINDEX ('"name":"', col))) - LEN(RIGHT(col, LEN(col) - CHARINDEX ('"},{"', col))) - 1)),len((SUBSTRING(col, LEN(LEFT(col, CHARINDEX ('"name":"', col))) + 1, LEN(col) - LEN(LEFT(col, 
CHARINDEX ('"name":"', col))) - LEN(RIGHT(col, LEN(col) - CHARINDEX ('"},{"', col))) - 1)))-7)

上述代码仅能提取第一个name值，无法处理多个值拆分多行的需求。

解决方案 #

你的name字段属于近似JSON数组格式（注意原数据末尾缺失一个}，需先修复格式），直接用JSON解析函数比字符串截取更可靠，可适配任意数量的name值：

SQL Server 版本 #

SELECT 
    t.id,
    j.name
FROM 
    your_table t
CROSS APPLY 
    OPENJSON(
        -- 补全缺失的}，确保JSON格式合法
        CASE WHEN RIGHT(t.name, 1) = ']' THEN STUFF(t.name, LEN(t.name), 0, '}') ELSE t.name END
    )
    WITH (name NVARCHAR(100) '$.name') j;

MySQL 版本 #

SELECT 
    t.id,
    j.name
FROM 
    your_table t,
    JSON_TABLE(
        -- 补全缺失的}，确保JSON格式合法
        CASE WHEN RIGHT(t.name, 1) = ']' THEN INSERT(t.name, LENGTH(t.name), 0, '}') ELSE t.name END,
        '$[*]' COLUMNS(name VARCHAR(100) PATH '$.name')
    ) j;

说明 #

• 字符串截取的方式只能处理固定位置的单个值，无法循环解析多个元素，而JSON解析函数可以直接将数组转换为行集，自动关联原表id。
• 先修复JSON格式是为了避免解析报错，若你的实际数据格式都是合法JSON数组，可去掉格式修复的CASE逻辑。

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