Formatieren von Abfrageergebnissen als JSON mit FOR JSON

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

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. SQL Server Management Studio zeigt eine unformatierte Zeichenkette an.

Formatieren von Abfrageergebnissen

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.

FOR JSON PATH

Steuern der Ausgabe mit FOR JSON PATH

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

Weitere detaillierte Informationen und Beispiele finden Sie unter Formatieren einer geschachtelten JSON-Ausgabe im PATH-Modus (SQL Server).

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

FOR JSON AUTO

Die SELECT-Anweisung steuert die Ausgabe mit FOR JSON AUTO

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

NULL-Werte sind standardmäßig 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"
}]

Beispiel mit JOIN und NULL

Im folgenden Beispiel von SELECT...FOR JSON AUTO wird gezeigt, wie die JSON-Ergebnisse im Falle einer 1:viele-Beziehung zwischen Daten aus verknüpften Tabellen aussehen.

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

Weitere detaillierte Informationen und Beispiele finden Sie unter Automatisches Formatieren von JSON-Ausgaben (SQL Server).

Weitere 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 Zusatzoptionen 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 mithilfe 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 die JSON-Ausgabe mit der Option INCLUDE_NULL_VALUES.

• 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, dessen Ausgabe in eckige Klammern eingeschlossen ist. Weitere Informationen finden Sie unter Entfernen von rechteckigen Klammern von JSON – Option WITHOUT_ARRAY_WRAPPER.

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); (So maskiert FOR JSON Sonderzeichen und Steuerzeichen (SQL Server)).

Beispiel

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

Abfrageergebnisse

H	B	C	D
10	11	12	X
20	21	22	Y
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"
}]

Zugehöriger Inhalt

• 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
• SELECT – FOR Clause (Transact-SQL)
• Verwenden der FOR JSON-Ausgabe in SQL Server und in Client-Apps (SQL Server)