SQL Server 2016 FOR JSON PATH возвращает строку вместо массива при использовании оператора case

Я пытаюсь построить объект JSON, содержащий массив, используя SQL Server 2016.

Исходными данными для массива являются сами JSON, поэтому я использую JSON_QUERY внутри оператора select, а к оператору select применяется предложение FOR JSON.

Все работает прекрасно, пока я не оберну предложение JSON_QUERY В оператор CASE (в некоторых случаях массив не должен быть включен, т. е. должен быть null).

Следующий код иллюстрирует проблему: 

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

Это код возвращает следующий JSON: 

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

Проблема заключается в том, что третий "массив" возвращается в виде строкового объекта.

Я ожидал бы, что он вернет следующий JSON: 

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

Если я удалю предложение FOR JSON PATH..., все столбцы, возвращаемые запросом, будут идентичны (т. е. все три значения nvarchar, возвращаемые функцией JSON_QUERY, будут идентичны).

Почему это происходит, как я могу заставить его вывести массив в конечном JSON?

1 4
arrays json sql-server tsql sql-server-2016

1 ответ:

Оберните результат из оператора case в вызов JSON_QUERY.

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

Согласно документации JSON_QUERY "извлекает объект или массив из строки JSON". Далее он говорит: "возвращает фрагмент JSON типа nvarchar (max).". Немного запутанный.

Выполнение for xml json на строковом значении даст вам строковое значение в возвращаемой строке JSON, и когда вы делаете это на объекте JSON, вы получаете объект JSON, встроенный в результирующее строковое значение.

Вы можете посмотрите на CASE как на вызов функции с возвращаемым значением, автоматически определяемым для вас, смотря на то, какие значения вы возвращаете из CASE. И так как JSON_QUERY возвращает строку, то case возвращает строку, а возвращаемое значение будет строковым значением в 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 всегда будет истинным и оптимизирован.

3