Freigeben über

Erste Schritte mit JSON-Features

Gilt für:Azure SQL-DatenbankAzure SQL Managed InstanceSQL-Datenbank in Fabric

Azure SQL-Datenbank, SQL-Datenbank in Microsoft Fabric und azure SQL Managed Instance ermöglichen Ihnen das Analysieren und Abfragen von Daten, die im JSON-Format ( JavaScript Object Notation) dargestellt sind, und exportieren Ihre relationalen Daten als JSON-Text. Die folgenden JSON-Szenarios stehen zur Verfügung:

Formatieren relationaler Daten im JSON-Format

Für einen Webdienst, der Daten aus der Datenbankschicht entnimmt und eine Antwort im JSON-Format bereitstellt, oder clientseitige JavaScript-Frameworks oder Bibliotheken, die Daten im JSON-Format akzeptieren, können Sie den Datenbankinhalt direkt in einer SQL-Abfrage im JSON-Format formatieren. Sie müssen keinen Anwendungscode mehr schreiben, der Ergebnisse als JSON formatiert, oder eine JSON-Serialisierungsbibliothek zum Konvertieren von Tabellenabfrageergebnissen und anschließendes Serialisieren von Objekten in das JSON-Format. Stattdessen können Sie die FOR JSON Klausel verwenden, um SQL-Abfrageergebnisse als JSON zu formatieren und sie direkt in Ihrer Anwendung zu verwenden.

Im folgenden Beispiel werden Zeilen aus der Tabelle Sales.Customer mithilfe der FOR JSON-Klausel im JSON-Format formatiert:

select CustomerName, PhoneNumber, FaxNumber
from Sales.Customers
FOR JSON PATH

Die FOR JSON PATH Klausel formatiert die Ergebnisse der Abfrage als JSON-Text. Spaltennamen werden als Schlüssel verwendet, während die Zellenwerte als JSON-Werte generiert werden:

[
{"CustomerName":"Eric Torres","PhoneNumber":"(307) 555-0100","FaxNumber":"(307) 555-0101"},
{"CustomerName":"Cosmina Vlad","PhoneNumber":"(505) 555-0100","FaxNumber":"(505) 555-0101"},
{"CustomerName":"Bala Dixit","PhoneNumber":"(209) 555-0100","FaxNumber":"(209) 555-0101"}
]

Das Resultset wird als JSON-Array formatiert, in dem jede Zeile als separates JSON-Objekt formatiert ist.

PATH gibt an, dass Sie das Ausgabeformat Ihres JSON-Ergebnisses mithilfe der Punktnotation in Spaltenaliasen anpassen können. Die folgende Abfrage ändert den Namen des CustomerName Schlüssels im JSON-Ausgabeformat und fügt Telefon- und Faxnummern in das Contact Unterobjekt ein:

select CustomerName as Name, PhoneNumber as [Contact.Phone], FaxNumber as [Contact.Fax]
from Sales.Customers
where CustomerID = 931
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Die Ausgabe dieser Abfrage sieht folgendermaßen aus:

{
    "Name":"Nada Jovanovic",
    "Contact":{
           "Phone":"(215) 555-0100",
           "Fax":"(215) 555-0101"
    }
}

In diesem Beispiel wird durch Angabe der Option WITHOUT_ARRAY_WRAPPER ein einzelnes JSON-Objekt statt eines Arrays zurückgegeben. Sie können diese Option verwenden, wenn Sie wissen, dass Sie ein einzelnes Objekt als Ergebnis der Abfrage zurückgeben.

Der Hauptwert der FOR JSON Klausel besteht darin, dass Sie komplexe hierarchische Daten aus Ihrer Datenbank zurückgeben können, die als geschachtelte JSON-Objekte oder Arrays formatiert sind. Das folgende Beispiel zeigt, wie die Zeilen aus der Orders-Tabelle, die zu Customer gehören, als geschachteltes Array von Orders einbezogen werden können:

select CustomerName as Name, PhoneNumber as Phone, FaxNumber as Fax,
        Orders.OrderID, Orders.OrderDate, Orders.ExpectedDeliveryDate
from Sales.Customers Customer
    join Sales.Orders Orders
        on Customer.CustomerID = Orders.CustomerID
where Customer.CustomerID = 931
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER

Anstatt separate Abfragen zum Abrufen Customer von Daten zu senden und dann eine Liste verwandter OrdersDaten abzurufen, können Sie alle erforderlichen Daten mit einer einzelnen Abfrage abrufen, wie in der folgenden Beispielausgabe gezeigt:

