OPENJSON (Transact-SQL)
Gilt für: 
SQL Server 2016 (13.x) und höhere Versionen Azure SQL-Datenbank Azure SQL verwaltete Instanz Azure Synapse Analytics SQL Analytics-Endpunkt in Microsoft Fabric Warehouse in Microsoft Fabric SQL-Datenbank in Microsoft Fabric SQL Stoff
OPENJSON ist eine Tabellenwertfunktion, die JSON-Text analysiert und Objekte und Eigenschaften aus der JSON-Eingabe als Zeilen und Spalten zurückgibt. Das heißt, dass OPENJSON eine Rowsetansicht eines JSON-Dokuments bereitstellt. Sie können die Spalten im Rowset und die JSON-Eigenschaftspfade zum Auffüllen der Spalten angeben. Da OPENJSON einen Satz von Zeilen zurückgibt, können Sie OPENJSON in der FROM-Klausel einer Transact-SQL-Anweisung nutzen, genauso wie Sie jede Tabelle, Ansicht oder Tabellenwertfunktion verwenden können.
Verwenden Sie OPENJSON, um die JSON-Daten in SQL Server zu importieren, oder um die JSON-Daten für eine Anwendung oder einen Dienst, die JSON nicht nutzen können, in ein relationales Format zu konvertieren.
Hinweis
Die OPENJSON-Funktion steht nur für den Kompatibilitätsgrad 130 oder höher zur Verfügung. Wenn der Kompatibilitätsgrad Ihrer Datenbank kleiner als 130 ist, kann SQL Server die OPENJSON-Funktion nicht finden und ausführen. Andere JSON-Funktionen sind für alle Kompatibilitätsgrade verfügbar.
Sie können den Kompatibilitätsgrad in der sys.databases-Ansicht oder in den Datenbankeigenschaften überprüfen. Sie können den Kompatibilitätsgrad einer Datenbank mithilfe des folgenden Befehls ändern:
ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130
Transact-SQL-Syntaxkonventionen
Syntax
OPENJSON( jsonExpression [ , path ] ) [ <with_clause> ]
<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )
Die Tabellenwertfunktion OPENJSON analysiert jsonExpression, was als erstes Argument bereitgestellt wird, und gibt eine oder mehrere Zeilen mit Daten aus den JSON-Objekten im Ausdruck zurück. jsonExpression kann geschachtelte, untergeordnete Objekte enthalten. Wenn Sie ein untergeordnetes Objekts innerhalb eines jsonExpression-Ausdrucks analysieren möchten, können Sie einen path-Parameter für das untergeordnete JSON-Objekt angeben.
openjson
Die Tabellenwertfunktion OPENJSON gibt standardmäßig drei Spalten zurück, die den Schlüsselnamen, den Wert und den Typ jedes {Schlüssel-Wert}-Paars im jsonExpression enthalten. Als Alternative können Sie das Schema des Ergebnissets, das OPENJSON zurückgibt, explizit angeben, indem Sie die with_clause bereitstellen.
with_clause
with_clause enthält eine Liste von Spalten mit Typen, die OPENJSON zurückgibt. Standardmäßig ordnet OPENJSON Schlüssel im jsonExpression den Spaltennamen in with_clause zu (in diesem Fall setzen Zuordnungen von Schlüsseln voraus, dass die Groß-/Kleinschreibung beachtet wird). Wenn ein Spaltenname und ein Schlüsselname nicht übereinstimmen, können Sie einen optionalen column_path bereitstellen. Dabei handelt es sich um einen JSON-Pfadausdruck, der auf einen Schlüssel innerhalb von jsonExpression verweist.
Argumente
jsonExpression
Ein Unicode-Zeichenausdruck, der JSON-Text enthält.
OPENJSON führt eine Iteration durch die Elemente eines Arrays oder die Eigenschaften des Objekts im JSON-Ausdruck durch, und gibt eine Zeile für jedes Element oder jede Eigenschaft zurück. Das folgende Beispiel gibt jede Eigenschaft des Objekts, das als jsonExpression bereitgestellt wird, zurück:
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);
Ergebnisse:
| Schlüssel | 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 |
- DoublePrecisionFloatingPoint_value entspricht IEEE-754.
path
Ist ein optionaler JSON-Pfadausdruck, der auf ein Objekt oder ein Array in jsonExpression verweist. OPENJSON sucht im JSON-Text an der angegebenen Position und analysiert nur das referenzierte Fragment. Weitere Informationen finden Sie unter JSON-Pfadausdrücke (SQL Server).
In SQL Server 2017 (14.x) und Azure SQL-Datenbank können Sie eine Variable als Wert von path bereitstellen.
Das folgende Beispiel gibt ein geschachteltes Objekt durch Angabe des path zurück:
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"')
Ergebnisse
| Schlüssel | Wert |
|---|---|
| 0 | en-GB |
| 1 | en-UK |
| 2 | de-AT |
| 3 | es-AR |
| 4 | sr-Cyrl |
Wenn OPENJSON ein JSON-Array analysiert, gibt die Funktion die Indizes der Elemente im JSON-Text als Schlüssel zurück.
Beim Vergleich, der zur Zuordnung von Pfadschritten zu den Eigenschaften des JSON-Ausdrucks verwendet wird, wird die Groß-/Kleinschreibung beachtet und die Sortierung nicht (d.h. ein BIN2-Vergleich).
Arrayelementidentität
Die OPENJSON-Funktion im serverlosen SQL-Pool in Azure Synapse Analytics kann automatisch die Identität aller Zeilen generieren, die als Ergebnis zurückgegeben werden. Die Identitätsspalte wird mithilfe des Ausdrucks $.sql:identity() im JSON-Pfad nach der Spaltendefinition angegeben. Die Spalte mit diesem Wert im JSON-Pfadausdruck generiert eine eindeutige 0-basierte Zahl für jedes Element im JSON-Array, das von der Funktion analysiert wird. Der Identitätswert stellt die Position/den Index des Arrayelements dar.
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
Ergebnisse
| month | temp | month_id |
|---|---|---|
| Jan | 10 | 0 |
| Feb | 12 | 1 |
| Mar | 15 | 2 |
| Apr | 17 | 3 |
| May | 23 | 4 |
| Jun | 27 | 5 |
Die Identität ist nur im serverlosen SQL-Pool in Synapse Analytics verfügbar.
with_clause
Definiert das Ausgabeschema explizit, das die OPENJSON-Funktion zurückgibt. Die optionale with_clause kann die folgenden Elemente enthalten:
colName ist der Name für die Ausgabespalte.
OPENJSON verwendet den Namen der Spalte standardmäßig, um eine Eigenschaft im JSON-Text zuzuordnen. Wenn Sie beispielsweise die Spalte Name im Schema angeben, versucht OPENJSON, diese Spalte mit der Eigenschaft „Name“ im JSON-Text zu füllen. Sie können diese Standardzuordnung mit dem column_path-Argument überschreiben.
type
Ist der Datentyp für die Ausgabespalte.
Hinweis
Wenn Sie auch die AS JSON-Option verwenden, muss die Spalte TypNVARCHAR(MAX) sein.
column_path
Ist der JSON-Pfad, der die zurückzugebende Eigenschaft in der angegebenen Spalte angibt. Weitere Informationen finden Sie in der Beschreibung der path-Parameter weiter oben in diesem Thema.
Verwenden Sie column_path zum Überschreiben von Standardzuordnungsregeln, wenn der Name einer Ausgabespalte nicht mit dem Namen der Eigenschaft übereinstimmt.
Beim Vergleich, der zur Zuordnung von Pfadschritten zu den Eigenschaften des JSON-Ausdrucks verwendet wird, wird die Groß-/Kleinschreibung beachtet und die Sortierung nicht (d.h. ein BIN2-Vergleich).
Weitere Informationen zu Pfaden finden Sie unter JSON-Pfadausdrücke (SQL Server).
AS JSON
Verwenden Sie die AS JSON-Option in einer Spaltendefinition, um anzugeben, dass die Eigenschaft, auf die verwiesen wird, ein inneres JSON-Objekt oder -Array enthält. Bei Angabe der AS JSON-Option muss der Typ der Spalte NVARCHAR(MAX) sein.
Wenn Sie keine AS JSON-Option für eine Spalte angeben, gibt die Funktion einen Skalarwert (z.B. int, string, TRUE, FALSE) aus der angegebenen JSON-Eigenschaft auf dem angegebenen Pfad zurück. Wenn der Pfad ein Objekt oder ein Array darstellt, und die Eigenschaft nicht unter dem angegebenen Pfad gefunden werden kann, gibt die Funktion NULL im Lax-Modus oder einen Fehler im Strict-Modus zurück. Dieses Verhalten ist vergleichbar mit dem Verhalten der JSON_VALUE-Funktion.
Wenn Sie eine AS JSON-Option für eine Spalte angeben, gibt die Funktion ein JSON-Fragment aus der angegebenen JSON-Eigenschaft auf dem angegebenen Pfad zurück. Wenn der Pfad einen Skalarwert darstellt, und die Eigenschaft nicht unter dem angegebenen Pfad gefunden werden kann, gibt die Funktion NULL im Lax-Modus oder ein Fehler im Strict-Modus zurück. Dieses Verhalten ist vergleichbar mit dem Verhalten der JSON_QUERY-Funktion.
Hinweis
Wenn Sie ein geschachteltes JSON-Fragment aus einer JSON-Eigenschaft zurückgegeben möchten, müssen Sie die AS JSON-Flag angeben. Wenn die Eigenschaft ohne diese Option nicht gefunden werden kann, gibt OPENJSON einen NULL-Wert anstelle des referenzierten JSON-Objekts oder -Arrays oder einen Laufzeitfehler im Strict-Modus zurück.
Die folgende Abfrage gibt beispielsweise die Elemente eines Arrays zurück und formatiert sie:
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
)
Ergebnisse
| Number | Date | Kunde | Menge | 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"} |
Rückgabewert
Die Spalten, die die OPENJSON-Funktion zurückgibt, hängen von der WITH-Option ab.
Wenn Sie OPENJSON mit dem Standardschema aufrufen, und kein explizites Schema in der WITH-Klausel angeben, gibt die Funktion eine Tabelle mit den folgenden Spalten zurück:
Schlüssel. Ein nvarchar(4000)-Wert, der den Namen der angegebenen Eigenschaft oder den Index des Elements im angegebenen Array enthält. Die Schlüsselspalte verfügt über eine BIN2-Sortierung.
Wert. Ein nvarchar(max)-Wert, der den Wert der Eigenschaft enthält. Die Wertspalte erbt die Sortierung aus jsonExpression.
Art: Ein int-Wert, der den Typ des Werts enthält. Die Typ-Spalte wird nur zurückgegeben, wenn Sie OPENJSON mit dem Standardschema verwenden. Die Typspalte besitzt einen der folgenden Werte:
Wert der Typspalte JSON-Datentyp 0 NULL 1 string 2 number 3 TRUE/FALSE 4 array 5 Objekt (object)
Es werden nur Eigenschaften der ersten Ebene zurückgegeben. Die Anweisung schlägt fehl, wenn der JSON-Text nicht ordnungsgemäß formatiert ist.
Wenn Sie OPENJSON aufrufen und ein explizites Schema in der WITH-Klausel angeben, gibt die Funktion eine Tabelle mit dem Schema zurück, das Sie in der WITH-Klausel definiert haben.
Hinweis
Die Spalten Key (Schlüssel), Value (Wert) und Type (Typ) werden nur zurückgegeben, wenn Sie OPENJSON mit dem Standardschema verwenden. Sie sind mit einem expliziten Schema nicht verfügbar.
Bemerkungen
json_path, das im zweiten Argument von OPENJSON oder in der with_clause verwendet wird, kann mit einem lax- oder strict-Schlüsselwort beginnen.
- Im Lax-Modus löst OPENJSON keinen Fehler aus, wenn das Objekt oder der Wert für den angegebenen Pfad nicht gefunden werden können. Wenn der Pfad nicht gefunden werden kann, gibt OPENJSON ein leeres Resultset oder einen NULL-Wert zurück.
- In strict-Modus gibt OPENJSON einen Fehler zurück, wenn der Pfad nicht gefunden werden kann.
Einige der Beispiele auf dieser Seite geben den Path-Modus explizit an, Lax oder Strict. Der Path-Modus ist optional. Wenn Sie nicht explizit einen Path-Modus angeben, ist der Lax-Modus die Standardeinstellung. Weitere Informationen zu Path-Modi und -Ausdrücken finden Sie unter JSON-Pfadausdrücke (SQL Server).
Spaltennamen in with_clause werden Schlüsseln im JSON-Text zugeordnet. Wenn Sie den Spaltennamen [Address.Country] angeben, wird er dem Address.Country-Schlüssel zugeordnet. Wenn Sie auf einen geschachtelten Schlüssel Country innerhalb des Address-Objekts verweisen möchten, müssen Sie den Pfad $.Address.Country in der Pfadspalte angeben.
json_path kann Schlüssel mit alphanumerischen Zeichen enthalten. Setzen Sie den Schlüsselnamen in json_path in doppelte Anführungszeichen, wenn Sie über Sonderzeichen in den Schlüsseln verfügen. Im folgenden JSON-Text ist $."my key $1".regularKey."key with . dot" beispielsweise dem Wert 1 zugeordnet:
{
"my key $1": {
"regularKey":{
"key with . dot": 1
}
}
}
Beispiele
Beispiel 1: Konvertieren eines JSON-Arrays in eine temporäre Tabelle
Das folgende Beispiel enthält eine Liste von Bezeichnern als JSON-Array der Zahlen. Die Abfrage konvertiert das JSON-Array in eine Tabelle von Bezeichnern und filtert alle Produkte mit den angegebenen IDs.
DECLARE @pSearchOptions NVARCHAR(4000) = N'[1,2,3,4]'
SELECT *
FROM products
INNER JOIN OPENJSON(@pSearchOptions) AS productTypes
ON product.productTypeID = productTypes.value
Diese Abfrage entspricht dem folgenden Beispiel. Im folgenden Beispiel müssen Sie jedoch Zahlen in die Abfrage einbetten, anstatt sie als Parameter zu übergeben.
SELECT *
FROM products
WHERE product.productTypeID IN (1,2,3,4)
Beispiel 2: Zusammenführen von Eigenschaften aus zwei JSON-Objekten
Das folgende Beispiel wählt eine Vereinigung aller Eigenschaften aus zwei JSON-Objekten aus. Die beiden Objekte verfügen über eine doppelte name-Eigenschaft. Im Beispiel wird der Schlüsselwert verwendet, um die doppelte Zeile aus den Ergebnissen auszuschließen.
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))
Beispiel 3: Verknüpfen von Zeilen und JSON-Daten, die in Tabellenzellen gespeichert sind, mithilfe von CROSS APPLY
Im folgenden Beispiel besitzt die SalesOrderHeader-Tabelle eine SalesReason-Textspalte, die ein SalesOrderReasons-Array im JSON-Format enthält. Die SalesOrderReasons-Objekte enthalten Eigenschaften wie Qualität und Hersteller. Das Beispiel erstellt einen Bericht, der jede Zeile der Bestellung und die zugehörigen Verkaufsgründe verknüpft. Der OPENJSON-Operator erweitert das JSON-Array von Verkaufsgründen, als ob die Gründe in einer separaten untergeordneten Tabelle gespeichert wären. Der CROSS APPLY-Operator verknüpft dann jede Verkaufszeile der Bestellung mit den von der OPENJSON-Tabellenwertfunktion zurückgegebenen Zeilen.
SELECT SalesOrderID,OrderDate,value AS Reason
FROM Sales.SalesOrderHeader
CROSS APPLY OPENJSON(SalesReasons)
Tipp
Wenn Sie in den einzelnen Feldern gespeicherte JSON-Arrays erweitern müssen und diesemit ihren übergeordneten Zeilen verknüpfen, verwenden Sie in der Regel den CROSS APPLY-Operator von Transact-SQL. Weitere Informationen zu CROSS APPLY finden Sie unter FROM (Transact-SQL).
Dieselbe Abfrage kann umgeschrieben werden, indem Sie OPENJSON mit einem explizit definierten Schema der zurückzugebenden Zeilen verwenden:
SELECT SalesOrderID, OrderDate, value AS Reason
FROM Sales.SalesOrderHeader
CROSS APPLY OPENJSON (SalesReasons) WITH (value NVARCHAR(100) '$')
In diesem Beispiel verweist der $-Pfad auf jedes Element im Array. Wenn der zurückgegebene Wert explizit umgewandelt werden soll, können Sie diese Art der Abfrage verwenden.
Beispiel 4: Kombinieren der relationalen Zeilen und JSON-Elemente mit CROSS APPLY
Die folgende Abfrage kombiniert relationale Zeilen und JSON-Elemente zu den in der folgenden Tabelle dargestellten Ergebnissen.
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
Ergebnisse
| title | street | postcode | 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 |
Beispiel 5: Importieren von JSON-Daten in SQL Server
Im folgenden Beispiel wird ein komplettes JSON-Objekt in eine SQL Server -Tabelle geladen.
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))
Beispiel 6: Einfaches Beispiel mit JSON-Inhalten
--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