SQL Server转换JSON：处理同一属性同时为字符串和对象的问题

核心问题原因：SQL Server的FOR JSON默认会将字符串类型的内容做转义处理，如果你需要让某个字段输出为原生JSON对象而非转义后的字符串，必须用JSON_QUERY()函数包裹该字段内容，明确告知数据库该字段为合法JSON不需要转义。

以下是可直接运行的完整实现代码：

DECLARE @InputJson NVARCHAR(MAX) = N'[
    {
        "label": "City",
        "values": [
            "Test City"
        ]
    },
    {
        "label": "imgTest",
        "values": [
            {
                "identifier": "56696553-48F4-4BC5-BB43-FF4F71743EE9",
                "filename": "file1.jpg",
                "contentType": "image/jpg",
                "bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD+wSzIAAAABlBMVEX///+/v7+jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
            },
            {
                "identifier": "2D9106D7-71A1-440E-8255-679E8905B32E",
                "filename": "file2.jpg",
                "contentType": "image/jpg",
                "bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD+wSzIAAAABlBMVEX///+/v7+jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
            }
        ]
    }
]'

SELECT 
    label,
    CASE 
        -- 处理字符串类型的value
        WHEN ISJSON([value]) = 0 THEN [value]
        -- 处理对象类型的value，JSON_QUERY包裹避免转义
        ELSE JSON_QUERY((
            SELECT filename, contentType, bytes 
            FROM OPENJSON([value]) 
            WITH (
                filename NVARCHAR(200) '$.filename',
                contentType NVARCHAR(100) '$.contentType',
                bytes NVARCHAR(MAX) '$.bytes'
            )
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        ))
    END AS answer
FROM OPENJSON(@InputJson)
WITH (
    label NVARCHAR(100) '$.label',
    values NVARCHAR(MAX) '$.values' AS JSON
)
-- 拆分values数组为多行
CROSS APPLY OPENJSON([values])
FOR JSON PATH

深度嵌套JSON适配方案 #

如果你的原始JSON嵌套层级很深，可以保留原来的临时表分层处理逻辑，只需要在每一层输出JSON对象类型的字段时，都用JSON_QUERY包裹对应字段即可，完全兼容现有存储过程的实现逻辑，不会出现多余转义字符。

内容的提问来源于stack exchange，提问作者Wouter De Raeve