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
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
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"}]}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With