OPENJSON en T‑SQL

OPENJSON est une fonction table permettant de lire un document JSON (ou un chemin spécifique) et de retourner une table de paires (key, value, type). Avec la clause WITH, on peut projeter des colonnes typées et mappées sur des chemins JSON.

Cas d’usage

Projection typée avec WITH

Extraire des champs scalaires d’un JSON sans écrire de parsing manuel.

-- Sample data
DECLARE @payload NVARCHAR(MAX) = N'{"id":1,"name":"Alice","tags":["vip","tech"]}';

-- Project properties using WITH mapping and paths
SELECT
  J.Id,
  J.Name
FROM OPENJSON(@payload)
WITH (
  Id INT        '$.id',
  Name NVARCHAR(50) '$.name'
) AS J;

Résultat attendu

Id  Name
--  -----
1   Alice

Parcourir un tableau JSON (CROSS APPLY)

Lire chaque élément du tableau et projeter ses propriétés.

-- Sample data
DECLARE @payload NVARCHAR(MAX) = N'{"items":[{"sku":"KB","qty":1},{"sku":"MS","qty":3}]}';

-- Read array elements with CROSS APPLY
SELECT
  ITM.[key]        AS ItemIndex,
  ITM.value        AS ItemJson,
  X.Sku,
  X.Qty
FROM OPENJSON(@payload, '$.items') AS ITM
CROSS APPLY OPENJSON(ITM.value)
WITH (
  Sku NVARCHAR(10) '$.sku',
  Qty INT          '$.qty'
) AS X;

Résultat attendu

ItemIndex  ItemJson                              Sku  Qty
---------  ----------------------------------------  ---  ---
0          {"sku":"KB","qty":1}                     KB   1
1          {"sku":"MS","qty":3}                     MS   3

Parsing par ligne avec OUTER APPLY

Appliquer OPENJSON sur une colonne JSON par enregistrement et conserver les lignes NULL.

-- Sample table
CREATE TABLE dbo.Events (
  Id INT PRIMARY KEY,
  Payload NVARCHAR(MAX)
);
INSERT INTO dbo.Events(Id, Payload) VALUES
  (1, '{"country":"FR","score":42}'),
  (2, NULL);

-- OUTER APPLY preserves rows with NULL payload
SELECT
  E.Id,
  J.[key] AS Prop,
  J.value AS Val
FROM dbo.Events AS E
OUTER APPLY OPENJSON(E.Payload) AS J;

Résumé

Utilisez OPENJSON pour transformer du JSON en lignes/colonnes SQL, avec WITH pour un schéma typé. Combinez‑le avec CROSS/OUTER APPLY pour un parsing par ligne.

Avantages

• Mapping typé et explicite (WITH)
• Lecture de tableaux et d’objets imbriqués
• Intégration naturelle avec APPLY

Inconvénients

• Dépend de SQL Server 2016+
• Peut nécessiter des index/optimisations si volumineux
• Attention aux chemins et aux valeurs NULL

Bonnes pratiques

• Valider la structure JSON côté applicatif quand c’est possible
• Privilégier WITH pour des colonnes typées (meilleure lisibilité/optimisation)
• Limiter la taille des documents JSON stockés
• Indexer les colonnes corrélées si vous joignez le résultat