如何用OPENJSON读取嵌套转义JSON值及SQL查询报错排查

表结构 #

Tackle表： #

TackleResult表： #

Main表： #

需求 #

根据标志位@showDelhi筛选数据：

• 当@showDelhi = 1时，显示所有城市为Delhi、Himachal、Agra的记录
• 当@showDelhi = 0时，仅显示城市为Himachal、Agra的记录，排除Delhi

原查询问题 #

给定的SQL查询在@showDelhi = 1时正常运行，但@showDelhi = 0时抛出错误：

JSON text is not properly formatted. Unexpected character 't' is found at position 2.

报错原因 #

1. JSON解析路径错误：原查询嵌套OPENJSON时，内层SELECT VALUE写法错误。使用OPENJSON(... WITH (Name nvarchar(100), Value nvarchar(max)))后，结果集列名为Name和Value，而非未指定WITH时的默认列VALUE，导致取出的不是ISD对应的有效JSON字符串，引发格式错误。
2. 不必要的类型转换：将TRD.RDI转换为VARCHAR(MAX)可能导致Unicode字符损坏，破坏JSON结构，出现意外字符。
3. 逻辑写法不规范：1 = @showDelhi的写法可读性差，且SQL优化器可能不会短路执行，浪费性能。

优化后的查询方案 #

方案1：用CTE提前解析JSON（高可读性） #

DECLARE @MainId uniqueidentifier = '45e0fa71-5091-4523-87f1-4f04bdd98cd7'
DECLARE @showDelhi bit = 0;

WITH ParsedTackleResult AS (
    SELECT 
        TRD.TackleID,
        JSON_VALUE(TRD_RDI.Value, '$.SID.column.City') AS City
    FROM TackleResult TRD
    CROSS APPLY OPENJSON(TRD.RDI) WITH (
        Name nvarchar(100) '$.Name',
        Value nvarchar(max) '$.Value'
    ) AS TRD_RDI
    WHERE TRD_RDI.Name = 'ISD'
)
SELECT TG.TackleID
FROM Main P
INNER JOIN Tackle TG ON TG.TackleID = P.TackleID
INNER JOIN ParsedTackleResult PTR ON PTR.TackleID = TG.TackleID
WHERE P.MainId = @MainId
AND (
    @showDelhi = 1
    OR PTR.City != 'Delhi'
);

方案2：直接在查询中链式解析JSON（简洁） #

DECLARE @MainId uniqueidentifier = '45e0fa71-5091-4523-87f1-4f04bdd98cd7'
DECLARE @showDelhi bit = 0;

SELECT TG.TackleID
FROM Main P
INNER JOIN Tackle TG ON TG.TackleID = P.TackleID
INNER JOIN TackleResult TRD ON TRD.TackleID = TG.TackleID
CROSS APPLY OPENJSON(TRD.RDI) WITH (
    Name nvarchar(100) '$.Name',
    Value nvarchar(max) '$.Value'
) AS TRD_RDI
CROSS APPLY OPENJSON(TRD_RDI.Value) WITH (
    City nvarchar(100) '$.SID.column.City'
) AS CityData
WHERE P.MainId = @MainId
AND TRD_RDI.Name = 'ISD'
AND (
    @showDelhi = 1
    OR CityData.City != 'Delhi'
);

关键优化点 #

• 用CROSS APPLY替代嵌套子查询，提升JSON解析的性能与可读性
• 明确指定OPENJSON的映射路径，避免解析错误
• 移除不必要的VARCHAR转换，保留NVARCHAR类型确保JSON完整性
• 使用直观的逻辑判断替代1=@showDelhi，增强代码可维护性
• 提前过滤Name='ISD'的JSON元素，减少无效解析操作

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