Formatieren von Abfrageergebnissen als JSON mit FOR JSON

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-Datenbank Azure SQL verwaltete Instanz 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 an SQL Server delegieren.

Hinweis

Azure Data Studio ist der empfohlene Abfrage-Editor für JSON-Abfragen, da sie die JSON-Ergebnisse automatisch formatiert, wie in diesem Artikel gezeigt. SQL Server Management Studio zeigt eine unformatierte Zeichenfolge 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 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.

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

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

FÜR JSON-PFAD

Steuern der Ausgabe mit FOR JSON PATH

Im PATH Modus können Sie die Punktsyntax ( z. B. ) verwenden, Item.Price um geschachtelte Ausgabe zu formatieren.

Hier ist eine Beispielabfrage, die den Modus mit der FOR JSON Klausel verwendetPATH. Im folgenden Beispiel wird auch die ROOT Option zum Angeben eines benannten Stammelements verwendet.

Weitere Informationen zu FOR JSON PATH

Ausführlichere Informationen und Beispiele finden Sie unter "Format Nested JSON Output with PATH Mode (SQL Server)".

Informationen zur Syntax und Verwendung finden Sie unter FOR-Klausel (Transact-SQL).For syntax and usage, see FOR Clause (Transact-SQL).

FÜR JSON AUTO

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 NULL sind Werte nicht in der Ausgabe enthalten. Sie können INCLUDE_NULL_VALUES verwenden, um dieses Verhalten zu ändern.

Hier ist eine Beispielabfrage, die den Modus mit der FOR JSON Klausel verwendetAUTO.

SELECT name, surname
FROM emp
FOR JSON AUTO;

Und hier ist der zurückgegebene JSON-Code.

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

Beispiel mit JOIN und NULL

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

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 "Automatisches Formatieren von JSON-Ausgabe mit AUTO-Modus (SQL Server)".

Informationen zur Syntax und Verwendung finden Sie unter FOR-Klausel (Transact-SQL).For syntax and usage, see FOR Clause (Transact-SQL).

Steuern der Ausgabe anderer JSON-Ausgabeoptionen

Steuern Sie die Ausgabe der FOR JSON Klausel mithilfe der folgenden zusätzlichen Optionen.

• ROOT

  Wenn Sie der JSON-Ausgabe ein einzelnes Element auf oberster Ebene hinzufügen möchten, geben Sie die ROOT Option an. 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

  Um Nullwerte in die JSON-Ausgabe einzuschließen, geben Sie die INCLUDE_NULL_VALUES Option an. Wenn Sie diese Option nicht angeben, enthält die Ausgabe keine JSON-Eigenschaften für NULL Werte in den Abfrageergebnissen. Weitere Informationen finden Sie unter Include Null Values in JSON – INCLUDE_NULL_VALUES Option.

• WITHOUT_ARRAY_WRAPPER

  Wenn Sie die eckigen Klammern entfernen möchten, die die JSON-Ausgabe der FOR JSON Klausel standardmäßig umgeben, geben Sie die WITHOUT_ARRAY_WRAPPER Option an. 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 , d. h. die Ausgabe wird in eckige Klammern eingeschlossen. Weitere Informationen finden Sie unter Entfernen eckiger Klammern aus JSON – WITHOUT_ARRAY_WRAPPER Option.

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.

     • Andere Clientanwendungen erfordern möglicherweise Code, um langwierige Ergebnisse in eine einzelne gültige JSON-Zeichenfolge zu integrieren, indem der Inhalt mehrerer Zeilen verkettet wird. 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"
}]

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