Formatieren von Abfrageergebnisse als JSON mit FOR JSON (SQL Server)

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

Formatieren Sie Abfrageergebnisse als JSON, oder exportieren Sie Daten aus SQL Server als JSON, indem Sie einer SELECT-Anweisung die FOR JSON-Klausel hinzufügen. Verwenden Sie die FOR JSON-Klausel, um Clientanwendungen zu vereinfachen, indem Sie die Formatierung der JSON-Ausgabe von der App zu SQL Server delegieren.

Hinweis

Azure Data Studio ist der empfohlene Abfrage-Editor für JSON-Abfragen, da hierbei die JSON-Ergebnisse (wie in diesem Artikel gezeigt) automatisch formatiert werden, anstatt dass eine flache Zeichenfolge angezeigt wird.

Wenn Sie die FOR JSON-Klausel verwenden, können Sie die Struktur der JSON-Ausgabe explizit angeben oder die Struktur der SELECT-Anweisung die Ausgabe bestimmen lassen.

• Verwenden Sie FOR JSON PATH, um die vollständige Kontrolle über das Format der JSON-Ausgabe zu behalten. Sie können Wrapper-Objekte erstellen und komplexe Eigenschaften schachteln.

• Verwenden Sie FOR JSON AUTO, um die JSON-Ausgabe basierend auf der Struktur der SELECT-Anweisung automatisch zu formatieren.

Hier finden Sie ein Beispiel für eine SELECT-Anweisung mit der FOR JSON-Klausel und deren Ausgabe.

Option 1: Sie können die Ausgabe mit FOR JSON PATH steuern

Im PATH -Modus können Sie die Punktsyntax verwenden, z.B. 'Item.Price', um geschachtelte Ausgaben zu formatieren.

Hier ist eine Beispielabfrage, die den PATH-Modus mit der FOR JSON-Klausel verwendet. Im folgenden Beispiel wird auch die Option ROOT verwendet, um ein benanntes Stammelement anzugeben.

Weitere Informationen zu FOR JSON PATH

Ausführlichere Informationen und Beispiele finden Sie unter Formatieren der geschachtelten JSON-Ausgabe im PATH-Modus (SQL Server).

Informationen zu Syntax und Verwendung finden Sie unter FOR-Klausel (Transact-SQL).

Option 2: Die SELECT-Anweisung steuert die Ausgabe mit FOR JSON AUTO

Im AUTO -Modus bestimmt die Struktur der SELECT-Anweisung das Format der JSON-Ausgabe.

Standardmäßig sind NULL-Werte nicht in der Ausgabe enthalten. Sie können INCLUDE_NULL_VALUES verwenden, um dieses Verhalten zu ändern.

Hier ist eine Beispielabfrage, die den AUTO-Modus mit der FOR JSON-Klausel verwendet.

SELECT name, surname
FROM emp
FOR JSON AUTO;

Hier sehen Sie den zurückgegebenen JSON-Code.

[{
    "name": "John"
}, {
    "name": "Jane",
    "surname": "Doe"
}]

2.b: Beispiel mit JOIN und NULL

Das folgende Beispiel von SELECT...FOR JSON AUTO enthält eine Anzeige, wie die JSON-Ergebnisse aussehen, wenn eine 1:viele-Beziehung zwischen Daten aus verknüpften Tabellen besteht.

Das Fehlen des NULL-Werts aus dem zurückgegebenen JSON-Code wird ebenfalls veranschaulicht. Sie können dieses Standardverhalten jedoch mithilfe des Schlüsselworts INCLUDE_NULL_VALUES in der FOR-Klausel außer Kraft setzen.

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
GO

CREATE TABLE #tabClass (
    ClassGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(),
    ClassName NVARCHAR(32) NOT NULL
);

CREATE TABLE #tabStudent (
    StudentGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(),
    StudentName NVARCHAR(32) NOT NULL,
    ClassGuid UNIQUEIDENTIFIER NULL -- Foreign key.
);
GO

INSERT INTO #tabClass (ClassGuid, ClassName)
VALUES
    ('DE807673-ECFC-4850-930D-A86F921DE438', 'Algebra Math'),
    ('C55C6819-E744-4797-AC56-FF8A729A7F5C', 'Calculus Math'),
    ('98509D36-A2C8-4A65-A310-E744F5621C83', 'Art Painting');

