sql - JSON 在较低级别上没有数组包装器

Question

我试图得到的只是一个要构建的简单 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]
       , 20 as [otherthing]
   FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) ;

SELECT @ReturnJSON 

发生了一些有趣的事情:

{"status":"{\"code\":404,\"message\":\"Not found\"}","otherthing":20}

我错过了一些东西，我知道，当然，但我无法预见

Answer 1

我认为 Matheno(在评论中)是正确的:显然问题是 FOR JSON 转义了您的文本。为了防止这种不必要的内部 JSON 转义，您可以用 JSON_QUERY() 包装它:

DECLARE @ReturnJSON nvarchar(max)
DECLARE @innerJSON nvarchar(max)

set @innerJSON =(        SELECT 404 as [code]
              ,'Not found' as [message] 
               FOR JSON PATH, WITHOUT_ARRAY_WRAPPER )

SET @ReturnJSON = (
    SELECT ( 
        JSON_QUERY(@innerJSON)
               ) as [status]
       , 20 as [otherthing]
   FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) ;

SELECT @ReturnJSON 

输出:

{"status":{"code":404,"message":"Not found"},"otherthing":20}