我试图获得的是一个简单的sql语句来构建：
{"status":{"code":404,"message":"Not found"},"otherthing":20}
如果我设置为：
DECLARE @ReturnJSON nvarchar(max)
SET @ReturnJSON = (
SELECT (
SELECT 404 as [code],'Not found' as [message]
FOR JSON PATH ) as [status],20 as [otherthing]
FOR JSON PATH,WITHOUT_ARRAY_WRAPPER) ;
SELECT @ReturnJSON
我在数组包装器下获得第二级,如下所示：
{"status":[{"code":404,"message":"Not found"}],"otherthing":20}
但是如果我在第二级添加WITHOUT_ARRAY_WRAPPER ……
DECLARE @ReturnJSON nvarchar(max)
SET @ReturnJSON = (
SELECT (
SELECT 404 as [code],'Not found' as [message]
FOR JSON PATH,WITHOUT_ARRAY_WRAPPER ) as [status],WITHOUT_ARRAY_WRAPPER) ;
SELECT @ReturnJSON
有趣的事情发生：
{"status":"{\"code\":404,\"message\":\"Not found\"}","otherthing":20}
我知道,我确实错过了一些东西,但我无法看到
解决方法
我认为Matheno(在评论中)是对的：显然问题是FOR JSON逃脱了你的文本.为了防止这种不必要的内部JSON转义,你可以用JSON_QUERY()包装它：
DECLARE @ReturnJSON nvarchar(max) DECLARE @innerJSON nvarchar(max) set @innerJSON =( SELECT 404 as [code],WITHOUT_ARRAY_WRAPPER ) SET @ReturnJSON = ( SELECT ( JSON_QUERY(@innerJSON) ) as [status],WITHOUT_ARRAY_WRAPPER) ; SELECT @ReturnJSON
这输出：
{"status":{"code":404,"otherthing":20}
