By youcantryreachingme


2019-05-15 04:19:29 8 Comments

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?

1 comments

@Mikael Eriksson 2019-05-15 05:50:14

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 ;

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] update json value to null

  • 2019-04-17 06:41:07
  • youcantryreachingme
  • 21 View
  • 1 Score
  • 1 Answer
  • Tags:   sql-server t-sql json

1 Answered Questions

INSERT/SELECT xml column from one table to another

1 Answered Questions

[SOLVED] Investigating errors from strange query

2 Answered Questions

[SOLVED] Import poorly formatted CSV/JSON to SQL Server

1 Answered Questions

[SOLVED] SQL 2005 Unused procedures

1 Answered Questions

[SOLVED] How to use Case Statement with BCP

  • 2016-07-27 05:22:16
  • peter
  • 581 View
  • 1 Score
  • 1 Answer
  • Tags:   sql-server bcp

2 Answered Questions

[SOLVED] Why does formatting a time with AM/PM specifier return null?

1 Answered Questions

[SOLVED] Oracle GoldenGate add trandata errors

Sponsored Content