Valider, interroger et modifier les données JSON avec des fonctions intégrées (SQL Server)

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL Database Azure SQL Managed Instance

La prise en charge intégrée de JSON inclut les fonctions intégrées suivantes décrites brièvement dans cette rubrique.

• ISJSON teste si une chaîne contient un JSON valide.
• JSON_VALUE extrait une valeur scalaire à partir d’une chaîne JSON.
• JSON_QUERY extrait un objet ou un tableau à partir d’une chaîne JSON.
• JSON_MODIFY met à jour la valeur d’une propriété dans une chaîne JSON et renvoie la chaîne JSON mise à jour.

Pour toutes les fonctions JSON, passez en revue les fonctions JSON.

Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 ou AdventureWorksDW2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires Microsoft SQL Server.

Texte JSON des exemples de cette page

Les exemples de cette page utilisent le texte JSON similaire au contenu illustré dans l’exemple suivant :

{
    "id": "DesaiFamily",
    "parents": [
        { "familyName": "Desai", "givenName": "Prashanth" },
        { "familyName": "Miller", "givenName": "Helen" }
    ],
    "children": [
        {
            "familyName": "Desai",
            "givenName": "Jesse",
            "gender": "female",
            "grade": 1,
            "pets": [
                { "givenName": "Goofy" },
                { "givenName": "Shadow" }
            ]
        },
        {
            "familyName": "Desai",
            "givenName": "Lisa",
            "gender": "female",
            "grade": 8
        }
    ],
    "address": {
        "state": "NY",
        "county": "Manhattan",
        "city": "NY"
    },
    "creationDate": 1431620462,
    "isRegistered": false
}

Ce document JSON, qui contient des éléments complexes imbriqués, est stocké dans l’exemple de table suivant :

CREATE TABLE Families (
    id INT identity CONSTRAINT PK_JSON_ID PRIMARY KEY,
    [doc] NVARCHAR(MAX)
);

Les fonctions JSON fonctionnent de la même façon que le document JSON soit stocké dans varchar, nvarchar ou dans le type de données json natif.

Valider le texte JSON en utilisant la fonction ISJSON

La fonction ISJSON teste si une chaîne contient un JSON valide.

L’exemple suivant retourne des lignes dans lesquelles la colonne JSON contient du texte JSON valide. Notez que, sans contrainte JSON explicite, vous pouvez entrer n’importe quel texte dans la colonne nvarchar :

SELECT *
FROM Families
WHERE ISJSON(doc) > 0;

Pour plus d’informations, consultez ISJSON.

Extraire une valeur d’un texte JSON en utilisant la fonction JSON_VALUE

La fonction JSON_VALUE extrait une valeur scalaire à partir d’une chaîne JSON. La requête suivante retourne les documents, pour lesquels le champ id JSON a la valeur DesaiFamily, triés selon les champs JSON city et state :

SELECT JSON_VALUE(f.doc, '$.id') AS Name,
    JSON_VALUE(f.doc, '$.address.city') AS City,
    JSON_VALUE(f.doc, '$.address.county') AS County
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC,
    JSON_VALUE(f.doc, '$.address.state') ASC

Les résultats de cette requête sont présentés dans le tableau suivant :

Pour plus d’informations, consultez JSON_VALUE.

Extraire un objet ou un tableau d’un texte JSON en utilisant la fonction JSON_QUERY

La fonction JSON_QUERY extrait un objet ou un tableau à partir d’une chaîne JSON. L’exemple suivant montre comment renvoyer un fragment JSON dans les résultats de la requête.

SELECT JSON_QUERY(f.doc, '$.address') AS Address,
    JSON_QUERY(f.doc, '$.parents') AS Parents,
    JSON_QUERY(f.doc, '$.parents[0]') AS Parent0
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily';

Les résultats de cette requête sont présentés dans le tableau suivant :

Pour plus d’informations, consultez JSON_QUERY.

Analyser les collections JSON imbriquées

La fonction OPENJSON vous permet de transformer un sous-tableau JSON en ensemble de lignes, puis de le joindre à l’élément parent. Par exemple, vous pouvez retourner tous les documents de la famille, puis les « joindre » à leurs objets children stockés sous forme de tableau JSON interne :

SELECT JSON_VALUE(f.doc, '$.id') AS Name,
    JSON_VALUE(f.doc, '$.address.city') AS City,
    c.givenName,
    c.grade
FROM Families f
CROSS APPLY OPENJSON(f.doc, '$.children') WITH (
    grade INT,
    givenName NVARCHAR(100)
) c

Les résultats de cette requête sont présentés dans le tableau suivant :

Nous obtenons deux lignes comme résultat, car une ligne parente est jointe à deux lignes enfants produites par l’analyse de deux éléments du sous-tableau des enfants. La fonction OPENJSON analyse un fragment children de la colonne doc et retourne les valeurs grade et givenName de chaque élément sous la forme d’un ensemble de lignes. Cet ensemble de lignes peut être joint au document parent.

Interroger des sous-tableaux JSON hiérarchiques imbriqués

Vous pouvez appliquer plusieurs appels CROSS APPLY OPENJSON afin d’interroger des structures JSON imbriquées. Le document JSON utilisé dans cet exemple comporte un tableau imbriqué appelé children, où chaque enfant comporte un tableau imbriqué de pets. La requête suivante analyse les enfants de chaque document, retourne chaque objet de tableau sous forme de ligne, puis analyse le tableau pets :

