Analysieren und Transformieren von JSON-Daten mit OPENJSON

  • Artikel

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics

Die OPENJSON Rowsetfunktion konvertiert JSON-Text in eine Reihe von Zeilen und Spalten. Nach der Transformation einer JSON-Sammlung in ein Rowset mithilfe von OPENJSON können Sie jede beliebige SQL-Abfrage auf zurückgegebene Daten ausführen oder diese in eine Tabelle von SQL Server einfügen. Weitere Informationen zum Arbeiten mit JSON-Daten in der SQL Server-Datenbank-Engine finden Sie unter JSON-Daten in SQL Server.

Die OPENJSON Funktion greift ein einzelnes JSON-Objekt oder eine Sammlung von JSON-Objekten auf und transformiert sie in eine oder mehrere Zeilen. Die OPENJSON Funktion gibt standardmäßig die folgenden Daten zurück:

  • Aus einem JSON-Objekt gibt die Funktion alle Schlüssel-Wert-Paare zurück, die auf der ersten Ebene gefunden werden.
  • Aus einem JSON-Array gibt die Funktion alle Elemente des Arrays mit ihren Indizes zurück.

Sie können eine optionale WITH Klausel hinzufügen, um ein Schema bereitzustellen, das die Struktur der Ausgabe definiert.

OPENJSON mit der Standardausgabe

Bei Verwendung der OPENJSON Funktion ohne ein explizites Schema für die Ergebnisse, d.h. ohne eine WITH Klausel nach OPENJSON, gibt die Funktion eine Tabelle mit den folgenden drei Spalten zurück:

  1. Der name der Eigenschaft im Eingabeobjekt (oder Index des Elements im Eingabe-Array)
  2. Der value der Eigenschaft oder des Arrayelements
  3. Der type (z.B. Zeichenfolge, Zahl, boolescher Wert, Array oder Objekt)

OPENJSON gibt jede Eigenschaft des JSON-Objekts oder jedes Element des Arrays als separate Zeile zurück

Das folgende Beispiel verwendet OPENJSON mit dem Standardschema – also ohne die optionale Klausel – und gibt eine Zeile für jede Eigenschaft des JSON-Objekts zurück.

DECLARE @json NVARCHAR(MAX);

SET @json='{ "name": "John", "surname": "Doe", "age": 45, "skills": [ "SQL", "C#", "MVC" ]}';

SELECT *
FROM OPENJSON(@json);

Hier sehen Sie das Ergebnis.

Schlüssel value Typ
name John 1
surname Doe 1
age 45 2
skills [ "SQL" ,"C#" ,"MVC" ] 4

Weitere Informationen und Beispiele finden Sie unter Use OPENJSON with the Default Schema

Syntax und Verwendung finden Sie unter OPENJSON.

OPENJSON-Ausgabe mit einer expliziten Struktur

Wenn Sie mithilfe der WITH Klausel der OPENJSON Funktion ein Schema für die Ergebnisse angeben, gibt die Funktion eine Tabelle zurück, die nur die von Ihnen in der WITH Klausel definierten Spalten enthält. In der optionalen WITH Klausel können Sie einen Satz von Ausgabespalten, deren Typen und die Pfade der JSON-Quelleigenschaften für jeden Ausgabewert angeben. OPENJSON durchläuft das Array von JSON-Objekten, liest Werte auf dem angegebenen Pfad für jede Spalte und konvertiert sie in einen angegebenen Typ.

Im folgenden Beispiel wird OPENJSON mit einem Schema für die Ausgabe verwendet, das Sie explizit in der WITH Klausel angeben.

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
    {
        "Order": {
            "Number": "SO43659",
            "Date": "2024-05-31T00:00:00"
        },
        "AccountNumber": "AW29825",
        "Item": {
            "Price": 2024.9940,
            "Quantity": 1
        }
    },
    {
        "Order": {
            "Number": "SO43661",
            "Date": "2024-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'
);

Hier sehen Sie das Ergebnis.

Zahl Date Kunde Menge
SO43659 2024-05-31T00:00:00 AW29825 1
SO43661 2024-06-01T00:00:00 AW73565 3

Diese Funktion gibt die Elemente eines JSON-Arrays zurück und formatiert diese.

  • Für jedes Element im JSON-Array generiert OPENJSON eine neue Zeile in der Ausgabetabelle. Die zwei Elemente im JSON-Array werden in zwei Zeilen in der zurückgegebenen Tabelle konvertiert.

  • Für jede Spalte, die mithilfe der Syntax colName type json_path angegeben wurde, konvertiert OPENJSON den Wert, der in jedem Arrayelement im angegebenen Pfad gefunden wurde, in den angegebenen Typ. In diesem Beispiel werden Werte aus der Date-Spalte aus jedem Element im Pfad $.Order.Date genommen und in datetime-Werte konvertiert.

Weitere Informationen und Beispiele finden Sie unter Use OPENJSON with an Explicit Schema (SQL Server).

Syntax und Verwendung finden Sie unter OPENJSON.

OPENJSON erfordert Kompatibilitätsgrad 130

Die OPENJSON Funktion steht nur für den Kompatibilitätsgrad 130 oder höher zur Verfügung. Wenn der Datenbank-Kompatibilitätsgrad kleiner als 130 ist, kann SQL Server die OPENJSON Funktion nicht finden und ausführen. Andere integrierte JSON-Funktionen sind für alle Kompatibilitätsgrade verfügbar.

Sie können die Kompatibilitätsebene in der sys.databases Ansicht oder in Datenbankeigenschaften überprüfen und die Kompatibilitätsebene einer Datenbank mit dem folgenden Befehls ändern:

ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130;

Zugehöriger Inhalt