Prise en main des fonctionnalités JSON

S’applique à :Base de données SQL Azure SQL Managed Instance Base de données Azure SQL dans Fabric

Azure SQL Database, SQL Database dans Microsoft Fabric et Azure SQL Managed Instance vous permettent d’analyser et d’interroger des données représentées au format JSON (JavaScript Object Notation) et d’exporter vos données relationnelles sous forme de texte JSON. Les scénarios JSON suivants sont disponibles :

Conversion de données relationnelles au format JSON à l’aide de la clause FOR JSON.
Utilisation de données JSON
Interrogation de données JSON à l’aide de fonctions scalaires JSON.
Conversion de JSON au format tabulaire à l’aide de la fonction OPENJSON.

Mettre en forme des données relationnelles au format JSON

Si vous disposez d’un service web qui prélève des données de la couche de base de données et fournit une réponse au format JSON, ou d’infrastructures ou bibliothèques JavaScript côté client qui acceptent des données au format JSON, vous pouvez convertir le contenu de votre base de données au format JSON directement dans une requête SQL. Vous n’avez plus besoin d’écrire du code d’application qui met en forme les résultats au format JSON, ou d’inclure une bibliothèque de sérialisation JSON pour convertir les résultats de requête tabulaire, puis sérialiser des objets au format JSON. Au lieu de cela, vous pouvez utiliser la FOR JSON clause pour mettre en forme les résultats de requête SQL en tant que JSON et l’utiliser directement dans votre application.

Dans l’exemple suivant, les lignes de la table Sales.Customer sont converties au format JSON à l’aide de la clause FOR JSON :

select CustomerName, PhoneNumber, FaxNumber
from Sales.Customers
FOR JSON PATH

La FOR JSON PATH clause met en forme les résultats de la requête sous forme de texte JSON. Les noms des colonnes sont utilisés en tant que clés, tandis que les valeurs des cellules sont générées en tant que valeurs JSON :

[
{"CustomerName":"Eric Torres","PhoneNumber":"(307) 555-0100","FaxNumber":"(307) 555-0101"},
{"CustomerName":"Cosmina Vlad","PhoneNumber":"(505) 555-0100","FaxNumber":"(505) 555-0101"},
{"CustomerName":"Bala Dixit","PhoneNumber":"(209) 555-0100","FaxNumber":"(209) 555-0101"}
]

Le jeu de résultats est mis en forme de tableau JSON, où chaque ligne est formatée en tant qu’objet JSON séparé.

PATH indique que vous pouvez personnaliser le format de sortie de votre résultat JSON à l’aide de la notation par points dans les alias de colonne. La requête suivante modifie le nom de la CustomerName clé au format JSON de sortie et place les numéros de téléphone et de télécopie dans le Contact sous-objet :

select CustomerName as Name, PhoneNumber as [Contact.Phone], FaxNumber as [Contact.Fax]
from Sales.Customers
where CustomerID = 931
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Le résultat de cette requête ressemble à ceci :

{
    "Name":"Nada Jovanovic",
    "Contact":{
           "Phone":"(215) 555-0100",
           "Fax":"(215) 555-0101"
    }
}

Dans cet exemple, nous renvoyons un objet JSON au lieu d’un tableau, en spécifiant l’option WITHOUT_ARRAY_WRAPPER. Vous pouvez utiliser cette option si vous savez que vous retournez un objet unique en tant que résultat de requête.

La valeur principale de la FOR JSON clause est qu’elle vous permet de retourner des données hiérarchiques complexes à partir de votre base de données mise en forme en tant qu’objets JSON imbriqués ou tableaux. L’exemple suivant montre comment inclure les lignes de la table Orders appartenant à Customer sous forme d’un tableau imbriqué de Orders :

select CustomerName as Name, PhoneNumber as Phone, FaxNumber as Fax,
        Orders.OrderID, Orders.OrderDate, Orders.ExpectedDeliveryDate
from Sales.Customers Customer
    join Sales.Orders Orders
        on Customer.CustomerID = Orders.CustomerID
where Customer.CustomerID = 931
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER

Au lieu d’envoyer des requêtes distinctes pour obtenir Customer des données, puis pour extraire une liste de données connexes Orders, vous pouvez obtenir toutes les données nécessaires avec une seule requête, comme illustré dans l’exemple de sortie suivant :

