T-SQL中STRING_ESCAPE的反转义方案：解决转义嵌套JSON无法读取问题

核心问题本质：外层JSON的output字段存储的是字符串类型的序列化JSON，而非原生嵌套JSON对象，因此直接用多层路径查询无法识别内层结构。SQL Server内置JSON解析函数本身已支持自动反转义所有标准JSON转义字符，无需手动编写替换逻辑。

方案1：双层JSON_VALUE（单字段读取场景） #

适用于仅需提取内层JSON单个字段的场景，代码最简：

SELECT CAST(
    JSON_VALUE(
        JSON_VALUE(@jsonContent, '$.output'),
        '$.latest_activity'
    ) AS datetime2(7)
) AS latest_activity

逻辑说明：第一次调用JSON_VALUE提取output字段时，SQL Server会自动完成所有标准转义字符的还原，得到合法的内层JSON字符串，第二次直接对内层字符串做路径解析即可。

方案2：OPENJSON双层解析（多字段读取场景） #

适用于output嵌套对象内有多个字段需要提取的场景，避免多次调用JSON函数，性能更优：

SELECT 
    CAST(inner_json.latest_activity AS datetime2(7)) AS latest_activity
    -- 可在此处新增其他内层字段的读取逻辑
FROM OPENJSON(@jsonContent)
WITH (
    output nvarchar(max) '$.output'
) outer_json
CROSS APPLY OPENJSON(outer_json.output)
WITH (
    latest_activity varchar(50) '$.latest_activity'
    -- 可在此处新增其他内层字段映射
) inner_json

方案3：重构为标准JSON结构（多次查询场景） #

如果需要对同一份JSON做多次内层字段查询，可以预先把转义的嵌套JSON转换为原生嵌套结构，后续直接用多层路径查询即可：

-- 第一步：重构JSON，把转义的output字符串转为原生JSON对象
DECLARE @standard_json nvarchar(max) = JSON_MODIFY(
    @jsonContent,
    '$.output',
    JSON_QUERY(JSON_VALUE(@jsonContent, '$.output'))
)

-- 后续可直接用多层路径查询，无需重复处理转义
SELECT JSON_VALUE(@standard_json, '$.output.latest_activity') AS latest_activity

注意：此处添加JSON_QUERY是为了告知SQL Server入参为合法JSON对象，避免被二次转义。

原替换方案的风险 #

手动替换\"的方式仅能处理双引号转义，遇到其他JSON标准转义字符（如换行\n、制表符\t、Unicode转义\uXXXX、反斜杠\\等）都会解析失败，兼容性和稳定性极低，不建议使用。

内容的提问来源于stack exchange，提问作者Mark Z.