SELECT c.familyName,
    c.givenName AS childGivenName,
    p.givenName AS petName
FROM Families f
CROSS APPLY OPENJSON(f.doc) WITH (
    familyName NVARCHAR(100),
    children NVARCHAR(MAX) AS JSON
) AS a
CROSS APPLY OPENJSON(children) WITH (
    familyName NVARCHAR(100),
    givenName NVARCHAR(100),
    pets NVARCHAR(max) AS JSON
) AS c
OUTER APPLY OPENJSON(pets) WITH (givenName NVARCHAR(100)) AS p;

Le premier appel OPENJSON retourne un fragment du tableau children à l’aide de la clause AS JSON. Ce fragment de tableau est fourni à la deuxième fonction OPENJSON qui retourne les valeurs givenName, firstName de chaque enfant, ainsi que le tableau de pets. Le tableau de pets est fourni à la troisième fonction OPENJSON qui retourne la valeur givenName de l’animal.

Les résultats de cette requête sont présentés dans le tableau suivant :

Le document racine est joint avec deux lignes children retournées par le premier appel OPENJSON(children) qui fait deux lignes (ou tuples). Ensuite, chaque ligne est jointe aux nouvelles lignes générées par OPENJSON(pets) à l’aide de l’opérateur OUTER APPLY. Jesse a deux animaux, donc (Desai, Jesse) est joint avec deux lignes générées pour Goofy et Shadow. Lisa n’a pas d’animaux, donc aucune ligne n’est retournée par OPENJSON(pets) pour ce tuple. En revanche, étant donné que nous utilisons OUTER APPLY, nous obtenons NULL dans la colonne. Si nous mettons CROSS APPLY au lieu de OUTER APPLY, Lisa n’est pas retournée dans le résultat, car il n’y a pas de lignes d’animaux pouvant être jointes à ce tuple.

Comparer JSON_VALUE et JSON_QUERY

La principale différence entre JSON_VALUE et JSON_QUERY est que JSON_VALUE renvoie une valeur scalaire, tandis que JSON_QUERY renvoie un objet ou un tableau.

Considérons l’exemple de texte JSON suivant :

{
    "a": "[1,2]",
    "b": [1, 2],
    "c": "hi"
}

Dans cet exemple de texte JSON, les membres de données « a » et « c » sont des valeurs de chaîne, tandis que le membre de données « b » est un tableau. JSON_VALUE et JSON_QUERY retournent les résultats suivants :

Tester JSON_VALUE et JSON_QUERY avec la base de données exemple AdventureWorks

Testez les fonctions intégrées décrites dans cette rubrique en exécutant les exemples suivants avec la base de données d’exemple AdventureWorks2022. Pour plus d'informations sur l'ajout de données JSON à des fins de test en exécutant un script, consultez Tester la prise en charge de JSON intégrée.

Dans les exemples suivants, la colonne Info de la table SalesOrder_json contient un texte JSON.

Exemple 1 : Renvoyer les colonnes standard et les données JSON

La requête suivante retourne des valeurs des deux colonnes relationnelles standard et d’une colonne JSON.

SELECT SalesOrderNumber,
    OrderDate,
    Status,
    ShipDate,
    AccountNumber,
    TotalDue,
    JSON_QUERY(Info, '$.ShippingInfo') ShippingInfo,
    JSON_QUERY(Info, '$.BillingInfo') BillingInfo,
    JSON_VALUE(Info, '$.SalesPerson.Name') SalesPerson,
    JSON_VALUE(Info, '$.ShippingInfo.City') City,
    JSON_VALUE(Info, '$.Customer.Name') Customer,
    JSON_QUERY(OrderItems, '$') OrderItems
FROM Sales.SalesOrder_json
WHERE ISJSON(Info) > 0;

Exemple 2 : Agréger et filtrer des valeurs JSON

La requête suivante agrège les sous-totaux par nom de client (stocké dans JSON) et état (stocké dans une colonne ordinaire). Elle filtre ensuite les résultats par ville (stockés dans JSON) et OrderDate (stockés dans une colonne ordinaire).

DECLARE @territoryid INT;
DECLARE @city NVARCHAR(32);

SET @territoryid = 3;
SET @city = N'Seattle';

SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer,
    Status,
    SUM(SubTotal) AS Total
FROM Sales.SalesOrder_json
WHERE TerritoryID = @territoryid
    AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
    AND OrderDate > '1/1/2015'
GROUP BY JSON_VALUE(Info, '$.Customer.Name'),
    Status
HAVING SUM(SubTotal) > 1000;

Mettre à jour les valeurs de propriété dans un texte JSON en utilisant la fonction JSON_MODIFY

La fonction JSON_MODIFY met à jour la valeur d’une propriété dans une chaîne JSON et retourne la chaîne JSON mise à jour.

L’exemple suivant met à jour la valeur d’une propriété JSON dans une variable contenant du texte JSON.

SET @info = JSON_MODIFY(@jsonInfo, '$.info.address[0].town', 'London');

Pour plus d’informations, consultez JSON_MODIFY.

Contenu connexe

• ISJSON (Transact-SQL)
• JSON_VALUE (Transact-SQL)
• JSON_QUERY (Transact-SQL)
• JSON_MODIFY (Transact-SQL)
• Expressions de chemin JSON (SQL Server)
• Type de données JSON