SQL Server 2016 pour JSON PATH renvoie une string au lieu d'un tableau lors de l'utilisation de l'instruction de requête

J'essaye de build un object JSON qui contient un tableau, en utilisant SQL Server 2016.

Les données source du tableau sont elles-mêmes JSON, donc j'utilise JSON_QUERY dans une instruction select, avec la clause FOR JSON appliquée à l'instruction select.

Tout fonctionne à merveille jusqu'à ce que JSON_QUERY clause JSON_QUERY dans une instruction CASE (dans certains cas, le tableau ne doit pas être inclus, c'est-à-dire doit être nul).

Le code suivant illustre le problème:

 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 ssortingng in the JSON when I do this? FOR JSON PATH, without_array_wrapper

Ce code renvoie le JSON suivant:

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

Le problème est que le troisième 'tableau' est renvoyé en tant qu'object string.

Je m'attendrais à ce qu'il renvoie le JSON suivant:

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

Si je supprime la clause FOR JSON PATH... , toutes les colonnes renvoyées par la requête sont identiques (les trois valeurs nvarchar renvoyées par la fonction JSON_QUERY sont identiques).

Pourquoi cela se produit-il, comment puis-je faire sortir un tableau dans le JSON final?

Enveloppez le résultat de l'instruction case dans un appel à JSON_QUERY .

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

Selon la documentation JSON_QUERY "Extrait un object ou un tableau d'une string JSON". Plus bas, il indique "Retourne un fragment JSON de type nvarchar (max).". Un peu déroutant.

Faire un for xml json sur une valeur de string vous donnera une valeur de string dans la string JSON returnnée et lorsque vous le faites sur un object JSON, vous obtenez l'object JSON inline dans la valeur de string résultante.

Vous pouvez regarder CASE comme un appel de fonction avec une valeur de return déterminée automatiquement pour vous en regardant les valeurs que vous renvoyez du CAS. Et puisque JSON_QUERY renvoie une string, la requête returnnera une string et la valeur returnnée sera une valeur de string dans JSON.

L'instruction case dans le plan de requête ressemble à ceci.

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

Lorsque vous encapsulez le cas dans un appel à JSON_QUERY, il ressemble à ceci.

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

Par une sorte de magie interne, SQL Server le reconnaît comme un object JSON au lieu d'une string et l'insère dans la string JSON résultante en tant que valeur JSON à la place.

CASE WHEN 1 is NOT NULL fonctionne parce que SQL Server est assez intelligent pour voir que l'instruction case sera toujours vraie et optimisée.