SQL Server解析JSON字符串转换为表格格式报错问题咨询

报错原因排查 #

第一个测试代码报错 #

Msg 13609, Level 16, State 4, Line 63
JSON text is not properly formatted. Unexpected character 'T' is found at position 916.

根因是JSON格式不符合官方标准：JSON规范要求布尔值必须为全小写的true/false，测试JSON中"Consent":True的True首字母大写，SQL Server的JSON解析器无法识别该格式。

第二个业务查询报错 #

Msg 13609, Level 16, State 2, Line 1
JSON text is not properly formatted. Unexpected character 'o' is found at position 0.

根因有两类常见情况：

• cijreport字段存储的不是标准JSON，包含HTML转义字符（比如用"代替双引号），或者内容头部存在非JSON前缀的普通文本
• 部分非空的cijreport字段存储的是简单状态字符串（比如ok、error），不是完整JSON结构

可行实现方案 #

步骤1：预处理JSON格式 #

解析前先修正格式问题：

1. 把大写布尔值替换为标准小写格式
2. 把HTML转义字符替换为JSON标准符号，示例：

-- 可根据实际脏数据情况叠加更多REPLACE逻辑
REPLACE(REPLACE(cijreport, '"', '"'), 'True', 'true')

步骤2：定位解析目标节点 #

直接通过路径定位到GetCustomReportResult节点，用WITH子句直接映射所需字段，避免冗余解析：

SELECT 
    y.ApplicationId,
    -- 按需补充需要提取的字段
    j.CIP,
    j.CIQ,
    j.Consent,
    j.IDNumber,
    j.ReportStatus,
    j.ReferenceNumber
FROM [你的实际表名] as y
CROSS APPLY OPENJSON (
    -- 先预处理JSON格式
    REPLACE(REPLACE(y.cijreport, '"', '"'), 'True', 'true'),
    -- 直接定位到目标节点的完整路径
    '$.data.response.GetCustomReportResult'
)
WITH (
    -- 映射一级字段
    CIP NVARCHAR(100) '$.CIP',
    CIQ NVARCHAR(100) '$.CIQ',
    -- 映射嵌套Parameters节点下的字段
    Consent BIT '$.Parameters.Consent',
    IDNumber NVARCHAR(50) '$.Parameters.IDNumber',
    -- 映射嵌套ReportInfo节点下的字段
    ReportStatus NVARCHAR(100) '$.ReportInfo.ReportStatus',
    ReferenceNumber NVARCHAR(100) '$.ReportInfo.ReferenceNumber'
    -- 其他字段按照以上规则补充即可
) as j
-- 过滤非法JSON行，避免脏数据导致整个查询报错
WHERE ISJSON(REPLACE(REPLACE(y.cijreport, '"', '"'), 'True', 'true')) = 1

补充说明 #

如果需要提取Parameters.Sections.string这类数组类型的字段，可以额外叠加一层CROSS APPLY OPENJSON处理对应数组节点即可。建议先执行查询筛选出所有格式非法的行，先清理脏数据再执行正式解析：

SELECT cijreport, ApplicationId 
FROM [你的实际表名] 
WHERE ISJSON(REPLACE(REPLACE(cijreport, '"', '"'), 'True', 'true')) = 0

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