Traiter des données JSON avec des fonctions intégrées

Envisagez un scénario dans lequel votre application de commerce électronique stocke les préférences des clients et les métadonnées de commande en tant que documents JSON. Une application mobile envoie des données de panier d’achat au format JSON, et votre système de création de rapports doit exporter des catalogues de produits en tant que JSON pour une API web. L’utilisation directe de JSON dans votre base de données élimine le besoin de transformations de couche application et assure le traitement de vos données efficacement.

Les bases de données SQL Server, Azure SQL et SQL dans Fabric fournissent une prise en charge JSON intégrée qui vous permet d’analyser, d’interroger, de créer et de transformer des données JSON directement dans T-SQL. Dans cette unité, vous allez apprendre à utiliser des fonctions JSON pour extraire des valeurs, construire une sortie JSON, agréger des données dans des tableaux JSON et valider le contenu JSON.

Extraire des valeurs avec JSON_VALUE et JSON_QUERY

Lorsque vous utilisez JSON stocké dans votre base de données, vous devez extraire des valeurs spécifiques pour le filtrage, la jointure ou l’affichage. SQL Server fournit deux fonctions à cet effet :

JSON_VALUE() extrait une valeur scalaire (chaîne, nombre, booléen) à partir d’une chaîne JSON :

DECLARE @json NVARCHAR(MAX) = N'{
    "customer": {
        "id": 12345,
        "name": "Contoso Ltd",
        "active": true
    },
    "orderTotal": 1599.99
}';

SELECT 
    JSON_VALUE(@json, '$.customer.id') AS CustomerID,
    JSON_VALUE(@json, '$.customer.name') AS CustomerName,
    JSON_VALUE(@json, '$.orderTotal') AS OrderTotal;

L'ensemble de résultats sera :

CustomerID   CustomerName   OrderTotal
----------   ------------   ----------
12345        Contoso Ltd    1599.99

La fonction navigue dans la structure JSON à l’aide de l’expression de chemin d’accès et retourne la valeur sous forme de NVARCHAR(4000) chaîne. Vous pouvez convertir le résultat en d’autres types de données en fonction des besoins pour les calculs ou les comparaisons.

JSON_QUERY() extrait un objet ou un tableau JSON (valeurs noncalaires) :

DECLARE @json NVARCHAR(MAX) = N'{
    "customer": {
        "id": 12345,
        "name": "Contoso Ltd"
    },
    "items": [
        {"product": "Widget", "qty": 5},
        {"product": "Gadget", "qty": 3}
    ]
}';

SELECT 
    JSON_QUERY(@json, '$.customer') AS CustomerObject,
    JSON_QUERY(@json, '$.items') AS ItemsArray;

L'ensemble de résultats sera :

CustomerObject                          ItemsArray
--------------------------------------  ------------------------------------------------
{"id": 12345,"name": "Contoso Ltd"}     [{"product": "Widget", "qty": 5},{"product": "Gadget", "qty": 3}]

Contrairement JSON_VALUE()à , JSON_QUERY() conserve la structure JSON, retourne des objets et des tableaux en tant que chaînes JSON valides que vous pouvez stocker, passer à d’autres fonctions ou revenir aux applications.

L’expression de chemin utilise $ pour représenter l’élément racine, avec la notation par points pour les propriétés imbriquées et la notation entre crochets pour les éléments de tableau, comme dans l’exemple suivant :

-- Access array elements by index (0-based)
SELECT JSON_VALUE(@json, '$.items[0].product') AS FirstProduct;

Le résultat sera :

FirstProduct
------------
Widget

Les index de tableau commencent à 0, ce qui fait référence $.items[0] au premier élément. Utilisez cette syntaxe pour extraire des éléments spécifiques lorsque vous connaissez la position, ou combinez avec OPENJSON lorsque vous devez traiter tous les éléments du tableau.

Analyser des tableaux JSON avec OPENJSON

