Why does JSON_QUERY within CASE statement fail to remove escape characters?

Question

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?

• Andriy M: Please repost your solution as an answer and remove it from the question to follow the format of the site, thank you!

Answer 1

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 ;