{
  "Name":"Nada Jovanovic",
  "Phone":"(215) 555-0100",
  "Fax":"(215) 555-0101",
  "Orders":[
    {"OrderID":382,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
    {"OrderID":395,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
    {"OrderID":1657,"OrderDate":"2013-01-31","ExpectedDeliveryDate":"2013-02-01"}
  ]
}

Arbeiten mit JSON-Daten

Wenn Sie nicht über streng strukturierte Daten verfügen, komplexe Unterobjekte, Arrays oder hierarchische Daten haben, oder Ihre Datenstrukturen sich mit der Zeit weiterentwickeln, kann das JSON-Format Sie bei der Darstellung beliebiger komplexer Datenstrukturen unterstützen.

JSON ist ein Textformat, das wie jede andere Zeichenfolge verwendet werden kann. Sie können JSON-Daten als Standard-nvarchar senden oder speichern:

CREATE TABLE Products (
  Id int identity primary key,
  Title nvarchar(200),
  Data nvarchar(max)
)
go
CREATE PROCEDURE InsertProduct(@title nvarchar(200), @json nvarchar(max))
AS BEGIN
    insert into Products(Title, Data)
    values(@title, @json)
END

Die in diesem Beispiel verwendeten JSON-Daten werden mithilfe des nvarchar(MAX)- Typs dargestellt. JSON kann in diese Tabelle eingefügt oder mithilfe von Transact-SQL-Standardsyntax als Argument der gespeicherten Prozedur bereitgestellt werden, wie im folgenden Beispiel gezeigt:

EXEC InsertProduct 'Toy car', '{"Price":50,"Color":"White","tags":["toy","children","games"]}'

Jede clientseitige Sprache oder Bibliothek, die mit Zeichenfolgendaten funktioniert, ist auch mit JSON-Daten verwendbar. JSON kann in jeder Tabelle gespeichert werden, die den Datentyp nvarchar unterstützt, z. B. in einer speicheroptimierten Tabelle oder einer Tabelle mit Systemversionsverwaltung. JSON ist weder für den clientseitigen Code noch in der Datenbankschicht mit Einschränkungen verbunden.

Abfragen von JSON-Daten

Wenn Daten als JSON formatiert sind, die in Tabellen gespeichert sind, können Sie mit JSON-Funktionen diese Daten in einer beliebigen SQL-Abfrage verwenden.

Mit JSON-Funktionen können Sie Daten, die im JSON-Format vorliegen, wie jeden anderen SQL-Datentyp behandeln. Sie können problemlos Werte aus dem JSON-Text extrahieren und JSON-Daten in beliebigen Abfragen verwenden:

select Id, Title, JSON_VALUE(Data, '$.Color'), JSON_QUERY(Data, '$.tags')
from Products
where JSON_VALUE(Data, '$.Color') = 'White'

update Products
set Data = JSON_MODIFY(Data, '$.Price', 60)
where Id = 1

Die JSON_VALUE Funktion extrahiert einen Wert aus JSON-Text, der in der Datenspalte gespeichert ist. Diese Funktion verwendet einen JavaScript-ähnlichen Pfad, um auf einen zu extrahierenden Wert in JSON-Text zu verweisen. Der extrahierte Wert kann in einem beliebigen Teil der SQL-Abfrage verwendet werden.

Die JSON_QUERY Funktion ist ähnlich wie JSON_VALUE. Im Gegensatz dazu JSON_VALUEextrahiert diese Funktion komplexe Unterobjekte wie Arrays oder Objekte, die in JSON-Text platziert werden.

Mit der JSON_MODIFY Funktion können Sie den Pfad des Werts im JSON-Text angeben, der aktualisiert werden soll, sowie einen neuen Wert, der das alte überschreibt. Auf diese Weise können Sie JSON-Text problemlos ohne erneute Analyse der gesamten Struktur aktualisieren.

Da JSON in einem Standardtext gespeichert wird, gibt es keine Garantie, dass die in Textspalten gespeicherten Werte ordnungsgemäß formatiert sind. Sie können mithilfe von CHECK-Standardeinschränkungen und der ISJSON-Funktion überprüfen, ob in der JSON-Spalte gespeicherter Text ordnungsgemäß formatiert ist:

ALTER TABLE Products
    ADD CONSTRAINT [Data should be formatted as JSON]
        CHECK (ISJSON(Data) > 0)

Wenn der Eingabetext richtig formatiert ist JSON, gibt die ISJSON Funktion den Wert 1zurück. Bei jeder Einfügung oder Aktualisierung in der JSON-Spalte stellt diese Einschränkung sicher, dass der neue Textwert richtiges JSON-Format aufweist.

Transformieren von JSON in tabellarisches Format

Sie können JSON-Sammlungen ins Tabellenformat umwandeln und JSON-Daten laden oder abfragen.

OPENJSON ist eine T-SQL-Funktion mit Tabellenwert, die JSON-Text analysiert, ein Array von JSON-Objekten sucht, die Elemente des Arrays durchläuft und eine Zeile im Ausgabeergebnis für jedes Element des Arrays zurückgibt.

Screenshots und Codeausschnitte von JSON-Beispieltabellendaten.

Im Beispiel können wir angeben, wo das JSON-Array gefunden werden soll, das geöffnet werden soll (im $.Orders Pfad), welche Spalten als Ergebnis zurückgegeben werden sollen und wo die JSON-Werte gefunden werden, die als Zellen zurückgegeben werden sollen.

Wir können ein JSON-Array in der Variablen @orders in einen Satz von Zeilen transformieren, dieses Resultset analysieren oder Zeilen in eine Standardtabelle einfügen:

CREATE PROCEDURE InsertOrders(@orders nvarchar(max))
AS BEGIN

    insert into Orders(Number, Date, Customer, Quantity)
    select Number, Date, Customer, Quantity
    FROM OPENJSON (@orders)
     WITH (
            Number varchar(200),
            Date datetime,
            Customer varchar(200),
            Quantity int
     )
END

Die als JSON-Array formatierte und der gespeicherten Prozedur als Parameter bereitgestellte Auflistung der Aufträge kann analysiert und in die Tabelle „Orders“ eingefügt werden.

Zugehöriger Inhalt

  • Last updated on