OPENJSON (Transact-SQL)

S’applique à : SQL Server 2016 (13.x) et versions ultérieures d’Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL Analytics dans Microsoft Fabric Warehouse dans microsoft Fabric SQL Database dans Microsoft Fabric SQL Database dans Microsoft Fabric

La OPENJSON fonction table analyse le texte JSON et retourne des objets et des propriétés de l’entrée JSON sous forme de lignes et de colonnes. En d’autres termes, OPENJSON fournit une vue d’ensemble de lignes sur 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. Comme OPENJSON retourne un ensemble de lignes, vous pouvez utiliser OPENJSON dans la FROM clause d’une instruction Transact-SQL, tout comme vous pouvez utiliser n’importe quelle autre fonction table, vue ou table.

Permet OPENJSON d’importer des données JSON dans SQL Server ou de convertir des données JSON au format relationnel pour une application ou un service qui ne peut pas utiliser JSON directement.

Note

La OPENJSON fonction est disponible uniquement au niveau de compatibilité 130 ou supérieur. Si votre niveau de compatibilité de base de données est inférieur à 130, SQL Server ne peut pas trouver et exécuter la OPENJSON fonction. 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

Syntax

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

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

La OPENJSON fonction table analyse le jsonExpression fourni en tant que premier argument et retourne une ou plusieurs lignes contenant des données à partir des objets JSON de l’expression. jsonExpression peut contenir des sous-objets imbriqués. Si vous souhaitez analyser un sous-objet à partir de jsonExpression, vous pouvez spécifier un paramètre de chemin d’accès pour le sous-objet JSON.

openjson

Diagramme de la syntaxe pour OPENJSON TVF.

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

with_clause

Diagramme de la syntaxe de la clause WITH dans OPENJSON TVF.

Le with_clause contient une liste de colonnes avec leurs types à OPENJSON retourner. Par défaut, OPENJSON correspond aux clés dans jsonExpression avec les noms de colonnes dans with_clause (dans ce cas, correspond aux clés implique qu’il respecte 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);

Results:

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 référence un objet ou un tableau au sein de 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 JSON.

Vous pouvez fournir une variable comme valeur de chemin d’accès. (Cela n’est pas pris en charge dans SQL Server 2016 (13.x) et les versions antérieures.)

L’exemple suivant retourne un objet imbriqué en spécifiant le chemin d’accès :

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"')

Results

Key	Value
0	en-GB
1	en-UK
2	de-AT
3	es-AR
4	sr-Cyrl

Lors OPENJSON de l’analyse d’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

Results

month	temp	month_id
Jan	10	0
Feb	12	1
Mar	15	2
Apr	17	3
May	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 de la OPENJSON fonction à retourner. Le with_clause facultatif peut contenir les éléments suivants :

colName

Nom de la colonne de sortie.

Par défaut, OPENJSON utilise le nom de la colonne pour faire correspondre une propriété dans le texte JSON. Par exemple, si vous spécifiez la colonne name dans le schéma, OPENJSON tente 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 pour la colonne de sortie.

Note

Si vous utilisez également l’option AS JSON , le type de données de 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 précédemment 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 au nom de la propriété.

Pour plus d’informations sur les chemins d’accès, consultez expressions de chemin JSON.

EN TANT QUE JSON

Utilisez l’option AS JSON d’une définition de colonne pour spécifier que la propriété référencée contient un objet ou un 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 AS JSON pas pour une colonne, la fonction retourne une valeur scalaire (par exemple, int, string, true, false) à partir de la propriété JSON spécifiée sur le chemin spécifié. Si le chemin représente un objet ou un tableau et que la propriété est introuvable sur le chemin spécifié, la fonction retourne NULL en lax mode ou retourne une erreur en strict mode. Ce comportement est similaire au comportement de la JSON_VALUE fonction.
Si vous spécifiez AS JSON pour une colonne, la fonction retourne un fragment JSON à partir de la propriété JSON spécifiée sur le chemin spécifié. Si le chemin représente une valeur scalaire et que la propriété est introuvable sur le chemin spécifié, la fonction retourne NULL en lax mode ou retourne une erreur en strict mode. Ce comportement est similaire au comportement de la JSON_QUERY fonction.

Note

Si vous souhaitez retourner 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 NULL valeur au lieu de l’objet JSON ou du tableau référencé, ou retourne une erreur d’exécution en strict mode.

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  
 )

Results

Number	Date	Customer	Quantity	Order
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 retournées par la OPENJSON fonction 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 de schéma explicite dans la WITH clause , la fonction retourne une table avec les colonnes suivantes :
- Key. Valeur nvarchar(4000) qui contient le nom de la propriété spécifiée ou l’index de l’élément dans le tableau spécifié. La key colonne a un classement BIN2.
- Value. Valeur nvarchar(MAX) qui contient la valeur de la propriété. La value colonne hérite de son classement de jsonExpression.
- Type. Valeur int qui contient le type de la valeur. La Type colonne est retournée uniquement lorsque vous utilisez OPENJSON le schéma par défaut. La type colonne a l’une des valeurs suivantes :
  
  Valeur de la colonne Type Type de données JSON
  
  0 null
  
  1 string
  
  2 number
  
  3 true/false
  
  4 array
  
  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 WITH clause, la fonction retourne une table avec le schéma que vous avez défini dans la WITH clause.

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

Note

Les Keycolonnes et Value les colonnes Typesont retournées uniquement lorsque vous utilisez OPENJSON le schéma par défaut et ne sont pas disponibles avec un schéma explicite.

Remarks

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

En lax mode, OPENJSON ne génère pas d’erreur si l’objet ou la valeur sur le chemin spécifié est introuvable. Si le chemin d’accès est introuvable, OPENJSON retourne un jeu de résultats vide ou une NULL valeur.
Dans strict, le mode OPENJSON retourne une erreur si le chemin d’accès est introuvable.

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

Les noms de colonnes dans with_clause sont mis en correspondance avec des 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 clé dans json_path avec des guillemets doubles si vous avez des caractères spéciaux dans les touches. 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
    }
  }
}

Examples

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é de nom 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 SalesOrderReasons objets contiennent des propriétés telles que Qualité et Fabricant. 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 raisons de vente comme si les raisons étaient stockées dans une table enfant distincte. CROSS APPLY L’opérateur joint ensuite chaque ligne de commande aux lignes retournées par la OPENJSON fonction table.

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

Tip

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

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

Results

title	street	postcode	lon	lat
Marchés alimentaires entiers	17991 Redmond Way	WA 98052	47.666124	-122.10155
Sears	148e 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

Rétroaction

Cette page vous a-t-elle été utile ?

Last updated on 2025-11-18

Partager via

OPENJSON (Transact-SQL)

Syntax

openjson

with_clause

Arguments

jsonExpression

path

Identité de l’élément de tableau

with_clause

colName

type

column_path

EN TANT QUE JSON

Valeur retournée

Remarks

Examples

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

Contenu connexe

Rétroaction

Ressources additionnelles