Is there a way to return either a string or embedded JSON using FOR JSON?

Question

I have a nvarchar column that I would like to return embedded in my JSON results if the contents is valid JSON, or as a string otherwise.

Here is what I've tried:

select
  (
    case when IsJson(Arguments) = 1 then 
      Json_Query(Arguments) 
    else 
      Arguments 
    end
  ) Results
  from Unit
  for json path

This always puts Results into a string.

The following works, but only if the attribute contains valid JSON:

select
    (
      Json_Query(
        case when IsJson(Arguments) = 1 then 
          Arguments 
        else 
          '"' + String_escape(IsNull(Arguments, ''), 'json') + '"' end
      )
    ) Results
    from Unit
    for json path

If Arguments does not contain a JSON object a runtime error occurs.

Update: Sample data:

Arguments
---------
{ "a": "b" }
Some text

Update: any version of SQL Server will do. I'd even be happy to know that it's coming in a beta or something.

Answer 1

When you say that your statement "... always puts Results into a string.", you probably mean that when JSON is stored in a text column, FOR JSON escapes this text. Of course, if you want to return an unescaped JSON text, you need to use JSON_QUERY function only for your valid JSON text.

Next is a small workaround (based on FOR JSON and string manipulation), that may help to solve your problem.

Table:

CREATE TABLE #Data (
   Arguments nvarchar(max)
)
INSERT INTO #Data 
   (Arguments)
VALUES
   ('{"a": "b"}'),
   ('Some text'),
   ('{"c": "d"}'),
   ('{"e": "f"}'),
   ('More[]text')

Statement:

SELECT CONCAT(N'[', j1.JsonOutput, N',', j2.JsonOutput, N']')
FROM 
(
   SELECT JSON_QUERY(Arguments) AS Results
   FROM #Data
   WHERE ISJSON(Arguments) = 1
   FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) j1 (JsonOutput),
(
   SELECT STRING_ESCAPE(ISNULL(Arguments, ''), 'json') AS Results
   FROM #Data
   WHERE ISJSON(Arguments) = 0
   FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) j2 (JsonOutput)

Output:

[{"Results":{"a": "b"}},{"Results":{"c": "d"}},{"Results":{"e": "f"}},{"Results":"Some text"},{"Results":"More[]text"}]

Notes:

One disadvantage here is that the order of the items in the generated output is not the same as in the table.