OPENJSON est une fonction table qui transforme les données JSON en ensemble de lignes relationnelles. Utilisez cette fonction pour joindre des données JSON avec des tables relationnelles ou traiter des éléments de tableau individuellement.

La requête suivante analyse un tableau JSON en lignes avec un schéma par défaut :

DECLARE @json NVARCHAR(MAX) = N'[
    {"id": 1, "name": "Widget", "price": 29.99},
    {"id": 2, "name": "Gadget", "price": 49.99},
    {"id": 3, "name": "Gizmo", "price": 19.99}
]';

SELECT * FROM OPENJSON(@json);

L'ensemble de résultats sera :

key   value                                          type
---   --------------------------------------------   ----
0     {"id": 1, "name": "Widget", "price": 29.99}   5
1     {"id": 2, "name": "Gadget", "price": 49.99}   5
2     {"id": 3, "name": "Gizmo", "price": 19.99}    5

Sans schéma, OPENJSON retourne trois colonnes : key (l’index de tableau ou le nom de propriété), value (contenu JSON) et type (nombre indiquant le type de données JSON : 0=null, 1=string, 2=number, 3=boolean, 4=array, 5=object).

La requête suivante définit un schéma explicite pour extraire des colonnes spécifiques avec des types de données appropriés :

SELECT 
    ProductID,
    ProductName,
    Price
FROM OPENJSON(@json)
WITH (
    ProductID INT '$.id',
    ProductName NVARCHAR(100) '$.name',
    Price DECIMAL(10,2) '$.price'
);

L'ensemble de résultats sera :

ProductID   ProductName   Price
---------   -----------   ------
1           Widget        29.99
2           Gadget        49.99
3           Gizmo         19.99

La WITH clause mappe les propriétés JSON aux colonnes typées. Cette approche vous donne des types de données appropriés pour les calculs et les comparaisons, et vous permet de sélectionner uniquement les propriétés dont vous avez besoin.

Combiner OPENJSON avec des données de table à l’aide de CROSS APPLY:

-- Assuming Orders table has a JSON column called OrderDetails
SELECT 
    o.OrderID,
    o.CustomerID,
    items.ProductName,
    items.Quantity,
    items.UnitPrice
FROM Orders AS o
CROSS APPLY OPENJSON(o.OrderDetails)
WITH (
    ProductName NVARCHAR(100) '$.product',
    Quantity INT '$.qty',
    UnitPrice DECIMAL(10,2) '$.price'
) AS items;

Construire JSON avec JSON_OBJECT et JSON_ARRAY

SQL Server 2022 introduit JSON_OBJECT et JSON_ARRAY fonctions pour la construction JSON intuitive :

JSON_OBJECT() crée un objet JSON à partir de paires clé-valeur. L’exemple suivant montre comment générer un objet JSON pour un produit :

SELECT JSON_OBJECT(
    'id': ProductID,
    'name': Name,
    'price': ListPrice,
    'available': CASE WHEN SellEndDate IS NULL THEN 'true' ELSE 'false' END
) AS ProductJson
FROM SalesLT.Product
WHERE ProductID = 680;

Le résultat sera :

ProductJson
---------------------------------------------------------------------------
{"id":680,"name":"HL Road Frame - Black, 58","price":1431.50,"available":"true"}

La fonction gère automatiquement la conversion de type de données et l’échappement JSON approprié pour les caractères spéciaux dans les valeurs de chaîne.

JSON_ARRAY() crée un tableau JSON à partir de valeurs. L’exemple suivant génère un tableau JSON :

SELECT JSON_ARRAY(
    'SQL Server',
    'Azure SQL Database',
    'SQL Database in Fabric'
) AS Platforms;

Le résultat sera :

Platforms
---------------------------------------------------------
["SQL Server","Azure SQL Database","SQL Database in Fabric"]

Vous pouvez transmettre des valeurs de colonne, des variables ou des valeurs littérales à JSON_ARRAY(). La fonction crée un tableau JSON correctement mis en forme, quel que soit le type d’entrée.

Ensuite, combinez ces fonctions pour générer des structures JSON imbriquées. L’exemple suivant construit un objet JSON de commande complet avec des informations client et des totaux :

SELECT JSON_OBJECT(
    'orderId': soh.SalesOrderID,
    'orderDate': soh.OrderDate,
    'customer': JSON_OBJECT(
        'id': c.CustomerID,
        'name': c.CompanyName
    ),
    'totals': JSON_OBJECT(
        'subtotal': soh.SubTotal,
        'tax': soh.TaxAmt,
        'total': soh.TotalDue
    )
) AS OrderJson
FROM SalesLT.SalesOrderHeader AS soh
INNER JOIN SalesLT.Customer AS c
    ON soh.CustomerID = c.CustomerID
WHERE soh.SalesOrderID = 71774;

Le résultat sera :

OrderJson
--------------------------------------------------------------------------------
{"orderId":71774,"orderDate":"2008-06-01","customer":{"id":29825,"name":"Contoso"},"totals":{"subtotal":880.35,"tax":70.43,"total":972.79}}

L'imbrication des appels JSON_OBJECT crée des structures hiérarchiques qui correspondent au format attendu de votre application. Cette approche est plus propre que la concaténation de chaînes et garantit une sortie JSON valide.

Agréger des données avec JSON_ARRAYAGG

JSON_ARRAYAGG agrège les valeurs de plusieurs lignes dans un tableau JSON unique. Cette fonction est utile pour créer une sortie JSON dénormalisée à partir de données relationnelles normalisées :

SELECT 
    c.CustomerID,
    c.CompanyName,
    JSON_ARRAYAGG(soh.SalesOrderID) AS OrderIds
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
    ON c.CustomerID = soh.CustomerID
GROUP BY c.CustomerID, c.CompanyName;

Le résultat sera :

CustomerID   CompanyName           OrderIds
----------   -------------------   ------------------
29825        Contoso Retail        [71774,71776,71780]
29847        Adventure Works       [71782,71784]

La fonction collecte toutes les valeurs correspondantes à partir des lignes groupées et les combine dans un seul tableau JSON. Cette méthode est utile pour créer des réponses d’API dénormalisées à partir de tables de base de données normalisées.

Vous pouvez combiner JSON_ARRAYAGG avec JSON_OBJECT pour créer des tableaux d’objets complexes :

SELECT 
    pc.Name AS Category,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id': p.ProductID,
            'name': p.Name,
            'price': p.ListPrice
        )
    ) AS Products
FROM SalesLT.ProductCategory AS pc
INNER JOIN SalesLT.Product AS p
    ON pc.ProductCategoryID = p.ProductCategoryID
GROUP BY pc.ProductCategoryID, pc.Name;

Le résultat suivant est :

Category        Products
--------------  --------------------------------------------------------------------------
Road Bikes      [{"id":749,"name":"Road-150 Red, 62","price":3578.27},{"id":750,"name":"Road-150 Red, 44","price":3578.27}]
Mountain Bikes  [{"id":771,"name":"Mountain-100 Silver, 38","price":3399.99},{"id":772,"name":"Mountain-100 Black, 38","price":3374.99}]

Valider et vérifier JSON avec JSON_CONTAINS

Les données JSON provenant de sources externes peuvent être incorrectes, manquantes ou contenir des valeurs inattendues. La tentative d’extraction de valeurs à partir de chemins JSON ou manquants non valides peut entraîner des échecs de requête ou retourner des résultats trompeurs NULL qui masquent les problèmes de données.

Le traitement JSON robuste nécessite un codage défensif : vérifiez que le JSON est bien formé avant de l’analyser. Vérifiez ensuite que les chemins attendus existent avant d’extraire des valeurs et vérifiez que les valeurs correspondent à vos attentes avant de les utiliser dans la logique métier. SQL Server fournit plusieurs fonctions pour vous aider à valider le contenu JSON à chaque étape du traitement.

