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. Pour plus d’informations sur l’utilisation de données JSON dans le moteur de base de données SQL Server, consultez Données JSON dans 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.

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 name 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 value 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.

DECLARE @json NVARCHAR(MAX);

SET @json='{ "name": "John", "surname": "Doe", "age": 45, "skills": [ "SQL", "C#", "MVC" ]}';

SELECT *
FROM OPENJSON(@json);

Voici le jeu de résultats obtenu.

key value type
name John 1
surname Doe 1
age 45 2
skills [ "SQL" ,"C#" ,"MVC" ] 4

Pour plus d’informations et pour obtenir des exemples, consultez Utiliser OPENJSON avec le schéma par défaut.

Pour plus de détails sur la syntaxe et l’utilisation, consultez OPENJSON.

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 suivant utilise OPENJSON avec un schéma pour la sortie que vous spécifiez explicitement dans la clause WITH.

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
    {
        "Order": {
            "Number": "SO43659",
            "Date": "2024-05-31T00:00:00"
        },
        "AccountNumber": "AW29825",
        "Item": {
            "Price": 2024.9940,
            "Quantity": 1
        }
    },
    {
        "Order": {
            "Number": "SO43661",
            "Date": "2024-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'
);

Voici le jeu de résultats obtenu.

Nombre Date Customer Quantité
SO43659 2024-05-31T00:00:00 AW29825 1
SO43661 2024-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.

Pour plus d’informations et pour obtenir des exemples, consultez Utilisation d’OPENJSON avec un schéma explicite (SQL Server).

Pour plus de détails sur la syntaxe et l’utilisation, consultez OPENJSON.

OPENJSON requiert le niveau de compatibilité 130

La fonction OPENJSON est disponible uniquement sous le niveau de compatibilité 130 ou supérieur. Si le niveau de compatibilité de la 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 la base de données et modifier le niveau de compatibilité d’une base de données à l’aide de la commande suivante :

ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130;

Contenu connexe