Freigeben über

OPENJSON (Transact-SQL)

Gilt für: SQL Server 2016 (13.x) und höhere Versionen von Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics SQL Analytics-Endpunktin Microsoft FabricWarehouse in Microsoft FabricSQL-Datenbank in Microsoft Fabric SQL-Datenbank in Microsoft Fabric

Die OPENJSON Tabellenwertfunktion analysiert JSON-Text und gibt Objekte und Eigenschaften aus der JSON-Eingabe als Zeilen und Spalten zurück. Mit anderen Worten: OPENJSON Stellt eine Rowsetansicht über ein JSON-Dokument bereit. Sie können die Spalten im Rowset und die JSON-Eigenschaftspfade zum Auffüllen der Spalten angeben. Da OPENJSON eine Reihe von Zeilen zurückgegeben wird, können Sie in der OPENJSON Klausel einer Transact-SQL-Anweisung genauso wie FROM jede andere Tabellen-, Ansichts- oder Tabellenwertfunktion verwenden.

Wird OPENJSON verwendet, um JSON-Daten in SQL Server zu importieren oder JSON-Daten in relationales Format für eine App oder einen Dienst zu konvertieren, die JSON nicht direkt nutzen können.

Note

Die OPENJSON Funktion ist nur unter Kompatibilitätsebene 130 oder höher verfügbar. Wenn die Kompatibilitätsebene der Datenbank niedriger 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 OPENJSON tabellenwertige Funktion analysiert das als erstes Argument bereitgestellte jsonExpression und gibt eine oder mehrere Zeilen zurück, die Daten aus den JSON-Objekten im Ausdruck enthalten. jsonExpression kann geschachtelte Unterobjekte enthalten. Wenn Sie ein Unterobjekt aus jsonExpression analysieren möchten, können Sie einen Pfadparameter für das JSON-Unterobjekt angeben.

openjson

Diagramm der Syntax für OPENJSON TVF.

Standardmäßig gibt die OPENJSON Tabellenwertfunktion drei Spalten zurück, die den Schlüsselnamen, den Wert und den Typ der einzelnen key:value Paare enthalten, die in jsonExpression gefunden wurden. Alternativ können Sie das Schema des zurückgegebenen Resultsets OPENJSON explizit angeben, indem Sie with_clause angeben.

with_clause

Diagramm der Syntax für WITH-Klausel in OPENJSON TVF.

Die with_clause enthält eine Liste von Spalten mit ihren Typen, die OPENJSON zurückgegeben werden sollen. Stimmt standardmäßig OPENJSON mit den Schlüsseln in jsonExpression mit den Spaltennamen in with_clause überein (in diesem Fall bedeutet dies, dass die Groß-/Kleinschreibung beachtet wird). Wenn ein Spaltenname nicht mit einem Schlüsselnamen übereinstimmt, können Sie eine optionale column_path angeben, bei der es sich um einen JSON-Pfadausdruck handelt, der auf einen Schlüssel innerhalb des jsonExpression verweist.

Arguments

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. Im folgenden Beispiel wird jede Eigenschaft des Objekts zurückgegeben, das als jsonExpression bereitgestellt wird:

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
  • DoublePrecisionFloatingPoint_value entspricht IEEE-754.

path

Ist ein optionaler JSON-Pfadausdruck, der auf ein Objekt oder ein Array innerhalb von jsonExpression verweist. OPENJSON sucht in den JSON-Text an der angegebenen Position und analysiert nur das referenzierte Fragment. Weitere Informationen finden Sie unter JSON-Pfadausdrücke.

Sie können eine Variable als Pfadwert angeben. (Dies wird in SQL Server 2016 (13.x) und früheren Versionen nicht unterstützt.)

Im folgenden Beispiel wird ein geschachteltes Objekt zurückgegeben, indem der Pfad angegeben wird:

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

Wenn OPENJSON ein JSON-Array analysiert wird, 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

Results

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 explizit das Ausgabeschema für die OPENJSON zurückzugebende Funktion. Die optionalen with_clause können die folgenden Elemente enthalten:

colName

Der Name für die Ausgabespalte.

Verwendet standardmäßig den Namen der Spalte, OPENJSON um mit einer Eigenschaft im JSON-Text übereinzugleichen. Wenn Sie beispielsweise die Spalte name im Schema angeben, wird versucht, OPENJSON diese Spalte mit dem Eigenschaftsnamen im JSON-Text aufzufüllen. Sie können diese Standardzuordnung überschreiben, indem Sie das Argument column_path verwenden.

type

Der Datentyp für die Ausgabespalte.

Note

