SQL Server修改JSON时如何避免URL等属性出现转义序列

问题根源 #

你遇到的斜杠被转义成\/的情况，本质是FOR JSON PATH的默认行为：它会自动转义JSON规范中的特殊字符（包括正斜杠），即便你用JSON_QUERY包裹结果避免了JSON_MODIFY的二次转义，FOR JSON生成数组时已经完成了转义操作。

解决方案 #

要避免这种不必要的转义，核心是让FOR JSON识别URL类字段已经是合法的JSON字符串值，无需再处理。可以通过JSON_QUERY将这些字段直接包装成JSON格式的字符串，跳过FOR JSON的转义逻辑。

修改后的查询语句如下：

SELECT TOP 1  JSON_MODIFY(
           CASE WHEN ISNULL([_DOCUMENTS],'') = '' THEN '{}' ELSE [_DOCUMENTS] END ,
           '$.Documents',
           JSON_QUERY(
              (
              SELECT 
                 Title,
                 Category,
                 NumberOfPages,
                 SizeInBytes,
                 MimeType,
                 [Date],
                 ResourceId,
                 ResourceType,
                 ParentEntityType,
                 JSON_QUERY('"' + [Url] + '"') AS [Url],
                 [Site],
                 [Description],
                 JSON_QUERY('"' + PreviewImageUrl + '"') AS PreviewImageUrl,
                 LanguageCode,
                 Sort
              FROM OPENJSON([_DOCUMENTS], '$.Documents') WITH (
                 Title nvarchar(1000) '$.Title',
                 Category nvarchar(1000) '$.Category',
                 NumberOfPages INT '$.NumberOfPages',
                 SizeInBytes INT '$.SizeInBytes',
                 MimeType nvarchar(1000) '$.MimeType',
                 [Date] nvarchar(1000) '$.Date',
                 ResourceId INT '$.ResourceId',
                 ResourceType INT '$.ResourceType',
                 ParentEntityType INT '$.ParentEntityType',
                 [Url] nvarchar(1000) '$.Url',
                 [Site] nvarchar(1000) '$.Site',
                 [Description] nvarchar(1000) '$.Description',
                 PreviewImageUrl nvarchar(1000) '$.PreviewImageUrl',
                 LanguageCode nvarchar(1000) '$.LanguageCode',
                 Sort INT '$.Sort'
              )
              WHERE LanguageCode != N'ru'
              FOR JSON PATH
              )
           )
        )
    FROM [dbo].[myTalbe]

注意事项 #

如果你的URL字段中包含双引号，直接拼接'"' + [Url] + '"'会破坏JSON结构，此时需要先转义双引号：

JSON_QUERY('"' + REPLACE([Url], '"', '\"') + '"') AS [Url]

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