{
  "Name":"Nada Jovanovic",
  "Phone":"(215) 555-0100",
  "Fax":"(215) 555-0101",
  "Orders":[
    {"OrderID":382,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
    {"OrderID":395,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
    {"OrderID":1657,"OrderDate":"2013-01-31","ExpectedDeliveryDate":"2013-02-01"}
  ]
}

Utiliser des données JSON

Si vous n’avez pas de données strictement structurées, si vous avez des sous-objets, tableaux ou données hiérarchiques complexes, ou si vos structures de données évoluent au fil du temps, le format JSON peut vous aider à représenter toute structure de données complexe.

JSON est un format texte utilisable comme tout autre type de chaîne. Vous pouvez envoyer ou stocker des données JSON en tant que nvarchar standard :

CREATE TABLE Products (
  Id int identity primary key,
  Title nvarchar(200),
  Data nvarchar(max)
)
go
CREATE PROCEDURE InsertProduct(@title nvarchar(200), @json nvarchar(max))
AS BEGIN
    insert into Products(Title, Data)
    values(@title, @json)
END

Les données JSON utilisées dans cet exemple sont représentées à l’aide du type nvarchar(MAX). Des données JSON peuvent être insérées dans cette table ou fournies en tant qu’argument de la procédure stockée à l’aide de la syntaxe Transact-SQL standard, comme indiqué dans l’exemple suivant :

EXEC InsertProduct 'Toy car', '{"Price":50,"Color":"White","tags":["toy","children","games"]}'

Tout langage ou bibliothèque côté client qui fonctionne avec des données de chaîne fonctionne également avec des données JSON. Des données JSON peuvent être stockées dans toute table prenant en charge le type nvarchar, par exemple, une table mémoire optimisée ou une table avec version système. JSON n’introduit aucune contrainte, que ce soit dans le code côté client ou dans la couche de base de données.

Interroger des données JSON

Si vous avez des données au format JSON stockées dans des tables, les fonctions JSON vous permettent d’utiliser ces données dans n’importe quelle requête SQL.

Les fonctions JSON vous permettent de traiter des données au format JSON comme tout autre type de données SQL. Vous pouvez facilement extraire des valeurs du texte JSON et utiliser des données JSON dans toute requête :

select Id, Title, JSON_VALUE(Data, '$.Color'), JSON_QUERY(Data, '$.tags')
from Products
where JSON_VALUE(Data, '$.Color') = 'White'

update Products
set Data = JSON_MODIFY(Data, '$.Price', 60)
where Id = 1

La JSON_VALUE fonction extrait une valeur du texte JSON stocké dans la colonne Données. Cette fonction utilise un chemin d’accès de type JavaScript pour faire référence à une valeur en texte JSON à extraire. La valeur extraite peut être utilisée dans toute partie de la requête SQL.

La JSON_QUERY fonction est similaire à JSON_VALUE. Contrairement à JSON_VALUE, cette fonction extrait des sous-objets complexes tels que des tableaux ou des objets présents dans un texte JSON.

La JSON_MODIFY fonction vous permet de spécifier le chemin d’accès de la valeur dans le texte JSON qui doit être mis à jour, ainsi qu’une nouvelle valeur qui remplacera l’ancienne. Vous pouvez ainsi facilement mettre à jour le texte JSON sans réanalyser la structure entière.

Étant donné que JSON est stocké dans un texte standard, il n’est nullement garanti que le format des valeurs stockées dans les colonnes de texte est correct. Vous pouvez vérifier que le texte stocké dans une colonne JSON est correctement mis en forme en utilisant des contraintes de vérification standard et la fonction ISJSON :

ALTER TABLE Products
    ADD CONSTRAINT [Data should be formatted as JSON]
        CHECK (ISJSON(Data) > 0)

Si le texte d’entrée est correctement mis en forme JSON, la ISJSON fonction retourne la valeur 1. À chaque insertion ou mise à jour de la colonne JSON, cette contrainte vérifie que nouvelle valeur de texte n’est pas dans du JSON mal formé.

Transformer JSON en format tabulaire

Vous pouvez convertir des collections JSON en format tabulaire, ainsi que de charger ou d’interroger des données JSON.

OPENJSON est une fonction T-SQL table qui analyse le texte JSON, localise un tableau d’objets JSON, effectue une itération dans les éléments du tableau et retourne une ligne dans le résultat de sortie pour chaque élément du tableau.

Dans l’exemple, nous pouvons spécifier où localiser le tableau JSON qui doit être ouvert (dans le $.Orders chemin), quelles colonnes doivent être retournées par conséquent et où trouver les valeurs JSON qui seront retournées en tant que cellules.

Nous pouvons transformer un tableau JSON dans la variable @orders en un ensemble de lignes, analyser ce résultat ou insérer des lignes dans une table standard :

CREATE PROCEDURE InsertOrders(@orders nvarchar(max))
AS BEGIN

    insert into Orders(Number, Date, Customer, Quantity)
    select Number, Date, Customer, Quantity
    FROM OPENJSON (@orders)
     WITH (
            Number varchar(200),
            Date datetime,
            Customer varchar(200),
            Quantity int
     )
END

La collection de commandes mise en forme de tableau JSON et fournie en tant que paramètre à la procédure stockée peut être analysée et insérée dans la table Orders.

Données JSON dans SQL Server

Commentaires

Cette page a-t-elle été utile ?

Last updated on 2025-11-20