SQL Server 2016 FOR JSON PATH returns string instead of array when using case statement

Question

I'm trying to build a JSON object that contains an array, using SQL Server 2016.

The source data for the array is itself JSON, so I'm using the JSON_QUERY inside a select statement, with the FOR JSON clause applied to the select statement.

Everything works beautifully until I wrap the JSON_QUERY clause in a CASE statement (in certain cases the array must not be included, i.e. must be null).

The following code illustrates the problem:

declare  @projects nvarchar(max) = '{"projects": [23439658267415,166584258534050]}'
declare @id bigint = 123

SELECT 
      [data.array1] = JSON_QUERY(@projects, '$.projects') -- returns an array - perfect.
    , [data.array2] = CASE WHEN 1 is NOT NULL 
                           THEN JSON_QUERY(@projects, '$.projects') 
                           ELSE NULL END -- returns an array - still good!
    , [data.array3] = CASE WHEN @id is NOT NULL
                           THEN JSON_QUERY(@projects, '$.projects') 
                           ELSE NULL END  -- why do I end up with a string in the JSON when I do this?
FOR JSON PATH, without_array_wrapper

This code returns the following JSON:

{  
   "data":{  
      "array1": [23439658267415,166584258534050],
      "array2": [23439658267415,166584258534050],
      "array3":"[23439658267415,166584258534050]"
   }
}

The problem is that the third 'array' is returned as a string object.

I would expect it to return the following JSON:

{  
   "data":{  
      "array1": [23439658267415,166584258534050],
      "array2": [23439658267415,166584258534050],
      "array3": [23439658267415,166584258534050]
   }
}

If I remove the FOR JSON PATH... clause, all columns returned by the query are identical (i.e. all three nvarchar values returned by the JSON_QUERY function are identical).

Why is this happening, how do I make it output an array in the final JSON?

Answer 1

Wrap the result from the case statement in a call to JSON_QUERY.

, [data.array3] = JSON_QUERY(
                            CASE WHEN @id is NOT NULL
                            THEN JSON_QUERY(@projects, '$.projects') 
                            ELSE NULL END
                            )

According to the documentation JSON_QUERY "Extracts an object or an array from a JSON string". Further down it says "Returns a JSON fragment of type nvarchar(max).". A bit confusing.

Doing a for xml json on a string value will give you a string value in the returned JSON string and when you do it on a JSON object you get the JSON object inlined in the resulting string value.

You can look at CASE as a function call with a return value automatically determined for you by looking at what values you are returning from the CASE. And since JSON_QUERY returns a string the case will return a string and the returned value will be a string value in JSON.

The case statement in the query plan looks like this.

<ScalarOperator ScalarString="CASE WHEN [@id] IS NOT NULL THEN json_query([@projects],N'$.projects') ELSE NULL END">

When you wrap the case in a call to JSON_QUERY it looks like this instead.

<ScalarOperator ScalarString="json_query(CASE WHEN [@id] IS NOT NULL THEN json_query([@projects],N'$.projects') ELSE NULL END)">
  <Intrinsic FunctionName="json_query">

By some kind of internal magic SQL Server recognize this as a JSON object instead of a string and inserts it into the resulting JSON string as a JSON value instead.

CASE WHEN 1 is NOT NULL works because SQL Server is smart enough to see that the case statement will always be true and is optimized away.