Otdfbt

It would seem that json_query fails to remove escape characters for the double quote character (") if the function is being applied within a case statement.

Example code below.

declare @data nvarchar(max);
declare @debug int = 0;

set @data = '"id":10';

set @debug = 0;
select
 isjson(@data) as 'validateData',
 @data as 'unprocessedSourceData',
 json_query(@data) as 'processedSourceData',
 case when @debug = 1 then json_query(@data) else null end as 'conditionallyProcessedSourceData'
 for json path, without_array_wrapper ;

set @debug = 1;
select
 isjson(@data) as 'validateData',
 @data as 'unprocessedSourceData',
 json_query(@data) as 'processedSourceData',
 case when @debug = 1 then json_query(@data) else null end as 'conditionallyProcessedSourceData'
 for json path, without_array_wrapper ;

Result from the first query:

"validateData":1,"unprocessedSourceData":""id":10","processedSourceData":"id":10

Result from the second query:

"validateData":1,"unprocessedSourceData":""id":10","processedSourceData":"id":10,"conditionallyProcessedSourceData":""id":10"

Note the escape characters toward the end: ""id":10"

1) Why would this be so?

2) How do I rework this query so that if @debug = 1 then the conditionallyProcessedSourceData element contains valid JSON, and if @debug = 0 then the element is null?

From JSON_QUERY (Transact-SQL)

Returns a JSON fragment of type nvarchar(max).

and

JSON_QUERY returns a valid JSON fragment. As a result, FOR JSON
doesn't escape special characters in the JSON_QUERY return value.

So for json path escapes nvarchar(max) data unless the nvarchar(max) data comes from json_query().

In your query the data comes from the case statement not directly from json_query.

The workaround you can do is to put json_query outside of the case.

json_query(case when @debug = 1 then @data else null end)

Incorporating this into your full script, so you can compare the different approaches, gives:

declare @data nvarchar(max);
declare @debug int = 0;

set @data = '"id":10';

set @debug = 0;
select
 isjson(@data) as 'validateData',
 @data as 'unprocessedSourceData',
 json_query(@data) as 'processedSourceData',
 case when @debug = 1 then json_query(@data) else null end as 'conditionallyProcessedSourceData', 
 json_query(case when @debug = 1 then @data else null end) as 'caseResultProcessed'
 for json path, without_array_wrapper ;

set @debug = 1;
select
 isjson(@data) as 'validateData',
 @data as 'unprocessedSourceData',
 json_query(@data) as 'processedSourceData',
 case when @debug = 1 then json_query(@data) else null end as 'conditionallyProcessedSourceData', -- This was the issue
 json_query(case when @debug = 1 then @data else null end) as 'caseResultProcessed' -- This is the solution
 for json path, without_array_wrapper ;