30

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

Improve this question
2
  • 3
    You could try to wrap the query in JSON_QUERY. IMHO these backslashes in your JSON string are escaping the double quotes inside your string. Your JSON is a valid. Commented Jan 31, 2017 at 12:42
  • 1
    @Matheno, I believe this is not my case, first JSON is what I try to obtain, second and third all what I achieve, How is possible to achieve that first JSON Commented Feb 1, 2017 at 9:41

3 Answers 3

Reset to default
31

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}
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Relevant reading here and here. From the second link: "Note one important thing – WITHOUT_ARRAY_WRAPPER will not generate valid JSON text. If you try to put FOR JSON WITHOUT_ARRAY_WRAPPER as column expression, it will be treated as a plain text..."
So long as there is only one row, WITHOUT_ARRAY_WRAPPER indeed generates valid JSON.
9

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
Improve this answer

1 Comment

This is an official way to achieve the expected result. See learn.microsoft.com/de-de/sql/relational-databases/json/…. If you use WITHOUT_ARRAY_WRAPPER the result is valid JSON only if there is only one dataset in the result. I guess Microsoft decided to treat it as string just in case. Thats for the reason of the Problem.
4

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"}]}
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.