在较低级别没有数组包装的JSON [英] JSON without array wrapper on lower levels

问题描述

我试图获得的只是要构建的一条简单的SQL语句:

All I try to get is a simple SQL statement to build:

 {"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 

我在数组包装器下获得了第二层，就像这样:

I get the second level under an array wrapper, like this:

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

但是如果我在第二级添加WITHOUT_ARRAY_WRAPPER ...

But if I add the WITHOUT_ARRAY_WRAPPER on the second level...

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 

一些有趣的事情发生了:

something funny happens:

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

我确实知道我缺少什么，但是我看不见

I am missing something, I know, sure, but I can not for-see

推荐答案

我认为Matheno(在注释中)是正确的:显然，问题在于FOR JSON转义了您的文本.为了防止对内部JSON的这种不必要的转义，您可以将其包装为JSON_QUERY():

I think that Matheno (in the comments) is right: apparently the problem is that FOR JSON escapes your text. To prevent this unwanted escaping of inner JSON you could wrap it with 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}

这篇关于在较低级别没有数组包装的JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！