Prevent double-escaped JSON in FOR JSON output in SQL

Question

I have a small problem in my case because in my case a column can contain text 'John' directly or text as array '["John","Smith"]' both. So how can I prevent double-escaped JSON in FOR JSON output? I think I am doing something wrong here. Please check my example:

Create table #jsonTest(NameList varchar(max))
insert into #jsonTest(NameList)
select '["John","Smith"]'

Now if I want its output it will give correct output from this (without escape character):

select JSON_QUERY(NameList) NameList from #jsonTest for json auto

Output:

[{"NameList":["John","Smith"]}]

Simple text example:

truncate table #jsonTest
insert into #jsonTest(NameList)
Select 'John'

Now for this I have to change my select query for the correct output because JSON_QUERY, as mentioned, it only returns objects and arrays. So i've changed it to this:

select case when ISJSON(NameList) = 1 then JSON_QUERY(NameList) else NameList end NameList from #jsonTest for json auto

Output:

[{"NameList":"John"}]

Now It will give correct output for now but if I insert previous data again and try upper select query

truncate table #jsonTest
insert into #jsonTest(NameList)
select '["John","Smith"]'

select case when ISJSON(NameList) = 1 then JSON_QUERY(NameList) else NameList end NameList from #jsonTest for json auto

Output:

[{"NameList":"[\"John\",\"Smith\"]"}]

then it is giving escape characters in output. What is wrong in the code?

Answer 1

This behaviour is explained in the documentation - If the source data contains special characters, the FOR JSON clause escapes them in the JSON output with '\'. Of course, as you already know, when JSON_QUERY() is used with FOR JSON AUTO, FOR JSON doesn't escape special characters in the JSON_QUERY return value.

Your problem is the fact, that your data is not always a JSON. So, one possible approach is to generate a statement with duplicate column names (NameList). By default FOR JSON AUTO does not include NULL values in the output, so the result is the expected JSON. Just note, that you must not use INCLUDE_NULL_VALUES in the statement or the final JSON will contain duplicate keys.

Table:

CREATE TABLE #jsonTest(NameList varchar(max))
insert into #jsonTest(NameList)
select '["John","Smith"]'
insert into #jsonTest(NameList)
Select 'John'

Statement:

SELECT
   JSON_QUERY(CASE WHEN ISJSON(NameList) = 1 THEN JSON_QUERY(NameList) END) AS NameList,
   CASE WHEN ISJSON(NameList) = 0 THEN NameList END AS NameList
FROM #jsonTest 
FOR JSON AUTO

Result:

[{"NameList":["John","Smith"]},{"NameList":"John"}]