Analyser et transformer des données JSON avec OPENJSON
S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics
La fonction d’ensemble de lignes OPENJSON convertit du texte JSON en un ensemble de lignes et de colonnes. Après avoir transformé une collection JSON en ensemble de lignes avec OPENJSON, vous pouvez exécuter n’importe quelle requête SQL sur les données retournées ou l’insérer dans une table SQL Server.
La fonction OPENJSON prend un seul objet JSON ou une collection d’objets JSON, et les transforme en une ou plusieurs lignes. Par défaut, la fonction OPENJSON retourne les données suivantes :
- À partir d’un objet JSON, toutes les paires clé/valeur qu’elle trouve au premier niveau.
- À partir d’un tableau JSON, tous les éléments du tableau avec leurs index.
Vous pouvez ajouter une clause WITH facultative afin de fournir un schéma qui définit explicitement la structure de la sortie.
Option 1 : OPENJSON avec la sortie par défaut
Quand vous utilisez la fonction OPENJSON sans fournir un schéma explicite pour les résultats (autrement dit, sans clause WITH après OPENJSON), la fonction retourne une table comportant les trois colonnes suivantes :
- Le nom de la propriété dans l’objet d’entrée (ou l’index de l’élément dans le tableau d’entrée).
- La valeur de la propriété ou de l’élément du tableau.
- Le type (par exemple, chaîne, nombre, booléen, tableau ou objet).
OPENJSON retourne chaque propriété de l’objet JSON, ou chaque élément du tableau, sous la forme d’une ligne distincte.
L’exemple ci-dessous utilise OPENJSON avec le schéma par défaut (autrement dit, sans la clause WITH facultative) et retourne une ligne pour chaque propriété de l’objet JSON.
Exemple :
DECLARE @json NVARCHAR(MAX)
SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}';
SELECT *
FROM OPENJSON(@json);
Résultats :
key | value | type |
---|---|---|
name | John | 1 |
surname | Doe | 1 |
age | 45 | 2 |
skills | ["SQL","C#","MVC"] | 4 |
Plus d’informations sur OPENJSON avec le schéma par défaut
Pour plus d’informations et d’exemples, consultez Utiliser OPENJSON avec le schéma par défaut (SQL Server) .
Pour plus d’informations sur la syntaxe et l’utilisation, consultez OPENJSON (Transact-SQL).
Option 2 : Sortie OPENJSON avec une structure explicite
Quand vous spécifiez un schéma pour les résultats à l’aide de la clause WITH de la fonction OPENJSON, la fonction retourne une table contenant uniquement les colonnes que vous définissez dans la clause WITH. Dans la clause WITH facultative, vous spécifiez un ensemble de colonnes de sortie, leurs types et les chemins des propriétés sources JSON pour chaque valeur de sortie. OPENJSON itère le tableau d’objets JSON, lit la valeur du chemin spécifié pour chaque colonne, puis convertit la valeur dans le type spécifié.
L’exemple ci-dessous utilise OPENJSON avec un schéma pour la sortie que vous spécifiez de façon explicite dans la clause WITH.
Exemple :
DECLARE @json NVARCHAR(MAX)
SET @json =
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
}
}
]'
SELECT * FROM
OPENJSON ( @json )
WITH (
Number varchar(200) '$.Order.Number' ,
Date datetime '$.Order.Date',
Customer varchar(200) '$.AccountNumber',
Quantity int '$.Item.Quantity'
)
Résultats :
Numéro | Date | Customer | Quantity |
---|---|---|---|
SO43659 | 2011-05-31T00:00:00 | AW29825 | 1 |
SO43661 | 2011-06-01T00:00:00 | AW73565 | 3 |
Cette fonction retourne et met en forme les éléments d’un tableau JSON.
Pour chaque élément du tableau JSON, OPENJSON génère une nouvelle ligne dans la table de sortie. Les deux éléments du tableau JSON sont convertis en deux lignes dans la table retournée.
Pour chaque colonne spécifiée à l’aide de la syntaxe
colName type json_path
, OPENJSON convertit la valeur trouvée dans chaque élément de tableau sur le chemin spécifié dans le type spécifié. Dans cet exemple, les valeurs de la colonneDate
sont tirées de chaque élément sur le chemin$.Order.Date
et converties en valeur datetime.
Plus d’informations sur OPENJSON avec un schéma explicite
Pour plus d’informations et d’exemples, consultez Utiliser OPENJSON avec un schéma explicite (SQL Server) .
Pour plus d’informations sur la syntaxe et l’utilisation, consultez OPENJSON (Transact-SQL).
OPENJSON exige le niveau de compatibilité 130
La fonction OPENJSON est disponible uniquement sous le niveau de compatibilité 130. Si le niveau de compatibilité de votre base de données est inférieur à 130, SQL Server ne peut pas trouver ni exécuter la fonction OPENJSON. Les autres fonctions JSON intégrées sont disponibles à tous les niveaux de compatibilité.
Vous pouvez vérifier le niveau de compatibilité dans la vue sys.databases
ou dans les propriétés de base de données.
Vous pouvez changer le niveau de compatibilité d’une base de données à l’aide de la commande suivante :
ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130
En savoir plus sur JSON dans SQL Server et Azure SQL Database
Vidéos Microsoft
Remarque
Certains des liens vidéo de cette section peuvent ne pas fonctionner pour l’instant. Microsoft migre le contenu précédemment disponible sur Channel 9 vers une nouvelle plateforme. Nous allons mettre à jour les liens au fur et à mesure que les vidéos sont migrées vers la nouvelle plateforme.
Pour obtenir une présentation visuelle de la prise en charge intégrée de JSON dans SQL Server et Azure SQL Database, consultez les vidéos suivantes :
Voir aussi
Commentaires
https://aka.ms/ContentUserFeedback.
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, consultezEnvoyer et afficher des commentaires pour