INSERT INTO #tabStudent (StudentName, ClassGuid)
VALUES
    ('Alice Apple', 'DE807673-ECFC-4850-930D-A86F921DE438'),
    ('Alice Apple', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    ('Betty Boot', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    ('Betty Boot', '98509D36-A2C8-4A65-A310-E744F5621C83'),
    ('Carla Cap', null);
GO

SELECT c.ClassName,
    s.StudentName
FROM #tabClass AS c
RIGHT JOIN #tabStudent AS s ON s.ClassGuid = c.ClassGuid
ORDER BY c.ClassName,
    s.StudentName
FOR JSON AUTO
    -- To include NULL values in the output, uncomment the following line:
    --, INCLUDE_NULL_VALUES
    ;
GO

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
GO

Als Nächstes folgt der JSON-Code, der von der vorherigen SELECT-Anweisung ausgegeben wird.

JSON_F52E2B61-18A1-11d1-B105-00805F49916B

[
   {"s":[{"StudentName":"Carla Cap"}]},
   {"ClassName":"Algebra Math","s":[{"StudentName":"Alice Apple"}]},
   {"ClassName":"Art Painting","s":[{"StudentName":"Betty Boot"}]},
   {"ClassName":"Calculus Math","s":[{"StudentName":"Alice Apple"},{"StudentName":"Betty Boot"}]}
]

Weitere Informationen zu FOR JSON AUTO

Ausführlichere Informationen und Beispiele finden Sie unter Format JSON Output Automatically with AUTO Mode (SQL Server).

Informationen zu Syntax und Verwendung finden Sie unter FOR-Klausel (Transact-SQL).

Steuern der Ausgabe anderer JSON-Ausgabeoptionen

Steuern Sie die Ausgabe der FOR JSON-Klausel, indem Sie die folgenden zusätzlichen Optionen verwenden.

• ROOT Geben Sie die Option ROOT an, um der JSON-Ausgabe ein einzelnes Element der obersten Ebene hinzuzufügen. Wenn Sie diese Option nicht angeben, besitzt die JSON-Ausgabe kein Stammelement. Weitere Informationen finden Sie unter Hinzufügen eines Stammknotens zur JSON-Ausgabe mit der ROOT-Option (SQL Server).

• INCLUDE_NULL_VALUES Geben Sie die Option INCLUDE_NULL_VALUES an, um NULL-Werte in die JSON-Ausgabe einzuschließen. Wenn Sie diese Option nicht angeben, enthält die Ausgabe in den Abfrageergebnissen keine JSON-Eigenschaften für NULL-Werte. Weitere Informationen finden Sie unter Einschließen von NULL-Werten in der JSON-Ausgabe mit der INCLUDE_NULL_VALUES-Option (SQL Server).

• WITHOUT_ARRAY_WRAPPER Geben Sie die Option WITHOUT_ARRAY_WRAPPER an, um die rechteckigen Klammern zu entfernen, die die JSON-Ausgabe der FOR JSON-Klausel umgeben. Verwenden Sie diese Option, um ein einzelnes JSON-Objekt als Ausgabe eines einzeiligen Ergebnisses zu generieren. Wenn Sie diese Option nicht angeben, wird die JSON-Ausgabe als Array formatiert, das in eckige Klammern eingeschlossen ist. Weitere Informationen finden Sie unter Entfernen von rechteckigen Klammern von JSON-Ausgabe mit der Option WITHOUT_ARRAY_WRAPPER (SQL Server).

Ausgabe der FOR JSON-Klausel

Die Ausgabe der FOR JSON-Klausel besitzt die folgenden Eigenschaften:

1. Das Resultset enthält eine einzelne Spalte.

   • Ein kleines Resultset kann eine einzelne Zeile enthalten.
   • Ein großes Resultset teilt den langen JSON-String über mehrere Zeilen auf.

     • Standardmäßig verkettet SQL Server Management Studio (SSMS) die Ergebnisse in einer einzelnen Zeile, wenn die Ausgabeeinstellung Results to Grid (Ergebnisse im Raster) ist. Die SSMS-Statusleiste zeigt die tatsächliche Zeilenanzahl an.

     • Möglicherweise erfordern andere Clientanwendungen Code, um längere Ergebnislisten neu zu einer einzelnen gültigen JSON-Zeichenfolge zu kombinieren, indem die Inhalte mehrerer Zeilen verkettet werden. Ein Beispiel für diesen Code in einer C#-Anwendung finden Sie unter Use FOR JSON output in a C# client app (Verwenden der FOR JSON-Ausgabe in einer C#-Client-App).

       

2. Die Ergebnisse werden wie ein JSON-Objekt-Array formatiert.

   • Die Anzahl der Elemente im JSON-Array entspricht der Anzahl der Zeilen in den Ergebnissen der SELECT-Anweisung (bevor die FOR JSON-Klausel angewendet wird).

   • Jede Zeile in den Ergebnissen der SELECT-Anweisung (bevor die FOR JSON-Klausel angewendet wird) wird ein separates JSON-Objekt im Array.

   • Jede Spalte in den Ergebnissen der SELECT-Anweisung (bevor die FOR JSON-Klausel angewendet wird) wird zu einer Eigenschaft des JSON-Objekts.

3. Sowohl die Namen der Spalten als auch deren Werte werden entsprechend der JSON-Syntax geschützt. Weitere Informationen finden Sie unter How FOR JSON escapes special characters and control characters (SQL Server).

Beispiel

In diesem Beispiel wird veranschaulicht, wie die FOR JSON-Klausel die JSON-Ausgabe formatiert.

Abfrageergebnisse

A	B	C	D
10	11	12	X
20	21	22	J
30	31	32	Z

JSON-Ausgabe

[{
    "A": 10,
    "B": 11,
    "C": 12,
    "D": "X"
}, {
    "A": 20,
    "B": 21,
    "C": 22,
    "D": "Y"
}, {
    "A": 30,
    "B": 31,
    "C": 32,
    "D": "Z"
}]

Weitere Informationen

• So konvertiert FOR JSON SQL Server-Datentypen in JSON-Datentypen (SQL Server)
• Schutz von Sonderzeichen und Steuerzeichen durch FOR JSON (SQL Server)
• JSON as a bridge between NoSQL and relational worlds

Nächste Schritte

• FOR-Klausel (Transact-SQL)
• Verwenden der FOR JSON-Ausgabe in SQL Server und in Client-Apps (SQL Server)