OPENJSON (Transact-SQL)

Article
05/23/2023

S’applique à : point de terminaison d’analytique SQL Server 2016 (13.x) et ultérieur Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL Analytics dans Microsoft Fabric Warehouse

OPENJSON est une fonction table qui analyse du texte JSON et retourne des objets et propriétés à partir de l’entrée JSON sous forme de lignes et de colonnes. Autrement dit, OPENJSON offre une vue de l’ensemble de lignes d’un document JSON. Vous pouvez spécifier explicitement les colonnes dans l’ensemble de lignes et les chemins de propriétés JSON utilisés pour remplir les colonnes. Étant donné que OPENJSON retourne un ensemble de lignes, vous pouvez utiliser OPENJSON dans la clause FROM d’une instruction Transact-SQL, tout comme vous pouvez utiliser n’importe quelle autre table, vue ou fonction table.

Utilisez OPENJSON pour importer des données JSON dans SQL Server ou pour les convertir au format relationnel pour une application ou un service qui ne peut pas les consommer directement.

Notes

La fonction OPENJSON est disponible uniquement sous le niveau de compatibilité 130 ou plus. Si le niveau de compatibilité de votre base de données est inférieur à 130, SQL Server ne peut pas trouver ni exécuter la fonction OPENJSON. Les autres fonctions JSON 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 base de données. Vous pouvez changer le niveau de compatibilité d’une base de données à l’aide de la commande suivante :

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Conventions de la syntaxe Transact-SQL

Syntaxe

OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]

<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )

La fonction table OPENJSON analyse l’expression jsonExpression fournie comme premier argument et retourne une ou plusieurs lignes contenant des données des objets JSON dans l’expression. jsonExpression peut contenir des sous-objets imbriqués. Si vous voulez analyser un sous-objet depuis jsonExpression, vous pouvez spécifier un paramètre path pour ce sous-objet JSON.

openjson

Syntaxe pour OPENJSON TVF

Par défaut, la fonction table OPENJSON retourne trois colonnes qui contiennent le nom de la clé, la valeur et le type de chaque paire {clé:valeur} trouvée dans jsonExpression. En guise d’alternative, vous pouvez spécifier explicitement le schéma du jeu de résultats retourné par OPENJSON en fournissant with_clause.

with_clause

Syntaxe pour la clause WITH dans OPENJSON TVF

with_clause contient la liste des colonnes avec leurs types que OPENJSON doit retourner. Par défaut, OPENJSON fait correspondre les clés dans jsonExpression avec les noms de colonne dans with_clause (dans ce cas, les clés correspondantes impliquent un respect de la casse). Si un nom de colonne ne correspond pas à un nom de clé, vous pouvez fournir un column_path facultatif, qui est une expression de chemin JSON qui fait référence à une clé dans jsonExpression.

Arguments

jsonExpression

Expression de caractères Unicode contenant du texte JSON.

OPENJSON effectue une itération sur les éléments du tableau ou les propriétés de l’objet dans l’expression JSON et renvoie une seule ligne pour chaque élément ou propriété. L’exemple suivant retourne chaque propriété de l’objet fourni en tant que jsonExpression :

DECLARE @json NVARCHAR(2048) = N'{
   "String_value": "John",
   "DoublePrecisionFloatingPoint_value": 45,
   "DoublePrecisionFloatingPoint_value": 2.3456,
   "BooleanTrue_value": true,
   "BooleanFalse_value": false,
   "Null_value": null,
   "Array_value": ["a","r","r","a","y"],
   "Object_value": {"obj":"ect"}
}';

SELECT * FROM OpenJson(@json);

Résultats :

key	value	type
String_value	John	1
DoublePrecisionFloatingPoint_value	45	2
DoublePrecisionFloatingPoint_value	2.3456	2
BooleanTrue_value	true	3
BooleanFalse_value	false	3
Null_value	NULL	0
Array_value	["a","r","r","a","y"]	4
Object_value	{"obj":"ect"}	5

La DoublePrecisionFloatingPoint_value est conforme à la norme IEEE-754.

path

Expression de chemin JSON facultative qui fait référence à un objet ou un tableau dans jsonExpression. OPENJSON recherche dans le texte JSON à la position spécifiée et analyse uniquement le fragment référencé. Pour plus d’informations, consultez Expressions de chemin d’accès JSON (SQL Server).

Dans SQL Server 2017 (14.x) et Azure SQL Database, vous pouvez fournir une variable comme valeur de path.

L’exemple suivant retourne un objet imbriqué en spécifiant le chemin :

DECLARE @json NVARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

SELECT [key], value
FROM OPENJSON(@json,'$.path.to."sub-object"')

Résultats

