Données JSON dans SQL Server
S’applique à :
SQL Server 2016 (13.x) et versions ultérieures Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics
JSON est un format de données textuelles répandu qui est utilisé pour échanger des données dans des applications mobiles et web modernes. JSON est aussi utilisé pour stocker des données non structurées dans des fichiers journaux ou des bases de données NoSQL telles que Microsoft Azure Cosmos DB. Nombreux sont les services web REST qui retournent des résultats sous forme de texte JSON ou qui acceptent des données au format JSON. Par exemple, la plupart des services Azure, tels que Recherche Azure, Stockage Azure et Azure Cosmos DB, ont des points de terminaison REST qui retournent ou utilisent des données JSON. JSON est aussi le principal format d’échange de données entre les pages web et les serveurs web en utilisant des appels AJAX.
Les fonctions JSON, d’abord introduites dans SQL Server 2016 (13.x), vous permettent de combiner noSQL et concepts relationnels dans la même base de données. Vous pouvez combiner des colonnes relationnelles classiques avec des colonnes qui contiennent des documents mis en forme sous forme de texte JSON dans le même tableau, analyser et importer des documents JSON dans des structures relationnelles ou mettre en forme des données relationnelles dans du texte JSON.
Remarque
La prise en charge JSON nécessite un niveau de compatibilité de base de données 130 ou supérieur.
Voici un exemple de texte JSON :
[
{
"name": "John",
"skills": [ "SQL", "C#", "Azure" ]
},
{
"name": "Jane",
"surname": "Doe"
}
]
À l’aide des fonctions et des opérateurs SQL Server intégrés, vous pouvez effectuer les opérations suivantes avec le texte JSON :
- Analyser du texte JSON et lire ou modifier des valeurs
- Transformer des tableaux d’objets JSON au format table
- Exécuter une requête Transact SQL sur les objets JSON convertis
- Mettre en forme les résultats des requêtes Transact-SQL au format JSON
Principales fonctionnalités JSON de SQL Server et de SQL Database
Les sections suivantes abordent les fonctionnalités clés fournies par SQL Server avec sa prise en charge intégrée de JSON.
Extraire les valeurs de texte JSON et les utiliser dans les requêtes
Si vous avez stocké du texte JSON dans des tables de base de données, vous pouvez lire ou modifier les valeurs présentes dans le texte JSON à l’aide des fonctions intégrées suivantes :
- ISJSON (Transact-SQL) vérifie si une chaîne contient un JSON valide.
- JSON_VALUE (Transact-SQL) extrait une valeur scalaire d’une chaîne JSON.
- JSON_QUERY (Transact-SQL) extrait un objet ou un tableau d’une chaîne JSON.
- JSON_MODIFY (Transact-SQL) change une valeur dans une chaîne JSON.
Exemple
Dans l’exemple suivant, la requête utilise des données relationnelles et JSON (stockées dans une colonne nommée jsonCol) à partir d’une table appelée People:
SELECT Name,
Surname,
JSON_VALUE(jsonCol, '$.info.address.PostCode') AS PostCode,
JSON_VALUE(jsonCol, '$.info.address."Address Line 1"')
+ ' ' + JSON_VALUE(jsonCol, '$.info.address."Address Line 2"') AS Address,
JSON_QUERY(jsonCol, '$.info.skills') AS Skills
FROM People
WHERE ISJSON(jsonCol) > 0
AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade'
AND STATUS = 'Active'
ORDER BY JSON_VALUE(jsonCol, '$.info.address.PostCode');
Les applications et les outils ne font aucune différence entre les valeurs tirées de colonnes de table scalaire et les valeurs tirées de colonnes JSON. Vous pouvez utiliser des valeurs de texte JSON dans n’importe quelle partie de requête Transact-SQL (notamment les clauses WHERE, ORDER BY ou GROUP BY, les agrégats de fenêtre, etc.). Les fonctions JSON utilisent une syntaxe de type JavaScript pour faire référence aux valeurs contenues dans du texte JSON.
Pour plus d’informations, consultez Validate, Query et Change JSON Data with Built-in Functions (SQL Server), JSON_VALUE (Transact-SQL) et JSON_QUERY (Transact-SQL).
Modifier les valeurs JSON
Si vous devez modifier des parties de texte JSON, vous pouvez utiliser la fonction JSON_MODIFY (Transact-SQL) pour mettre à jour une valeur de propriété de chaîne JSON et retourner la chaîne JSON mise à jour. L’exemple suivant met à jour la valeur d’une propriété dans une variable contenant du texte JSON :
DECLARE @json NVARCHAR(MAX);
SET @json = '{"info": {"address": [{"town": "Belgrade"}, {"town": "Paris"}, {"town":"Madrid"}]}}';
SET @json = JSON_MODIFY(@json, '$.info.address[1].town', 'London');
SELECT modifiedJson = @json;
Voici le jeu de résultats obtenu.
{"info":{"address":[{"town":"Belgrade"},{"town":"London"},{"town":"Madrid"}]}}
Convertir des collections JSON en ensemble de lignes
Vous n’avez pas besoin d’un langage de requête personnalisé pour interroger les données JSON dans SQL Server. Pour interroger des données JSON, vous pouvez utiliser le langage T-SQL standard. Si vous devez créer une requête ou un rapport sur des données JSON, vous pouvez facilement convertir des données JSON en lignes et colonnes en appelant la OPENJSON fonction d’ensemble de lignes. Pour plus d’informations, consultez Analyse et transformation de données JSON avec OPENJSON.
L’exemple suivant appelle OPENJSON et transforme le tableau d’objets stockés dans la @json variable dans un ensemble de lignes qui peut être interrogé avec une instruction Transact-SQL SELECT standard :
DECLARE @json NVARCHAR(MAX);
SET @json = N'[
{"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
{"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';
SELECT *
FROM OPENJSON(@json) WITH (
id INT 'strict $.id',
firstName NVARCHAR(50) '$.info.name',
lastName NVARCHAR(50) '$.info.surname',
age INT,
dateOfBirth DATETIME2 '$.dob'
);
Voici le jeu de résultats obtenu.
| id | firstName | lastName | age | dateOfBirth |
|---|---|---|---|---|
| 2 | John | Smith | 25 | |
| 5 | Jane | Smith | 2005-11-04T12:00:00 |
OPENJSON transforme le tableau d’objets JSON en une table dans laquelle chaque objet est représenté sous la forme d’une ligne et les paires clé/valeur sont retournées sous forme de cellules. La sortie respecte les règles suivantes :
OPENJSONconvertit les valeurs JSON en types spécifiés dans laWITHclause.OPENJSONpeut gérer les paires clé/valeur plate et les objets imbriqués et organisés hiérarchiquement.- Vous n’êtes pas obligé de retourner tous les champs contenus dans le texte JSON.
- Si les valeurs JSON n’existent pas,
OPENJSONretourneNULLdes valeurs. - Vous pouvez éventuellement spécifier un chemin après la spécification du type pour référencer une propriété imbriquée ou une propriété avec un autre nom.
- Le préfixe facultatif
strictdans le chemin d’accès spécifie que les valeurs des propriétés spécifiées doivent exister dans le texte JSON.
Pour plus d’informations, consultez Analyse et transformation de données JSON avec OPENJSON et OPENJSON (Transact-SQL).
Les documents JSON peuvent avoir des sous-éléments et des données hiérarchiques qui ne peuvent pas être directement mappées dans les colonnes relationnelles standard. Dans ce cas, vous pouvez aplatir la hiérarchie JSON en joignant l’entité parente à des sous-tableaux.
Dans l’exemple suivant, le deuxième objet dans le tableau possède un sous-tableau qui représente les compétences (skills) de la personne. Chaque sous-objet peut être analysé à l’aide d’un appel de fonction OPENJSON supplémentaire :
DECLARE @json NVARCHAR(MAX);
SET @json = N'[
{"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
{"id": 5, "info": {"name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"]}, "dob": "2005-11-04T12:00:00"}
]';
SELECT id,
firstName,
lastName,
age,
dateOfBirth,
skill
FROM OPENJSON(@json) WITH (
id INT 'strict $.id',
firstName NVARCHAR(50) '$.info.name',
lastName NVARCHAR(50) '$.info.surname',
age INT,
dateOfBirth DATETIME2 '$.dob',
skills NVARCHAR(MAX) '$.info.skills' AS JSON
)
OUTER APPLY OPENJSON(skills) WITH (skill NVARCHAR(8) '$');
Le skills tableau est retourné dans le premier OPENJSON en tant que fragment de texte JSON d’origine et transmis à une autre OPENJSON fonction à l’aide APPLY d’un opérateur. La deuxième OPENJSON fonction analyse le tableau JSON et retourne des valeurs de chaîne en tant qu’ensemble de lignes à colonne unique qui sera joint au résultat du premier OPENJSON.
Voici le jeu de résultats obtenu.
| id | firstName | lastName | age | dateOfBirth | skill |
|---|---|---|---|---|---|
| 2 | John | Smith | 25 | ||
| 5 | Jane | Smith | 2005-11-04T12:00:00 | SQL | |
| 5 | Jane | Smith | 2005-11-04T12:00:00 | C# | |
| 5 | Jane | Smith | 2005-11-04T12:00:00 | Azure |
OUTER APPLY OPENJSON joint une entité de premier niveau avec un sous-tableau et retourne un jeu de résultats aplatissement. En raison de JOIN, la deuxième ligne est répétée pour chaque compétence.
Convertir des données SQL Server au format JSON ou exporter des données JSON
Remarque
Ni la conversion des données Azure Synapse Analytics au format JSON ni l’exportation au format JSON ne sont prises en charge.
Mettez en forme les données SQL Server ou les résultats des requêtes SQL en tant que JSON en ajoutant la FOR JSON clause à une SELECT instruction. Permet FOR JSON de déléguer la mise en forme de la sortie JSON de vos applications clientes à SQL Server. Pour plus d’informations, consultez Mettre en forme les résultats de requête au format JSON avec FOR JSON.
L’exemple suivant utilise le mode PATH avec la FOR JSON clause :
SELECT id,
firstName AS "info.name",
lastName AS "info.surname",
age,
dateOfBirth AS dob
FROM People
FOR JSON PATH;
La FOR JSON clause met en forme les résultats SQL sous forme de texte JSON qui peuvent être fournis à n’importe quelle application qui comprend JSON. L’option PATH utilise des alias séparés par des points dans la clause SELECT pour imbriquer des objets dans les résultats de requête.
Voici le jeu de résultats obtenu.
[
{
"id": 2,
"info": {
"name": "John",
"surname": "Smith"
},
"age": 25
},
{
"id": 5,
"info": {
"name": "Jane",
"surname": "Smith"
},
"dob": "2005-11-04T12:00:00"
}
]
Pour plus d’informations, consultez Mettre en forme les résultats de la requête au format JSON avec la clause FOR JSON et FOR (Transact-SQL).
Cas d’usage pour les données JSON dans SQL Server
Dans SQL Server et Azure SQL Database, la prise en charge de JSON vous permet de combiner des concepts relationnels et NoSQL. Vous pouvez facilement convertir des données relationnelles en données semi-structurées, et inversement. JSON n’est pas un remplacement pour les modèles relationnels existants. Voici quelques cas d’usage où la prise en charge JSON se révèle utile dans SQL Server et SQL Database.
Simplifier les modèles de données complexes
Il est préférable de dénormaliser votre modèle de données avec des champs JSON plutôt que d’utiliser plusieurs tables enfants.
Stocker des données de vente au détail et d’e-commerce
Pour plus de flexibilité, vous pouvez stocker des informations produits dans un modèle dénormalisé à l’aide d’un large éventail d’attributs variables.
Traiter les données de journal et les données de télémétrie
Chargez, interrogez et analysez les données de journal stockées dans les fichiers JSON en utilisant toute la puissance du langage Transact-SQL.
Stocker des données IoT semi-structurées
Lorsque vous avez besoin d’analyser des données IoT en temps réel, chargez les données entrantes directement dans la base de données au lieu de les mettre en transit dans un emplacement de stockage.
Simplifier le développement d’API REST
Convertissez facilement les données relationnelles de votre base de données au format JSON, qui est utilisé par les API REST prenant en charge votre site web.
Combiner des données relationnelles et des données JSON
SQL Server met à disposition un modèle hybride pour stocker et traiter les données relationnelles et JSON en utilisant le langage Transact-SQL standard. Vous pouvez organiser des regroupements de vos documents JSON dans des tables, établir des relations entre eux, combiner des colonnes scalaires fortement typées stockées dans des tables contenant des paires clé-valeur flexibles dans des colonnes JSON, et interroger les valeurs scalaires et JSON dans une ou plusieurs tables en utilisant la syntaxe Transact-SQL complète.
Le texte JSON est stocké dans des colonnes VARCHAR ou NVARCHAR et est indexé sous forme de texte brut. Sachant qu’une fonctionnalité ou un composant SQL Server qui prend en charge le texte prend aussi en charge JSON, il n’y a quasiment aucune contrainte dans l’interaction entre JSON et les autres fonctionnalités SQL Server. Vous pouvez stocker du texte JSON dans des tables en mémoire ou temporelles, appliquer des prédicats de sécurité au niveau des lignes sur du texte JSON, et ainsi de suite.
Voici quelques cas d’usage qui montrent comment utiliser la prise en charge JSON intégrée dans SQL Server.
Stocker et indexer des données JSON dans SQL Server
JSON étant un format texte, les documents JSON peuvent être stockés en colonnes NVARCHAR dans une base de données SQL Database. Étant donné que NVARCHAR le type est pris en charge dans tous les sous-systèmes SQL Server, vous pouvez placer des documents JSON dans des tables avec des index columnstore en cluster, des tables optimisées en mémoire ou des fichiers externes qui peuvent être lus à l’aide d’OPENROWSET ou de PolyBase.
Pour plus d’informations sur les options de stockage, d’indexation et d’optimisation des données JSON dans SQL Server, consultez les articles suivants :
- Stocker des documents JSON dans SQL Server ou SQL Database
- Indexer des données JSON
- Optimiser le traitement JSON avec OLTP en mémoire
Charger de fichiers JSON dans SQL Server
Vous pouvez mettre les informations stockées dans les fichiers au format JSON standard ou JSON délimité par des lignes. SQL Server peut importer le contenu des fichiers JSON, l’analyser à l’aide du ou JSON_VALUE des OPENJSON fonctions et le charger dans des tables.
Si vos documents JSON sont stockés dans des fichiers locaux, sur des lecteurs réseau partagés ou à des emplacements Azure Files accessibles par SQL Server, vous pouvez recourir à l’importation en bloc pour charger vos données JSON dans SQL Server.
Si vos fichiers au format JSON délimité par des lignes sont stockés dans le stockage blob Azure ou le système de fichiers Hadoop, vous pouvez utiliser PolyBase pour charger le texte JSON, l’analyser dans du code Transact-SQL et le charger dans des tables.
Importer des données JSON dans des tables SQL Server
Si vous devez charger des données JSON à partir d’un service externe dans SQL Server, vous pouvez l’utiliser OPENJSON pour importer les données dans SQL Server au lieu d’analyser les données dans la couche Application.
DECLARE @jsonVariable NVARCHAR(MAX);
SET @jsonVariable = N'[
{
"Order": {
"Number":"SO43659",
"Date":"2011-05-31T00:00:00"
},
"AccountNumber":"AW29825",
"Item": {
"Price":2024.9940,
"Quantity":1
}
},
{
"Order": {
"Number":"SO43661",
"Date":"2011-06-01T00:00:00"
},
"AccountNumber":"AW73565",
"Item": {
"Price":2024.9940,
"Quantity":3
}
}
]';
-- INSERT INTO <sampleTable>
SELECT SalesOrderJsonData.*
FROM OPENJSON(@jsonVariable, N'$') WITH (
Number VARCHAR(200) N'$.Order.Number',
Date DATETIME N'$.Order.Date',
Customer VARCHAR(200) N'$.AccountNumber',
Quantity INT N'$.Item.Quantity'
) AS SalesOrderJsonData;
Vous pouvez fournir le contenu de la variable JSON par le biais d’un service REST externe, l’envoyer en tant que paramètre à partir d’un framework JavaScript côté client ou le charger à partir de fichiers externes. Vous pouvez facilement insérer, mettre à jour ou fusionner les résultats du texte JSON dans une table SQL Server.
Analyser des données JSON à l’aide de requêtes SQL
Si vous devez filtrer ou agréger des données JSON à des fins de création de rapports, vous pouvez utiliser OPENJSON pour transformer JSON en format relationnel. Vous pouvez ensuite utiliser le langage et les fonctions intégrées Transact-SQL standard pour préparer les rapports.
SELECT Tab.Id,
SalesOrderJsonData.Customer,
SalesOrderJsonData.Date
FROM SalesOrderRecord AS Tab
CROSS APPLY OPENJSON(Tab.json, N'$.Orders.OrdersArray') WITH (
Number VARCHAR(200) N'$.Order.Number',
Date DATETIME N'$.Order.Date',
Customer VARCHAR(200) N'$.AccountNumber',
Quantity INT N'$.Item.Quantity'
) AS SalesOrderJsonData
WHERE JSON_VALUE(Tab.json, '$.Status') = N'Closed'
ORDER BY JSON_VALUE(Tab.json, '$.Group'),
Tab.DateModified;
Vous pouvez utiliser des colonnes de table standard et des valeurs de texte JSON dans une même requête. Vous pouvez ajouter des index dans l’expression JSON_VALUE(Tab.json, '$.Status') pour améliorer les performances de la requête. Pour plus d’informations, consultez Indexer des données JSON.
Retourner les données d’une table SQL Server au format JSON
Si vous avez un service web qui tire des données de la couche Base de données et les retourne au format JSON, ou que vous disposez de frameworks ou de bibliothèques JavaScript qui acceptent des données au format JSON, vous pouvez mettre en forme la sortie JSON directement dans une requête SQL. Au lieu d’écrire du code ou d’inclure une bibliothèque pour convertir les résultats de requête tabulaire, puis sérialiser des objets au format JSON, vous pouvez utiliser FOR JSON pour déléguer la mise en forme JSON à SQL Server.
Par exemple, vous pouvez générer une sortie JSON conforme à la spécification OData. Le service web attend une demande et une réponse dans le format suivant :
Demande :
/Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductNameRéponse :
{"@odata.context": "https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity", "ProductID": 1, "ProductName": "Chai"}
Cette URL OData représente une demande pour les colonnes ProductID et ProductName pour le produit avec comme ID la valeur 1. Vous pouvez utiliser FOR JSON pour mettre en forme la sortie comme prévu dans SQL Server.
SELECT 'https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity' AS '@odata.context',
ProductID,
Name as ProductName
FROM Production.Product
WHERE ProductID = 1
FOR JSON AUTO;
La sortie de cette requête est du texte JSON entièrement conforme aux spécifications OData. La mise en forme et l’échappement sont gérées par SQL Server. SQL Server peut également mettre en forme les résultats de requêtes dans n’importe quel format, notamment OData JSON ou GeoJSON.
Tester la prise en charge de JSON à partir de l’exemple de base de données AdventureWorks
Pour obtenir l’exemple de base de données AdventureWorks, téléchargez au minimum le fichier de base de données et le fichier d’exemples et de scripts sur GitHub.
Après avoir restauré l’exemple de base de données sur une instance de SQL Server, extrayez le fichier d’exemples, puis ouvrez le JSON Sample Queries procedures views and indexes.sql fichier à partir du dossier JSON. Exécutez les scripts de ce fichier pour remettre certaines données existantes au format JSON, testez des exemples de rapports et de requêtes sur les données JSON, indexez les données JSON, puis importez et exportez les données JSON.
Voici ce que vous pouvez faire avec les scripts inclus dans le fichier :
Dénormaliser le schéma existant pour créer des colonnes de données JSON.
Stockez des informations de
SalesReasons,SalesOrderDetails,SalesPerson,Customeret d’autres tables qui contiennent des informations relatives à la commande client dans des colonnes JSON de la tableSalesOrder_json.Stockez des informations depuis
EmailAddressesetPersonPhonedes tables dans laPerson_jsontable sous forme de tableaux d’objets JSON.
Créer des procédures et des vues qui interrogent les données JSON.
Indexer des données JSON. Créez des index sur les propriétés JSON et des index de recherche en texte intégral.
Importer et exporter les données JSON. Créez et exécutez des procédures qui exportent le contenu des tables
PersonetSalesOrdersous forme de résultats JSON, puis importez et mettez à jour les tablesPersonetSalesOrderen utilisant une entrée JSON.Exécuter des exemples de requêtes. Exécutez des requêtes qui appellent les procédures stockées et les vues que vous avez créées aux étapes 2 et 4.
Nettoyer les scripts. N’exécutez pas cette partie si vous voulez conserver les procédures stockées et les vues que vous avez créées aux étapes 2 et 4.
En savoir plus sur JSON dans SQL Server et Azure SQL Database
Vidéos Microsoft
Pour une présentation visuelle de la prise en charge intégrée de JSON dans SQL Server et Azure SQL Database, consultez la vidéo suivante :
Contenu connexe
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour