Microsoft SQL Server
OPENJSON

Get key: paires de valeurs à partir du texte JSON

La fonction OPENJSON analyse le texte JSON et renvoie toutes les paires clé-valeur au premier niveau de JSON:

declare @json NVARCHAR(4000) = N'{"Name":"Joe","age":27,"skills":["C#","SQL"]}';
SELECT * FROM OPENJSON(@json);

Le type de colonne décrit le type de valeur, à savoir null (0), string (1), number (2), boolean (3), array (4) et object (5).

Transformer le tableau JSON en un ensemble de lignes

La fonction OPENJSON analyse la collection d'objets JSON et renvoie des valeurs à partir du texte JSON sous la forme d'un ensemble de lignes.

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00","Customer": "MSFT","Price":59.99,"Quantity":1},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00","Customer":"Nokia","Price":24.99,"Quantity":3}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200),
          Date     datetime,
          Customer varchar(200),
          Quantity int
  )

Dans la clause WITH est spécifié le schéma de retour de la fonction OPENJSON. Les clés des objets JSON sont extraites par les noms de colonne. Si une clé dans JSON n'est pas spécifiée dans la clause WITH (par exemple Price dans cet exemple), elle sera ignorée. Les valeurs sont automatiquement converties en types spécifiés.

Transformer des champs JSON imbriqués en un ensemble de lignes

La fonction OPENJSON analyse la collection d'objets JSON et renvoie des valeurs à partir du texte JSON sous la forme d'un ensemble de lignes. Si les valeurs de l'objet d'entrée sont imbriquées, un paramètre de mappage supplémentaire peut être spécifié dans chaque colonne de la clause WITH:

declare @json nvarchar(4000) = N'[
  {"data":{"num":"SO43659","date":"2011-05-31T00:00:00"},"info":{"customer":"MSFT","Price":59.99,"qty":1}},
  {"data":{"number":"SO43661","date":"2011-06-01T00:00:00"},"info":{"customer":"Nokia","Price":24.99,"qty":3}}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200) '$.data.num',
          Date     datetime '$.data.date',
          Customer varchar(200) '$.info.customer',
          Quantity int '$.info.qty',
  )

Dans la clause WITH est spécifié le schéma de retour de la fonction OPENJSON. Après le type est spécifié chemin d'accès aux noeuds JSON où la valeur renvoyée doit être trouvée. Les clés des objets JSON sont extraites par ces chemins. Les valeurs sont automatiquement converties en types spécifiés.

Extraire des sous-objets JSON internes

OPENJSON peut extraire des fragments d'objets JSON dans le texte JSON. Dans la définition de colonne qui référence le sous-objet JSON, définissez les options de type nvarchar (max) et AS JSON:

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00","info":{"customer":"MSFT","Price":59.99,"qty":1}},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00","info":{"customer":"Nokia","Price":24.99,"qty":3}}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200),
          Date     datetime,
          Info nvarchar(max) '$.info' AS JSON
  )

La colonne Info sera mappée à l'objet "Info". Les résultats seront:

Travailler avec des sous-tableaux JSON imbriqués

JSON peut avoir une structure complexe avec des tableaux internes. Dans cet exemple, nous avons un tableau d'ordres avec des sous-tableaux imbriqués de OrderItems.

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00",
    "Items":[{"Price":11.99,"Quantity":1},{"Price":12.99,"Quantity":5}]},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00",
    "Items":[{"Price":21.99,"Quantity":3},{"Price":22.99,"Quantity":2},{"Price":23.99,"Quantity":2}]}
]'

Nous pouvons analyser les propriétés de niveau racine en utilisant OPENJSON qui renverra le fragment AS du tableau AS JSON. Ensuite, nous pouvons appliquer OPENJSON à nouveau sur le tableau Items et ouvrir la table JSON interne. La table de premier niveau et la table interne seront "jointes" comme dans la jointure entre les tables standard:

SELECT    * 
FROM
    OPENJSON (@json)
    WITH (  Number varchar(200), Date datetime,
            Items nvarchar(max) AS JSON )
        CROSS APPLY 
            OPENJSON (Items)
            WITH ( Price float, Quantity int)

Résultats: