CODEWITHSUNDEEP
sql-servert-sqlfor-json
N8allan
N8allan

Reputation: 2268

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

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.

Upvotes: 2

Views: 884

Answers (2)

Zhorov
Zhorov

Reputation: 29993

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.

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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...

Upvotes: 2

Related Questions