Clé	Valeur
0	en-GB
1	en-UK
2	de-AT
3	es-AR
4	sr-Cyrl

Quand OPENJSON analyse un tableau JSON, la fonction retourne les index des éléments dans le texte JSON en tant que clés.

La comparaison utilisée pour faire correspondre les étapes de chemin aux propriétés de l’expression JSON respecte la casse et ne tient pas compte du classement (autrement dit, il s’agit d’une comparaison BIN2).

Identité de l’élément de tableau

la fonction OPENJSON du pool SQL sans serveur dans Azure Synapse Analytics peut générer automatiquement l’identité de chaque ligne retournée en tant que résultat. La colonne d’identité est spécifiée à l’aide de l’expression $.sql:identity() dans le chemin JSON après la définition de la colonne. La colonne avec cette valeur dans l’expression de chemin JSON génère un nombre unique de base zéro pour chaque élément du tableau JSON analysé par la fonction. La valeur d’identité représente la position ou l’index de l’élément de tableau.

DECLARE @array VARCHAR(MAX);
SET @array = '[{"month":"Jan", "temp":10},{"month":"Feb", "temp":12},{"month":"Mar", "temp":15},
               {"month":"Apr", "temp":17},{"month":"May", "temp":23},{"month":"Jun", "temp":27}
              ]';

SELECT * FROM OPENJSON(@array)
        WITH (  month VARCHAR(3),
                temp int,
                month_id tinyint '$.sql:identity()') as months

Résultats

month	temp	month_id
Jan	10	0
Fév	12	1
Mar	15	2
Avr	17	3
Mai	23	4
Jun	27	5

L’identité est disponible uniquement dans le pool SQL sans serveur dans Synapse Analytics.

with_clause

Définit explicitement le schéma de sortie pour la fonction OPENJSON à retourner. with_clause peut contenir les éléments suivants :

colName est le nom de la colonne de sortie.

Par défaut, OPENJSON utilise le nom de la colonne pour rechercher une propriété dans le texte JSON. Par exemple, si vous spécifiez la colonne name dans le schéma, OPENJSON essaie de remplir cette colonne avec la propriété « name » dans le texte JSON. Vous pouvez remplacer ce mappage par défaut à l’aide de l’argument column_path.

type
Type de données de la colonne de sortie.

Notes

Si vous utilisez également l’option AS JSON, le type de la colonne doit être NVARCHAR(MAX).

column_path
Chemin JSON qui spécifie la propriété à retourner dans la colonne spécifiée. Pour plus d’informations, consultez la description du paramètre path plus haut dans cette rubrique.

Utilisez column_path pour remplacer les règles de mappage par défaut lorsque le nom d’une colonne de sortie ne correspond pas à celui de la propriété.

Pour plus d’informations sur les chemins, consultez Expressions de chemin JSON (SQL Server).

AS JSON
Utilisez l’option AS JSON dans une définition de colonne pour spécifier que la propriété référencée contient un objet ou tableau JSON interne. Si vous spécifiez l’option AS JSON, le type de la colonne doit être NVARCHAR(MAX).

Si vous ne spécifiez pas AS JSON pour une colonne, la fonction retourne une valeur scalaire (par exemple, int, string, true, false) à partir de la propriété JSON spécifiée dans le chemin spécifié. Si le chemin représente un objet ou un tableau et que la propriété est introuvable dans le chemin spécifié, la fonction retourne la valeur Null en mode lax ou une erreur en mode strict. Ce comportement est semblable à celui de la fonction JSON_VALUE.
Si vous spécifiez AS JSON pour une colonne, la fonction retourne un fragment JSON à partir de la propriété JSON spécifiée dans le chemin spécifié. Si le chemin représente une valeur scalaire et que la propriété est introuvable dans le chemin spécifié, la fonction retourne la valeur Null en mode lax ou une erreur en mode strict. Ce comportement est semblable à celui de la fonction JSON_QUERY.

Notes

Si vous voulez renvoyer un fragment JSON imbriqué à partir d’une propriété JSON, vous devez fournir l’indicateur AS JSON. Sans cette option, si la propriété est introuvable, OPENJSON retourne une valeur NULL au lieu de l’objet ou du tableau JSON référencé, ou bien une erreur d’exécution est renvoyée en mode strict.

Par exemple, la requête suivante retourne et formate les éléments d’un tableau :

