4

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?

  • Please repost your solution as an answer and remove it from the question to follow the format of the site, thank you! – Andriy M May 20 at 7:50
6

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.