SQL Server使用OPENJSON解析JSON返回Unicode转义字符串问题求解

你遇到的问题是原始JSON中的Unicode转义序列被双重转义：正常JSON中\uXXXX格式的转义会被OPENJSON自动识别转换为对应字符，但你存储的JSON中转义符多了一层转义变成\\uXXXX，第一次OPENJSON解析后会保留字面量形式的\uXXXX字符串，不会自动转码。

因为你只有远程库的只读权限，无法修改源数据、也无法创建自定义函数，可直接通过二次调用SQL Server原生JSON解析能力实现转义还原，不需要额外权限：

SELECT 
  Units.Id,
  -- 将提取到的转义字符串封装为合法JSON值后二次解析，自动完成Unicode转义还原
  JSON_VALUE(CONCAT(N'["', REPLACE(Units.Name, '"', '\\"'), N'"]'), N'$[0]') AS Name
FROM OPENJSON(@Json) AS i
CROSS APPLY OPENJSON(i.[value]) WITH (
   [Id] INT '$.id',
   [Name] NVARCHAR(MAX) '$.name'
) AS Units

1. 用REPLACE(Units.Name, '"', '\\"')处理字符串中可能存在的双引号，避免拼接JSON时出现语法错误
2. 用CONCAT将目标字符串包装为单元素JSON数组的字符串形式
3. 调用JSON_VALUE提取数组的第一个元素，SQL Server原生JSON解析器会自动识别并转换\uXXXX格式的Unicode转义为对应可读字符
4. 所有函数均为SQL Server内置系统函数，只读权限下可正常调用，无需任何数据库修改权限

如果你有权限创建自定义标量函数，也可以编写专门的Unicode转义解析函数实现更灵活的处理，性能也会更优：

CREATE FUNCTION dbo.DecodeUnicodeEscape(@Input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
  DECLARE @Pos INT = PATINDEX('%\\u[0-9A-Fa-f][0-9A-Fa-f][0-9A-Fa-f][0-9A-Fa-f]%', @Input)
  WHILE @Pos > 0
  BEGIN
    DECLARE @Code NVARCHAR(4) = SUBSTRING(@Input, @Pos + 2, 4)
    DECLARE @Char NCHAR(1) = NCHAR(CONVERT(INT, CONVERT(BINARY(2), @Code, 2)))
    SET @Input = STUFF(@Input, @Pos, 6, @Char)
    SET @Pos = PATINDEX('%\\u[0-9A-Fa-f][0-9A-Fa-f][0-9A-Fa-f][0-9A-Fa-f]%', @Input)
  END
  RETURN @Input
END

调用时直接替换Name字段即可：

SELECT Units.Id, dbo.DecodeUnicodeEscape(Units.Name) AS Name
FROM OPENJSON(@Json) AS i
CROSS APPLY OPENJSON(i.[value]) WITH (
   [Id] INT '$.id',
   [Name] NVARCHAR(MAX) '$.name'
) AS Units

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