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

Other SQL Server Topics
1

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
2

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
3

ErVishalM,

When I work with JSON data, I usually like to map it out into a table. Are you aware that you can do this and then check if it is NULL or whatever:

DECLARE @json_string NVARCHAR(MAX) = 
 N'{"Customers":
    [{"Id":1,"Name":"Basavaraj",
            "Address":{"State":"KA","Country":"India"}},
     {"Id":2,"Name":"Kalpana",
            "Address":{"State":"NY","Country":"United State"}}
    ]
 }'
SELECT *
FROM OPENJSON(@json_string,'$.Customers')
WITH(EmployeeId INT '$.Id', EmployeeName Varchar(100) '$.Name',
        DOB Varchar(10) '$.Dob')
1 Like