Comprendre les modes de chemin lax et stricts

Vous pouvez utiliser des expressions de chemin JSON dans deux modes qui contrôlent la gestion des erreurs :

DECLARE @json NVARCHAR(MAX) = N'{"name": "Widget", "price": 29.99}';

-- Lax mode (default): Returns NULL for missing paths
SELECT JSON_VALUE(@json, 'lax $.description') AS LaxResult;

-- Strict mode: Raises an error for missing paths
SELECT JSON_VALUE(@json, 'strict $.description') AS StrictResult;

Le résultat sera :

LaxResult
---------
NULL

-- Strict mode raises: Property cannot be found on the specified JSON path.

Utilisez le mode lax (valeur par défaut) lorsque les propriétés manquantes sont attendues et doivent retourner NULL. Utilisez le mode strict lorsque des propriétés manquantes indiquent un problème de données qui devrait déclencher une erreur.

ISJSON vérifie si une chaîne contient un JSON valide. L'exemple suivant montre comment utiliser ISJSON :

SELECT 
    ISJSON('{"name": "test"}') AS ValidJson,      -- Returns 1
    ISJSON('not valid json') AS InvalidJson,       -- Returns 0
    ISJSON(NULL) AS NullJson;                      -- Returns NULL

Le résultat sera :

ValidJson   InvalidJson   NullJson
---------   -----------   --------
1           0             NULL

Pour gérer correctement les données non valides, utilisez ISJSON dans les clauses WHERE pour filtrer les lignes contenant un JSON valide, ou dans les expressions CASE.

JSON_PATH_EXISTS vérifie si un chemin d’accès spécifique existe dans un document JSON, comme dans l’exemple suivant :

DECLARE @json NVARCHAR(MAX) = N'{"customer": {"name": "Contoso", "tier": "Gold"}}';

SELECT 
    JSON_PATH_EXISTS(@json, '$.customer.name') AS HasName,
    JSON_PATH_EXISTS(@json, '$.customer.email') AS HasEmail;

Le résultat sera :

HasName   HasEmail
-------   --------
1         0

Cette fonction retourne 1 si le chemin existe, 0 si ce n’est pas le cas. Utilisez-la avant d’appeler JSON_VALUE en mode strict ou de traiter de manière conditionnelle JSON avec des structures variables.

Permet JSON_CONTAINS de vérifier si un document JSON contient une valeur ou un objet spécifique, comme dans l’exemple suivant :

DECLARE @json NVARCHAR(MAX) = N'{"tags": ["sql", "database", "azure"]}';

SELECT 
    JSON_CONTAINS(@json, '"sql"', '$.tags') AS HasSqlTag,
    JSON_CONTAINS(@json, '"python"', '$.tags') AS HasPythonTag;

Le résultat sera :

HasSqlTag   HasPythonTag
---------   ------------
1           0

Optimiser les requêtes JSON avec des colonnes calculées

Lorsque vous interrogez fréquemment des propriétés JSON spécifiques, le moteur de base de données doit analyser le document JSON pour chaque ligne de chaque requête. Pour les tables avec des milliers ou des millions de lignes, cette analyse répétée crée une surcharge significative. Les colonnes calculées vous permettent d’extraire des valeurs JSON une fois et de les stocker dans un format interrogeable qui prend en charge l’indexation.

Pourquoi l’analyse JSON a un impact sur les performances

Considérez une table avec 100 000 enregistrements de produit où chaque ligne contient un document JSON avec des attributs de produit. Un filtrage de requête par catégorie doit :

1. Lire chaque ligne du tableau
2. Pour rechercher la propriété de catégorie, analysez le document JSON
3. Extraire et comparer la valeur

Sans optimisation, même les filtres simples nécessitent des analyses complètes de table avec l’analyse JSON sur chaque ligne.

