JSON without array wrapper on lower levels JSON without array wrapper on lower levels json json

JSON without array wrapper on lower levels


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}


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