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