OPENJSON (Transact-SQL)

Artikel
05/23/2023

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-Datenbank Azure SQL verwaltete Instanz Azure Synapse Analytics SQL Analytics-Endpunkt in Microsoft Fabric Warehouse in Microsoft Fabric In Microsoft Fabric

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

Syntax für OPENJSON TVF

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

Syntax für WITH-Klausel in OPENJSON TVF

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.

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:
1. 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.
2. Wert. Ein nvarchar(max)-Wert, der den Wert der Eigenschaft enthält. Die Wertspalte erbt die Sortierung aus jsonExpression.
3. 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.

Wert der Typspalte	JSON-Datentyp
0	NULL
1	string
2	number
3	TRUE/FALSE
4	array
5	Objekt (object)

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

Teilen über

OPENJSON (Transact-SQL)

Syntax

openjson

with_clause

Argumente

jsonExpression

path

Arrayelementidentität

with_clause

Rückgabewert

Bemerkungen

Beispiele

Beispiel 1: Konvertieren eines JSON-Arrays in eine temporäre Tabelle

Beispiel 2: Zusammenführen von Eigenschaften aus zwei JSON-Objekten

Beispiel 3: Verknüpfen von Zeilen und JSON-Daten, die in Tabellenzellen gespeichert sind, mithilfe von CROSS APPLY

Beispiel 4: Kombinieren der relationalen Zeilen und JSON-Elemente mit CROSS APPLY

Beispiel 5: Importieren von JSON-Daten in SQL Server

Beispiel 6: Einfaches Beispiel mit JSON-Inhalten

Weitere Informationen

Feedback

Zusätzliche Ressourcen