Analyser et transformer des données JSON avec OPENJSON

  • Article

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 :

  1. 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).
  2. La valeur de la propriété ou de l’élément du tableau.
  3. 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 colonne Date 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