Why does JSON_QUERY within CASE statement fail to remove escape characters?Oracle GoldenGate add trandata errorsWhy does formatting a time with AM/PM specifier return null?How to use Case Statement with BCPSQL 2005 Unused proceduresImport poorly formatted CSV/JSON to SQL ServerInvestigating errors from strange queryHow to use SUM window function on a column that requires data conversion (and filtering away non convertible numbers)?bit columns generating warnings - how can a cast in the field list affect the cardinality estimate?INSERT/SELECT xml column from one table to anotherupdate json value to null
shutdown at specific date
What problems does SciDraw still solve?
Glitch in AC sine wave interfering with phase cut dimming
Is CD audio quality good enough for the final delivery of music?
Is a post-climate apocolypse city in which many or most insects have disappeared realistic?
What is the most important source of natural gas? coal, oil or other?
Why did this prime-sequence puzzle not work?
The Passive Wisdom (Perception) score of my character on D&D Beyond seems too high
Is it possible to change original filename of an exe?
How could Catholicism have incorporated witchcraft into its dogma?
Plot exactly N bounce of a ball
Why does the 6502 have the BIT instruction?
Can a wire having a 610-670 THz (frequency of blue light) AC frequency supply, generate blue light?
Can non-English-speaking characters use wordplay specific to English?
What does "Marchentalender" on the front of a postcard mean?
Compact Mechanical Energy Source
Yandex Programming Contest: Alarms
Tic-Tac-Toe for the terminal
Apparent Ring of Craters on the Moon
How to prevent bad sectors?
How many chess players are over 2500 Elo?
Looking after a wayward brother in mother's will
Which noble houses were destroyed during the Game of Thrones?
How were these pictures of spacecraft wind tunnel testing taken?
Why does JSON_QUERY within CASE statement fail to remove escape characters?
Oracle GoldenGate add trandata errorsWhy does formatting a time with AM/PM specifier return null?How to use Case Statement with BCPSQL 2005 Unused proceduresImport poorly formatted CSV/JSON to SQL ServerInvestigating errors from strange queryHow to use SUM window function on a column that requires data conversion (and filtering away non convertible numbers)?bit columns generating warnings - how can a cast in the field list affect the cardinality estimate?INSERT/SELECT xml column from one table to anotherupdate json value to null
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
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
?
sql-server t-sql sql-server-2016 json
add a comment |
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
?
sql-server t-sql sql-server-2016 json
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
add a comment |
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
?
sql-server t-sql sql-server-2016 json
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
?
sql-server t-sql sql-server-2016 json
sql-server t-sql sql-server-2016 json
edited May 20 at 23:53
youcantryreachingme
asked May 15 at 4:19
youcantryreachingmeyoucantryreachingme
62610
62610
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
add a comment |
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
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
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
add a comment |
1 Answer
1
active
oldest
votes
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 ;
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f238186%2fwhy-does-json-query-within-case-statement-fail-to-remove-escape-characters%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 ;
add a comment |
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 ;
add a comment |
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 ;
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 ;
edited May 21 at 4:00
youcantryreachingme
62610
62610
answered May 15 at 5:50
Mikael ErikssonMikael Eriksson
18.4k34689
18.4k34689
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f238186%2fwhy-does-json-query-within-case-statement-fail-to-remove-escape-characters%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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