T-SQL生成JSON时schemas字段未输出为数组的查询语句修复求助

问题原因 #

你当前的写法直接给schemas字段赋值了字符串类型的数组文本，SQL Server的FOR JSON语法会自动对所有字符串类型的字段值做转义处理、外层包裹双引号，因此会出现多余引号，无法直接输出为原生JSON数组。

修复方法 #

使用JSON_QUERY函数包裹数组文本，该函数的返回值会被FOR JSON识别为原生JSON片段，不会做额外转义。

修复后完整代码 #

Drop TABLE #tmp
CREATE TABLE #tmp (
    [EmployeeEmailAccount] [nvarchar](50) NULL,
    [displayName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT #tmp ([EmployeeEmailAccount], [displayName]) VALUES (N'test1@gmail.com', N'testusr1')
GO    
SELECT TOP 1                         
[schemas] = JSON_QUERY('["urn:scim:schemas:core:2.0:User" , "urn:scim:schemas:extension:fa:2.0:faUser"]'),
EmployeeEmailAccount as 'userName'
FROM #tmp
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER 

输出效果 #

执行后输出符合预期的JSON结构：

{"schemas":["urn:scim:schemas:core:2.0:User","urn:scim:schemas:extension:fa:2.0:faUser"],"userName":"test1@gmail.com"}

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