SQL Server 2016 for JSON PATH returns a string instead of an array when using the case statement

Question

I am trying to create a JSON object containing an array using SQL Server 2016.

The source data for the array is JSON itself, so I use JSON_QUERYthe select statement inside, and the FOR JSON clause applies to the select statement.

Everything works beautifully until I complete the sentence JSON_QUERYin the instruction CASE(in some cases, the array should not be included, i.e. it should be zero).

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 expect it to return the following JSON:

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

FOR JSON PATH..., , , (.. nvarchar, JSON_QUERY, ).

, JSON?

Answer 1

case JSON_QUERY.

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

documentation JSON_QUERY " JSON". : " JSON nvarchar (max)". .

for xml json JSON JSON JSON .

CASE , , , CASE. JSON_QUERY , , JSON.

case .

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

JSON_QUERY, .

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

- SQL Server JSON JSON JSON.

CASE WHEN 1 is NOT NULL , SQL Server , , case .