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;








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?










share|improve this question
























  • 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


















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?










share|improve this question
























  • 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














4












4








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?










share|improve this 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?







sql-server t-sql sql-server-2016 json






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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


















  • 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











1 Answer
1






active

oldest

votes


















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 ;





share|improve this answer

























    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
    );



    );













    draft saved

    draft discarded


















    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









    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 ;





    share|improve this answer





























      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 ;





      share|improve this answer



























        6












        6








        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 ;





        share|improve this answer















        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 ;






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited May 21 at 4:00









        youcantryreachingme

        62610




        62610










        answered May 15 at 5:50









        Mikael ErikssonMikael Eriksson

        18.4k34689




        18.4k34689



























            draft saved

            draft discarded
















































            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.




            draft saved


            draft discarded














            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





















































            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







            Popular posts from this blog

            Club Baloncesto Breogán Índice Historia | Pavillón | Nome | O Breogán na cultura popular | Xogadores | Adestradores | Presidentes | Palmarés | Historial | Líderes | Notas | Véxase tamén | Menú de navegacióncbbreogan.galCadroGuía oficial da ACB 2009-10, páxina 201Guía oficial ACB 1992, páxina 183. Editorial DB.É de 6.500 espectadores sentados axeitándose á última normativa"Estudiantes Junior, entre as mellores canteiras"o orixinalHemeroteca El Mundo Deportivo, 16 setembro de 1970, páxina 12Historia do BreogánAlfredo Pérez, o último canoneiroHistoria C.B. BreogánHemeroteca de El Mundo DeportivoJimmy Wright, norteamericano do Breogán deixará Lugo por ameazas de morteResultados de Breogán en 1986-87Resultados de Breogán en 1990-91Ficha de Velimir Perasović en acb.comResultados de Breogán en 1994-95Breogán arrasa al Barça. "El Mundo Deportivo", 27 de setembro de 1999, páxina 58CB Breogán - FC BarcelonaA FEB invita a participar nunha nova Liga EuropeaCharlie Bell na prensa estatalMáximos anotadores 2005Tempada 2005-06 : Tódolos Xogadores da Xornada""Non quero pensar nunha man negra, mais pregúntome que está a pasar""o orixinalRaúl López, orgulloso dos xogadores, presume da boa saúde económica do BreogánJulio González confirma que cesa como presidente del BreogánHomenaxe a Lisardo GómezA tempada do rexurdimento celesteEntrevista a Lisardo GómezEl COB dinamita el Pazo para forzar el quinto (69-73)Cafés Candelas, patrocinador del CB Breogán"Suso Lázare, novo presidente do Breogán"o orixinalCafés Candelas Breogán firma el mayor triunfo de la historiaEl Breogán realizará 17 homenajes por su cincuenta aniversario"O Breogán honra ao seu fundador e primeiro presidente"o orixinalMiguel Giao recibiu a homenaxe do PazoHomenaxe aos primeiros gladiadores celestesO home que nos amosa como ver o Breo co corazónTita Franco será homenaxeada polos #50anosdeBreoJulio Vila recibirá unha homenaxe in memoriam polos #50anosdeBreo"O Breogán homenaxeará aos seus aboados máis veteráns"Pechada ovación a «Capi» Sanmartín e Ricardo «Corazón de González»Homenaxe por décadas de informaciónPaco García volve ao Pazo con motivo do 50 aniversario"Resultados y clasificaciones""O Cafés Candelas Breogán, campión da Copa Princesa""O Cafés Candelas Breogán, equipo ACB"C.B. Breogán"Proxecto social"o orixinal"Centros asociados"o orixinalFicha en imdb.comMario Camus trata la recuperación del amor en 'La vieja música', su última película"Páxina web oficial""Club Baloncesto Breogán""C. B. Breogán S.A.D."eehttp://www.fegaba.com

            Vilaño, A Laracha Índice Patrimonio | Lugares e parroquias | Véxase tamén | Menú de navegación43°14′52″N 8°36′03″O / 43.24775, -8.60070

            Cegueira Índice Epidemioloxía | Deficiencia visual | Tipos de cegueira | Principais causas de cegueira | Tratamento | Técnicas de adaptación e axudas | Vida dos cegos | Primeiros auxilios | Crenzas respecto das persoas cegas | Crenzas das persoas cegas | O neno deficiente visual | Aspectos psicolóxicos da cegueira | Notas | Véxase tamén | Menú de navegación54.054.154.436928256blindnessDicionario da Real Academia GalegaPortal das Palabras"International Standards: Visual Standards — Aspects and Ranges of Vision Loss with Emphasis on Population Surveys.""Visual impairment and blindness""Presentan un plan para previr a cegueira"o orixinalACCDV Associació Catalana de Cecs i Disminuïts Visuals - PMFTrachoma"Effect of gene therapy on visual function in Leber's congenital amaurosis"1844137110.1056/NEJMoa0802268Cans guía - os mellores amigos dos cegosArquivadoEscola de cans guía para cegos en Mortágua, PortugalArquivado"Tecnología para ciegos y deficientes visuales. Recopilación de recursos gratuitos en la Red""Colorino""‘COL.diesis’, escuchar los sonidos del color""COL.diesis: Transforming Colour into Melody and Implementing the Result in a Colour Sensor Device"o orixinal"Sistema de desarrollo de sinestesia color-sonido para invidentes utilizando un protocolo de audio""Enseñanza táctil - geometría y color. Juegos didácticos para niños ciegos y videntes""Sistema Constanz"L'ocupació laboral dels cecs a l'Estat espanyol està pràcticament equiparada a la de les persones amb visió, entrevista amb Pedro ZuritaONCE (Organización Nacional de Cegos de España)Prevención da cegueiraDescrición de deficiencias visuais (Disc@pnet)Braillín, un boneco atractivo para calquera neno, con ou sen discapacidade, que permite familiarizarse co sistema de escritura e lectura brailleAxudas Técnicas36838ID00897494007150-90057129528256DOID:1432HP:0000618D001766C10.597.751.941.162C97109C0155020