DECLARE @json NVARCHAR(MAX) = N'[  
  {  
    "Order": {  
      "Number":"SO43659",  
      "Date":"2011-05-31T00:00:00"  
    },  
    "AccountNumber":"AW29825",  
    "Item": {  
      "Price":2024.9940,  
      "Quantity":1  
    }  
  },  
  {  
    "Order": {  
      "Number":"SO43661",  
      "Date":"2011-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',  
              [Order]  NVARCHAR(MAX)  AS JSON  
 )

Résultats

Number	Date	Customer	Quantité	JSON
SO43659	2011-05-31T00:00:00	AW29825	1	{"Number":"SO43659","Date":"2011-05-31T00:00:00"}
SO43661	2011-06-01T00:00:00	AW73565	3	{"Number":"SO43661","Date":"2011-06-01T00:00:00"}

Valeur retournée

Les colonnes que la fonction OPENJSON retourne dépendent de l’option WITH.

Lorsque vous appelez OPENJSON avec le schéma par défaut (autrement dit, lorsque vous ne spécifiez pas un schéma explicite dans la clause WITH), la fonction retourne une table comportant les colonnes suivantes :
1. Key. Valeur nvarchar(4000) qui contient le nom de la propriété spécifiée ou de l’index de l’élément dans le tableau spécifié. Le classement de la colonne Key est BIN2.
2. Valeur. Valeur nvarchar(max) qui contient la valeur de la propriété. La colonne de valeur hérite son classement de jsonExpression.
3. Type. Valeur entière qui contient le type de la valeur. La colonne Type est retournée uniquement lorsque vous utilisez OPENJSON avec le schéma par défaut. La colonne Type a l’une des valeurs suivantes :
  
  Valeur de la colonne Type Type de données JSON
  
  0 null
  
  1 string
  
  2 nombre
  
  3 true/false
  
  4 tableau
  
  5 object
Seules les propriétés de premier niveau sont retournées. L’instruction échoue si le texte JSON n’est pas formaté correctement.
Lorsque vous appelez OPENJSON et que vous spécifiez un schéma explicite dans la clause WITH, la fonction retourne une table avec le schéma que vous avez défini dans la clause WITH.

Valeur de la colonne Type	Type de données JSON
0	null
1	string
2	nombre
3	true/false
4	tableau
5	object

Notes

Les colonnes Clé, Valeur et Type ne sont retournées que lorsque vous utilisez OPENJSON avec le schéma par défaut et ne sont pas disponibles avec un schéma explicite.

Notes

Le chemin json_path utilisé dans le deuxième argument OPENJSON ou dans with_clause peut commencer par le mot clé lax ou strict.

En mode lax, OPENJSON ne déclenche pas d’erreur si l’objet ou la valeur dans le chemin spécifié est introuvable. Si le chemin est introuvable, OPENJSON retourne un jeu de résultats vide ou une valeur NULL.
En mode strict, OPENJSON renvoie une erreur si le chemin est introuvable.

Certains exemples de cette page spécifient explicitement le mode path, lax ou strict. Le mode path est facultatif. Si vous ne spécifiez pas explicitement un mode path, le mode lax est la valeur par défaut. Pour plus d’informations sur le mode path et les expressions de chemin, consultez Expressions de chemin JSON (SQL Serverà).

Les noms de colonne dans with_clause sont mis en correspondance avec les clés dans le texte JSON. Si vous spécifiez le nom de colonne [Address.Country], il est mis en correspondance avec la clé Address.Country. Si vous voulez faire référence à une clé imbriquée Country au sein de l’objet Address, vous devez spécifier le chemin $.Address.Country dans le chemin de la colonne.

json_path peut contenir des clés avec des caractères alphanumériques. Échappez le nom de la clé dans json_path avec des guillemets doubles si vous avez des caractères spéciaux dans les clés. Par exemple, $."my key $1".regularKey."key with . dot" correspond à la valeur 1 dans le texte JSON suivant :

{
  "my key $1": {
    "regularKey":{
      "key with . dot": 1
    }
  }
}

Exemples

Exemple 1 : convertir un tableau JSON en table temporaire

L’exemple suivant fournit la liste des identificateurs sous la forme d’un tableau JSON de nombres. La requête convertit le tableau JSON en table des identificateurs et filtre tous les produits avec les ID spécifiés.

DECLARE @pSearchOptions NVARCHAR(4000) = N'[1,2,3,4]'

SELECT *
FROM products
INNER JOIN OPENJSON(@pSearchOptions) AS productTypes
 ON product.productTypeID = productTypes.value

Cette requête équivaut à l’exemple suivant. Toutefois, dans l’exemple ci-dessous, vous devez incorporer des nombres dans la requête au lieu de les passer en tant que paramètres.

SELECT *
FROM products
WHERE product.productTypeID IN (1,2,3,4)

Exemple 2 : fusionner des propriétés de deux objets JSON

L’exemple suivant sélectionne une union de toutes les propriétés de deux objets JSON. Les deux objets ont une propriété name en double. L’exemple utilise la valeur de clé pour exclure la ligne en double des résultats.

DECLARE @json1 NVARCHAR(MAX),@json2 NVARCHAR(MAX)

SET @json1=N'{"name": "John", "surname":"Doe"}'

SET @json2=N'{"name": "John", "age":45}'

SELECT *
FROM OPENJSON(@json1)
UNION ALL
SELECT *
FROM OPENJSON(@json2)
WHERE [key] NOT IN (SELECT [key] FROM OPENJSON(@json1))

Exemple 3 : joindre des lignes avec des données JSON stockées dans les cellules d’une table à l’aide de CROSS APPLY

Dans l’exemple suivant, la table SalesOrderHeader a une colonne de texte SalesReason qui contient un tableau de SalesOrderReasons au format JSON. Les objets SalesOrderReasons contiennent des propriétés telles que Quality et Manufacturer. L’exemple crée un rapport qui joint chaque ligne de commande client au motif de vente associé. L’opérateur OPENJSON développe le tableau JSON des motifs de vente comme si les motifs étaient stockés dans une table enfant distincte. Ensuite, l’opérateur CROSS APPLY joint chaque ligne de commande client aux lignes retournées par la fonction table OPENJSON.

SELECT SalesOrderID,OrderDate,value AS Reason
FROM Sales.SalesOrderHeader
CROSS APPLY OPENJSON(SalesReasons)

Conseil

Lorsque vous devez développer des tableaux JSON stockés dans des champs individuels et les joindre à leurs lignes parents, vous utilisez généralement l’opérateur Transact-SQL CROSS APPLY. Pour plus d’informations sur CROSS APPLY, consultez FROM (Transact-SQL).

La même requête peut être réécrite à l’aide de OPENJSON avec un schéma explicitement défini de lignes à retourner :

SELECT SalesOrderID, OrderDate, value AS Reason  
FROM Sales.SalesOrderHeader  
     CROSS APPLY OPENJSON (SalesReasons) WITH (value NVARCHAR(100) '$')

Dans cet exemple, le chemin $ fait référence à chaque élément du tableau. Pour caster explicitement la valeur retournée, vous pouvez utiliser ce type de requête.

Exemple 4 : combiner des lignes relationnelles et des éléments JSON avec CROSS APPLY

La requête suivante combine des lignes relationnelles et des éléments JSON dans les résultats présentés dans le tableau suivant.

SELECT store.title, location.street, location.lat, location.long  
FROM store  
CROSS APPLY OPENJSON(store.jsonCol, 'lax $.location')   
     WITH (street VARCHAR(500) ,  postcode VARCHAR(500) '$.postcode' ,  
     lon int '$.geo.longitude', lat int '$.geo.latitude')  
     AS location

Résultats

title	street	code postal	lon	lat
Whole Food Markets	17991 Redmond Way	WA 98052	47.666124	-122.10155
Sears	148th Ave NE	WA 98052	47.63024	-122.141246,17

Exemple 5 : importer des données JSON dans SQL Server

L’exemple suivant charge l’intégralité d’un objet JSON dans une table SQL Server .

DECLARE @json NVARCHAR(max)  = N'{  
  "id" : 2,  
  "firstName": "John",  
  "lastName": "Smith",  
  "isAlive": true,  
  "age": 25,  
  "dateOfBirth": "2015-03-25T12:00:00",  
  "spouse": null  
  }';  
   
  INSERT INTO Person  
  SELECT *   
  FROM OPENJSON(@json)  
  WITH (id INT,  
        firstName NVARCHAR(50), lastName NVARCHAR(50),   
        isAlive BIT, age INT,  
        dateOfBirth DATETIME, spouse NVARCHAR(50))

Exemple 6 : exemple simple avec du contenu JSON

--simple cross apply example
DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]'

SELECT root.[key] AS [Order],TheValues.[key], TheValues.[value]
FROM OPENJSON ( @JSON ) AS root
CROSS APPLY OPENJSON ( root.value) AS TheValues

Partager via

OPENJSON (Transact-SQL)

Syntaxe

openjson

with_clause

Arguments

jsonExpression

path

Identité de l’élément de tableau

with_clause

Valeur retournée

Notes

Exemples

Exemple 1 : convertir un tableau JSON en table temporaire

Exemple 2 : fusionner des propriétés de deux objets JSON

Exemple 3 : joindre des lignes avec des données JSON stockées dans les cellules d’une table à l’aide de CROSS APPLY

Exemple 4 : combiner des lignes relationnelles et des éléments JSON avec CROSS APPLY

Exemple 5 : importer des données JSON dans SQL Server

Exemple 6 : exemple simple avec du contenu JSON

Voir aussi

Commentaires

Ressources supplémentaires