Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

JSON without array wrapper on lower levels

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

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

If I set as :

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}

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

like image 588
pGrnd2 Avatar asked Jan 31 '17 12:01

pGrnd2


3 Answers

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 

This outputs:

{"status":{"code":404,"message":"Not found"},"otherthing":20}
like image 135
Andrea Avatar answered Oct 19 '22 17:10

Andrea


It's not exact answer to your question, but I hope it will give solution to your problem.

You can construct expected output without nested query, just define hierarchy using property names, like this:

DECLARE @ReturnJSON nvarchar(max)

SET @ReturnJSON = (
    SELECT 
        404 as [status.code]
        ,'Not found' as [status.message]
        , 20 as [otherthing]
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) ;

SELECT @ReturnJSON
like image 39
Uriil Avatar answered Oct 19 '22 17:10

Uriil


If you don't want the extra variables, you can inline the innerJson like this:


DECLARE @ReturnJSON AS NVARCHAR(max);

SET @ReturnJSON = (SELECT JSON_QUERY((SELECT [code], [message] FOR JSON PATH)) AS [Status] 
FROM (VALUES(404, 'Non found', 20)) AS src([code], [message], [otherthing])
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER)

SELECT @ReturnJSON

Same output as Andrea

{"Status":[{"code":404,"message":"Non found"}]}
like image 1
Josh Ross Avatar answered Oct 19 '22 17:10

Josh Ross