SQL JSON_Query is not working as expected when using case with For Json Auto or For Json Path

Question

question

ErVishalM asked Oct 11, '19

SQL JSON_Query is not working as expected when using case with For Json Auto or For Json Path

Hi,

I am using SQL 2017 JSON features but little stuck in this. I am sorry if its a noobie question. You can see the output. I am not getting correct output when using case in JSON_QUERY.

create table #Color(ColorNameList varchar(max))
insert into #Color(ColorNameList)
values ('["Marine Green","Lime"]'), ('ALL')


Select * from #Color


Select ISJSON(ColorNameList) IsJsonData,ColorNameList from #Color


Select ISJSON(ColorNameList) IsJsonData,case when ISJSON(ColorNameList) = 1 then JSON_QUERY(ColorNameList) else ColorNameList end ColorNameList from #Color


Select ISJSON(ColorNameList) IsJsonData,case when ISJSON(ColorNameList) = 1 then JSON_QUERY(ColorNameList) else ColorNameList end ColorNameList from #Color for json auto

--Like this.
Select top 1 ISJSON(ColorNameList) IsJsonData,JSON_QUERY(ColorNameList) ColorNameList from #Color for json auto


--Output is [{"IsJsonData":1,"ColorNameList":"[\"Marine Green\",\"Lime\"]"},{"IsJsonData":0,"ColorNameList":"ALL"}]
--should be [{"IsJsonData":1,"ColorNameList":["Marine Green","Lime"]},{"IsJsonData":0,"ColorNameList":"ALL"}]
drop table #Color

sql server 2016 json sql-server 2017

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2019-10-11T09:41:44Z

ErVishalM answered Oct 11, '19

This is the answer for my question -
If anybody wants to check isnull into Json_Query then use isnull into JSON_Query like this.

Select JSON_Query(case when ISJSON(ColorNameList)=1 then ColorNameList else null end) from #Color

and if i want data without escape character like in my case value can be as array and without array also so in that case

select 
    ISJSON(ColorNameList) as IsJsonData, 
    JSON_QUERY( case when ISJSON(ColorNameList) = 1 then ColorNameList else NULL end) as ColorNameList, 
    case when ISJSON(ColorNameList) = 1 then NULL else ColorNameList end as ColorNameList 
from #Color 
for json auto

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question