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.

Your version of SQL Server will be important as well. Lots of json functionality available in 2016 and above. It can be done in just about any version of SQL (it's just character data), but the answer will be different for different versions. — jamie, Jun 11 '19 at 00:00
Sample data is best served as [DDL](https://en.wikipedia.org/wiki/Data_definition_language) + [DML](https://en.wikipedia.org/wiki/Data_manipulation_language). Please [edit] your question to include it, your current attempt and your desired results. For more details, [read this.](https://dba.meta.stackexchange.com/questions/2976/help-me-write-this-query-in-sql) — Zohar Peled, Jun 11 '19 at 05:11

score 2 · Answer 1 · answered Jun 11 '19 at 06:50

I did not find a good solution and would be happy, if someone comes around with a better one than this hack:

DECLARE @tbl TABLE(ID INT IDENTITY,Arguments NVARCHAR(MAX));
INSERT INTO @tbl VALUES
 (NULL)
,('plain text')
,('[{"id":"1"},{"id":"2"}]');

SELECT t1.ID
      ,(SELECT Arguments FROM @tbl t2 WHERE t2.ID=t1.ID AND ISJSON(Arguments)=0) Arguments
      ,(SELECT JSON_QUERY(Arguments) FROM @tbl t2 WHERE t2.ID=t1.ID AND ISJSON(Arguments)=1) ArgumentsJSON
FROM @tbl t1 
FOR JSON PATH;

As NULL-values are omitted, you will always find eiter Arguments or ArgumentsJSON in your final result. Treating this JSON as NVARCHAR(MAX) you can use REPLACE to rename all to the same Arguments.

The problem seems to be, that you cannot include two columns with the same name within your SELECT, but each column must have a predictable type. This depends on the order you use in CASE (or COALESCE). If the engine thinks "Okay, here's text", all will be treated as text and your JSON is escaped. But if the engine thinks "Okay, some JSON", everything is handled as JSON and will break if this JSON is not valid.

With FOR XML PATH there are some tricks with column namig (such as [*], [node()] or even twice the same within one query), but FOR JSON PATH is not that powerfull...

THanks for confirming what I was noticing with this "hack". I had pretty much concluded the same. Here is a modification that merges the two columns into one: — Jon A Stocksdale, Sep 15 '22 at 16:00

Zhorov · Accepted Answer · 2019-06-11T13:31:32.933

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.

Clever work-around, thanks! It's too bad that SQL Server doesn't have a way to deal with this more cleanly. — N8allan, Jun 11 '19 at 21:12

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

2 Answers2