Microsoft SQL Server
Requêtes avec des données JSON

Utilisation de valeurs de JSON dans la requête

La fonction JSON_VALUE vous permet de prendre des données à partir du texte JSON sur le chemin spécifié en tant que second argument et d'utiliser cette valeur dans n'importe quelle partie de la requête select:

select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'

Utilisation des valeurs JSON dans les rapports

Une fois que les valeurs JSON sont extraites du texte JSON, vous pouvez les utiliser dans n'importe quelle partie de la requête. Vous pouvez créer un type de rapport sur les données JSON avec des regroupements, etc.:

select JSON_VALUE(Data, '$.Type') as type,
        AVG( cast(JSON_VALUE(Data, '$.ManufacturingCost') as float) ) as cost
from Product
group by JSON_VALUE(Data, '$.Type') 
having JSON_VALUE(Data, '$.Type') is not null

Filtrage du texte JSON incorrect à partir des résultats de la requête

Si du texte JSON n'est peut-être pas correctement formaté, vous pouvez supprimer ces entrées de la requête en utilisant la fonction ISJSON.

select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'
and ISJSON(Data) > 0

Mettre à jour la valeur dans la colonne JSON

La fonction JSON_MODIFY peut être utilisée pour mettre à jour une valeur sur un chemin. Vous pouvez utiliser cette fonction pour modifier la valeur d'origine de la cellule JSON dans l'instruction UPDATE:

update Product
set Data = JSON_MODIFY(Data, '$.Price', 24.99)
where ProductID = 17;

La fonction JSON_MODIFY mettra à jour ou créera une clé de prix (si elle n'existe pas). Si la nouvelle valeur est NULL, la clé sera supprimée. La fonction JSON_MODIFY traitera la nouvelle valeur comme une chaîne (échappez les caractères spéciaux, enveloppez-la avec des guillemets pour créer une chaîne JSON correcte). Si votre nouvelle valeur est un fragment JSON, vous devez l’emballer avec la fonction JSON_QUERY:

update Product
set Data = JSON_MODIFY(Data, '$.tags', JSON_QUERY('["promo","new"]'))
where ProductID = 17;

La fonction JSON_QUERY sans second paramètre se comporte comme un "cast to JSON". Puisque le résultat de JSON_QUERY est un fragment JSON valide (objet ou tableau), JSON_MODIFY n'échappera pas à cette valeur lorsque modifie l'entrée JSON.

Ajouter une nouvelle valeur au tableau JSON

La fonction JSON_MODIFY peut être utilisée pour ajouter une nouvelle valeur à un tableau dans JSON:

update Product
set Data = JSON_MODIFY(Data, 'append $.tags', "sales")
where ProductID = 17;

Une nouvelle valeur sera ajoutée à la fin du tableau ou un nouveau tableau avec une valeur ["sales"] sera créé. La fonction JSON_MODIFY traitera la nouvelle valeur comme une chaîne (échappez les caractères spéciaux, enveloppez-la avec des guillemets pour créer une chaîne JSON correcte). Si votre nouvelle valeur est un fragment JSON, vous devez l’emballer avec la fonction JSON_QUERY:

update Product
set Data = JSON_MODIFY(Data, 'append $.tags', JSON_QUERY('{"type":"new"}'))
where ProductID = 17;

La fonction JSON_QUERY sans second paramètre se comporte comme un "cast to JSON". Puisque le résultat de JSON_QUERY est un fragment JSON valide (objet ou tableau), JSON_MODIFY n'échappera pas à cette valeur lorsque modifie l'entrée JSON.

Table JOIN avec une collection JSON interne

Si vous avez une "table enfant" formatée en tant que collection JSON et stockée dans la ligne en tant que colonne JSON, vous pouvez décompresser cette collection, la transformer en table et la joindre à la ligne parente. Au lieu de l'opérateur JOIN standard, vous devez utiliser CROSS APPLY. Dans cet exemple, les composants du produit sont mis en forme en tant que collection d'objets JSON dans et stockés dans la colonne Données:

select ProductID, Name, Size, Price, Quantity, PartName, Code
from Product
    CROSS APPLY OPENJSON(Data, '$.Parts') WITH (PartName varchar(20), Code varchar(5))

Le résultat de la requête est équivalent à la jointure entre les tables Product et Part.

Recherche de lignes contenant une valeur dans le tableau JSON

Dans cet exemple, le tableau de balises peut contenir différents mots-clés tels que ["promo", "sales"], nous pouvons donc ouvrir ce tableau et les valeurs de filtre:

select ProductID, Name, Color, Size, Price, Quantity
from Product
    CROSS APPLY OPENJSON(Data, '$.Tags') 
where value = 'sales'

OPENJSON ouvrira la collection interne de balises et la renverra sous forme de tableau. Ensuite, nous pouvons filtrer les résultats par une valeur dans la table.