Wenn Sie auch die AS JSON Option verwenden, muss der Spaltendatentyp nvarchar(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 des Pfadparameters weiter oben in diesem Thema.

Verwenden Sie column_path , um Standardzuordnungsregeln außer Kraft zu setzen, 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.

AS JSON

Verwenden Sie die AS JSON Option in einer Spaltendefinition, um anzugeben, dass die referenzierte Eigenschaft ein inneres JSON-Objekt oder array enthält. Wenn Sie die AS JSON Option angeben, muss der Spaltentyp nvarchar(MAX) sein.

  • Wenn Sie keine Spalte angeben AS JSON , gibt die Funktion einen skalaren Wert (z. B. int, string, true, false) aus der angegebenen JSON-Eigenschaft im angegebenen Pfad zurück. Wenn der Pfad ein Objekt oder ein Array darstellt und die Eigenschaft nicht im angegebenen Pfad gefunden werden kann, gibt die Funktion NULL im lax Modus zurück oder gibt einen Fehler im strict Modus zurück. Dieses Verhalten ähnelt dem Verhalten der JSON_VALUE Funktion.

  • Wenn Sie eine Spalte angeben AS JSON , gibt die Funktion ein JSON-Fragment aus der angegebenen JSON-Eigenschaft im angegebenen Pfad zurück. Wenn der Pfad einen skalaren Wert darstellt und die Eigenschaft nicht im angegebenen Pfad gefunden werden kann, gibt die Funktion NULL im lax Modus zurück oder gibt einen Fehler im strict Modus zurück. Dieses Verhalten ähnelt dem Verhalten der JSON_QUERY Funktion.

Note

Wenn Sie ein geschachteltes JSON-Fragment aus einer JSON-Eigenschaft zurückgeben möchten, müssen Sie das AS JSON Flag angeben. Ohne diese Option gibt die Eigenschaft, wenn die Eigenschaft nicht gefunden werden kann, OPENJSON einen NULL Wert anstelle des referenzierten JSON-Objekts oder Arrays zurück, oder es wird ein Laufzeitfehler im strict Modus zurückgegeben.

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  
 )

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"}

Rückgabewert

Die von der OPENJSON Funktion zurückgegebenen Spalten hängen von der WITH Option ab.

  • Wenn Sie das Standardschema aufrufen OPENJSON – d. h., wenn Sie kein explizites Schema in der WITH Klausel angeben – gibt die Funktion eine Tabelle mit den folgenden Spalten zurück:

    • Key. Ein nvarchar(4000) -Wert, der den Namen der angegebenen Eigenschaft oder den Index des Elements im angegebenen Array enthält. Die key Spalte weist eine BIN2-Sortierung auf.

    • Value. Ein nvarchar(MAX) -Wert, der den Wert der Eigenschaft enthält. Die value Spalte erbt die Sortierung von jsonExpression.

    • Type. Ein Int-Wert , der den Typ des Werts enthält. Die Type Spalte wird nur zurückgegeben, wenn Sie das Standardschema verwenden OPENJSON . Die type Spalte weist einen der folgenden Werte auf:

      Wert der Typspalte JSON-Datentyp
      0 null
      1 string
      2 number
      3 true/false
      4 array
      5 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 ein explizites Schema in der OPENJSON Klausel aufrufen WITH und angeben, gibt die Funktion eine Tabelle mit dem Schema zurück, das Sie in der WITH Klausel definiert haben.

Note

Die KeySpalten und Value Die Spalten Typewerden nur zurückgegeben, wenn Sie das Standardschema verwenden OPENJSON und nicht mit einem expliziten Schema verfügbar sind.

Remarks

json_path, die im zweiten Argument von OPENJSON oder in with_clause verwendet werden, kann mit dem oder lax dem strict Schlüsselwort beginnen.

  • Im lax Modus wird kein Fehler ausgelöst, OPENJSON wenn das Objekt oder der Wert im angegebenen Pfad nicht gefunden werden kann. Wenn der Pfad nicht gefunden werden kann, OPENJSON wird entweder ein leeres Resultset oder ein NULL Wert zurückgegeben.
  • Im strictModus OPENJSON wird ein Fehler zurückgegeben, wenn der Pfad nicht gefunden werden kann.

Einige beispiele auf dieser Seite geben explizit den Pfadmodus lax oder strict. Der Path-Modus ist optional. Wenn Sie keinen Pfadmodus explizit angeben, lax ist der Modus die Standardeinstellung. Weitere Informationen zu Pfadmodus- und Pfadausdrücken finden Sie unter JSON-Pfadausdrücke.

Spaltennamen in with_clause werden mit Schlüsseln im JSON-Text abgeglichen. 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 können Schlüssel mit alphanumerischen Zeichen enthalten. Escapen Sie den Schlüsselnamen in json_path mit doppelten Anführungszeichen, wenn Sie Sonderzeichen in den Tasten haben. Entspricht z $."my key $1".regularKey."key with . dot" . B. dem Wert 1 im folgenden JSON-Text:

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

Examples

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 weisen eine doppelte Namenseigenschaft auf. 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 Quality und Manufacturer. 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 der Verkaufsgründe, als ob die Gründe in einer separaten untergeordneten Tabelle gespeichert wurden. Anschließend verknüpft der CROSS APPLY Operator jede Verkaufsauftragszeile mit den Zeilen, die von der OPENJSON Tabellenwertfunktion zurückgegeben werden.

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

Tip

Wenn Sie JSON-Arrays erweitern müssen, die in einzelnen Feldern gespeichert sind und sie mit ihren übergeordneten Zeilen verknüpfen müssen, verwenden Sie in der Regel den Operator Transact-SQL CROSS APPLY . Weitere Informationen finden CROSS APPLYSie unter FROM-Klausel.

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

Results

title street postcode lon lat
Ganze Lebensmittelmärkte 17991 Redmond Way WA 98052 47.666124 -122.10155
Sears 148. 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

Verwandte Inhalte

  • Last updated on