Créer des colonnes calculées pour les propriétés JSON

Une colonne calculée extrait automatiquement une propriété JSON et la rend disponible en tant que colonne régulière, comme dans l’exemple suivant :

-- Add a computed column that extracts a JSON property
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category');

-- The column is now available in queries
SELECT ProductID, ProductName, ProductCategory
FROM Products
WHERE ProductCategory = 'Electronics';

Le résultat sera :

ProductID   ProductName           ProductCategory
---------   -------------------   ---------------
101         Wireless Mouse        Electronics
102         USB Keyboard          Electronics
103         HD Monitor            Electronics

Par défaut, les colonnes calculées sont virtuelles. La base de données calcule la valeur au moment de la requête, mais peut optimiser l’extraction JSON. Pour obtenir de meilleures performances, vous pouvez conserver la colonne calculée comme dans l’exemple suivant :

-- Persisted computed column stores the extracted value physically
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED;

Les colonnes persistantes stockent la valeur extraite sur le disque, de sorte que le JSON est analysé uniquement lors des opérations INSERT et UPDATE, et non pendant les requêtes SELECT.

Ajouter des index pour un filtrage plus rapide

Le gain de performances réel provient de l’indexation de colonnes calculées :

-- Create an index on the computed column
CREATE INDEX IX_Products_Category ON Products(ProductCategory);

-- Now this query uses an index seek instead of a table scan
SELECT ProductID, ProductName
FROM Products
WHERE ProductCategory = 'Electronics';

Sans l’index, la requête analyse toutes les 100 000 lignes. Avec l’index, le moteur de requête effectue une recherche d’index et récupère uniquement les lignes correspondantes. Cet index peut réduire le temps de requête de secondes à millisecondes.

Indexer plusieurs propriétés JSON

Pour les requêtes qui filtrent sur plusieurs propriétés JSON, créez des colonnes calculées et un index composite :

-- Extract multiple properties
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED,
    ProductBrand AS JSON_VALUE(ProductData, '$.brand') PERSISTED,
    ProductPrice AS CAST(JSON_VALUE(ProductData, '$.price') AS DECIMAL(10,2)) PERSISTED;

-- Create a composite index for common query patterns
CREATE INDEX IX_Products_Category_Brand ON Products(ProductCategory, ProductBrand);

-- Create an index for price range queries
CREATE INDEX IX_Products_Price ON Products(ProductPrice);

Désormais, les requêtes filtrant par catégorie et par marque, ou tri par prix, peuvent utiliser ces index efficacement.

Transformer des données relationnelles en JSON avec FOR JSON

Pour obtenir une sortie JSON complète à partir de requêtes, utiliser FOR JSON PATH ou FOR JSON AUTO:

SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    pc.Name AS CategoryName
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 1000
FOR JSON PATH, ROOT('products');

Le résultat sera :

{"products":[{"ProductID":749,"Name":"Road-150 Red, 62","ListPrice":3578.27,"CategoryName":"Road Bikes"},{"ProductID":750,"Name":"Road-150 Red, 44","ListPrice":3578.27,"CategoryName":"Road Bikes"}]}

FOR JSON PATH vous permet de contrôler la structure JSON par le biais d’alias de colonne. Utilisez la notation par points dans les alias pour créer des objets imbriqués :

SELECT 
    p.ProductID AS 'product.id',
    p.Name AS 'product.name',
    pc.Name AS 'product.category'
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID = 680
FOR JSON PATH;

Le résultat sera :

[{"product":{"id":680,"name":"HL Road Frame - Black, 58","category":"Road Frames"}}]

L’alias 'product.id' de colonne crée un objet imbriqué product avec une id propriété. Cette technique vous permet de mettre en forme la sortie pour qu’elle corresponde au format attendu de votre API sans post-traitement.

Pour plus d’informations sur les fonctions JSON dans SQL Server, consultez les données JSON dans SQL Server et JSON Functions.