JSON als elegante Formatierung von Abfrage-Ergebnissen zum schnellen Datenaustausch

Zumindest in meinem Projektumfeld kommt es immer wieder vor, dass mal eben schnell ein paar Daten aus einer Datenbank ermittelt und per Mail an andere Personen zu übertragen sind. Wenn es sich um komplexere Datenstrukturen handelt, dann stellt sich immer die Frage: In welchem Format sollen die Daten bereitgestellt werden. Statt der klassischen Wege der CSV-Datei oder Excel-Tabelle möchte ich in diesem Artikel JSON als Alternative vorstellen.

Vorab: Meine Beispieldatenbank

Ich verwende hier in den Beispielen den Microsoft SQL Server, die gezeigte Funktionalität ist ab der Version 2016 enthalten.

Als Beispieldatenbank verwende ich die Datenbank StackOverflow in der kleinen Version von 2010. Hier finden Sie nähere Infos dazu:

https://www.brentozar.com/archive/2017/07/love-stack-overflow-database/

https://www.brentozar.com/archive/2018/01/updated-and-smaller-stack-overflow-demo-databases/

Am Anfang ist alles einfach

Meist beginnt es ganz harmlos, beispielsweise mit der Anfrage: „Ich brauche mal schnell die UserIDs der User mit einer Reputation von über 500.000."

Das ist schnell getippt:

SELECT Id
FROM dbo.Users
WHERE Reputation > 500000;

SQL tippen und ausführen, Ergebnis markieren und kopieren, Mail schreiben und Zwischenablage einfügen. Fertig.

Kaum ist die Mail abgeschickt kommt schon eine Nachfrage: Ob es auch möglich wäre, neben der UserID noch den Namen zu liefern? Klar, SQL erweitern und ausführen, Ergebnis markieren und kopieren, Mail schreiben und Zwischenablage einfügen. Gerade bei der Kombination aus einer Zahl und einer Zeichenkette ist das Ergebnis auch noch ohne besondere Formatierungen gut zu lesen.

Aber dann wird es doch immer umfangreicher

Spätestens wenn es mehrere Spalten mit gleichen Datentypen sind, stellt sich die Frage: In welchem Format sollen die Daten bereitgestellt werden? Als eigene Datei im Anhang (Excel? CSV?)? Oder doch einfach als Text in der Mail? Die klassische Text-Tabelle mit auffüllenden Leerzeichen funktioniert nur bei Schriftarten mit gleicher Breite (z. B. Courier New), die inzwischen nur noch selten verwendet werden. Bei HTML-Mails ist noch die Formatierung als HTML-Tabelle möglich, was aber dann vielleicht nicht jeder Empfänger und nicht jedes Programm mag.

Und hier kommt jetzt JSON ins Spiel. Ein Format, das sowohl für Mensch als auch Maschine gut lesbar ist. Zudem gibt es für viele Editoren passende Plugins, die die Formatierung von JSON-Daten für uns Menschen optimieren. Ich verwende dazu für Notepad++ das Plugin JSTool.

FOR JSON PATH

Die SQL-Abfrage muss dazu nur um "FOR JSON PATH" ergänzt werden, schon werden die Daten im JSON-Format zurückgegeben:

SELECT Id, DisplayName, Reputation
FROM dbo.Users
WHERE Reputation > 500000
ORDER BY Reputation DESC
FOR JSON PATH;

Egal wie viele Datensätze die Abfrage liefert, es wird jetzt nur eine Zeile und eine Spalte zurück geliefert, die das komplette JSON-Dokument enthält:

Die Daten werden in einer Zeile ohne jegliche Formatierungen ausgegeben:

[{"Id":22656,"DisplayName":"Jon Skeet","Reputation":990402},{"Id":29407,"DisplayName":"Darin Dimitrov","Reputation":768383},{"Id":157882,"DisplayName":"BalusC","Reputation":766766},{"Id":6309,"DisplayName":"VonC","Reputation":730237},{"Id":17034,"DisplayName":"Hans Passant","Reputation":728015},{"Id":23354,"DisplayName":"Marc Gravell","Reputation":702715},{"Id":115145,"DisplayName":"CommonsWare","Reputation":688162},{"Id":34397,"DisplayName":"SLaks","Reputation":613673},{"Id":100297,"DisplayName":"Martijn Pieters","Reputation":604098},{"Id":893,"DisplayName":"Greg Hewgill","Reputation":590161},{"Id":157247,"DisplayName":"T.J. Crowder","Reputation":583878},{"Id":19068,"DisplayName":"Quentin","Reputation":568225},{"Id":14860,"DisplayName":"paxdiablo","Reputation":561959},{"Id":95810,"DisplayName":"Alex Martelli","Reputation":550605},{"Id":335858,"DisplayName":"dasblinkenlight","Reputation":545910},{"Id":5445,"DisplayName":"CMS","Reputation":523846},{"Id":61974,"DisplayName":"Mark Byers","Reputation":518142},{"Id":13302,"DisplayName":"marc_s","Reputation":515855},{"Id":20862,"DisplayName":"Ignacio Vazquez-Abrams","Reputation":513959},{"Id":23283,"DisplayName":"JaredPar","Reputation":513399},{"Id":15168,"DisplayName":"Jonathan Leffler","Reputation":504683},{"Id":69083,"DisplayName":"Guffa","Reputation":504630}]

Nach der Formatierung von Notepad++ in Verbindung mit dem Plugin JSTool sind die Daten übersichtlich angeordnet:

[{
"Id": 22656,
"DisplayName": "Jon Skeet",
"Reputation": 990402
}, {
"Id": 29407,
"DisplayName": "Darin Dimitrov",
"Reputation": 768383
}, {
"Id": 157882,
"DisplayName": "BalusC",
"Reputation": 766766
}, {
"Id": 6309,
"DisplayName": "VonC",
"Reputation": 730237
}, {
"Id": 17034,
"DisplayName": "Hans Passant",
"Reputation": 728015
}, {
"Id": 23354,
"DisplayName": "Marc Gravell",
"Reputation": 702715
}, {
"Id": 115145,
"DisplayName": "CommonsWare",
"Reputation": 688162
}, {
"Id": 34397,
"DisplayName": "SLaks",
"Reputation": 613673
}, {
"Id": 100297,
"DisplayName": "Martijn Pieters",
"Reputation": 604098
}, {
"Id": 893,
"DisplayName": "Greg Hewgill",
"Reputation": 590161
}, {
"Id": 157247,
"DisplayName": "T.J. Crowder",
"Reputation": 583878
}, {
"Id": 19068,
"DisplayName": "Quentin",
"Reputation": 568225
}, {
"Id": 14860,
"DisplayName": "paxdiablo",
"Reputation": 561959
}, {
"Id": 95810,
"DisplayName": "Alex Martelli",
"Reputation": 550605
}, {
"Id": 335858,
"DisplayName": "dasblinkenlight",
"Reputation": 545910
}, {
"Id": 5445,
"DisplayName": "CMS",
"Reputation": 523846
}, {
"Id": 61974,
"DisplayName": "Mark Byers",
"Reputation": 518142
}, {
"Id": 13302,
"DisplayName": "marc_s",
"Reputation": 515855
}, {
"Id": 20862,
"DisplayName": "Ignacio Vazquez-Abrams",
"Reputation": 513959
}, {
"Id": 23283,
"DisplayName": "JaredPar",
"Reputation": 513399
}, {
"Id": 15168,
"DisplayName": "Jonathan Leffler",
"Reputation": 504683
}, {
"Id": 69083,
"DisplayName": "Guffa",
"Reputation": 504630
}
]

JSON kann auch geschachtelt werden

Auch wenn die Abfrage komplexer wird, wenn beispielsweise eine 1:n-Relation abgefragt wird, kann JSON die gewünschten Daten übersichtlich darstellen. Wenn also in unserem Beispiel zu jedem User noch seine Fragen mit angezeigt werden sollen.

Allerdings ist hierbei zu beachten, dass statt des klassischen JOINS zwischen Users und Posts die Unterabfrage auf Posts in die SELECT-Klausel gehört. So wird zu jedem User eine Spalte bzw. ein Attribut ermittelt, das die Fragen des Users bereits JSON-formatiert enthält.

Laut Dokumentation wird die Ausgabe eines großen JSON-Strings auf mehrere Zeilen aufgeteilt, bei meinen Versuchen wurde das Ergebnis jedoch nach ca. 2000 Zeichen abgeschnitten. Abhilfe schafft nach meinen Beobachtungen die Kapselung des SELECTs in einem weiteren SELECT.

Eine komplexe Abfrage mit "FOR JSON PATH"-Zusatz:

SELECT (SELECT Users.Id, Users.DisplayName, Users.Reputation
,    (SELECT Posts.Id, Posts.CreationDate, Posts.AnswerCount
   FROM dbo.Posts
   WHERE Posts.OwnerUserId = Users.Id
    AND Posts.AnswerCount > 0
   ORDER BY Posts.CreationDate
   FOR JSON PATH
    ) "Posts"
FROM dbo.Users
WHERE Users.Reputation > 500000
ORDER BY Users.Reputation DESC
FOR JSON PATH
   ) AS Ausgabe;

Auszug aus dem Ergebnis, bereits mit Notepad++ und JSTool formatiert:

[{

   "Id": 22656,
   "DisplayName": "Jon Skeet",
   "Reputation": 990402,
   "Posts": [{
"Id": 194484,
"CreationDate": "2008-10-11T19:30:45.407",
"AnswerCount": 37
}, {
"Id": 215548,
    "CreationDate": "2008-10-18T20:47:40.003",
"AnswerCount": 42
}, <...> {
"Id": 3438806,
    "CreationDate": "2010-08-09T09:40:42.517",
"AnswerCount": 10
}, {
"Id": 3657778,
    "CreationDate": "2010-09-07T10:27:17.253",
"AnswerCount": 9
}
]
}, {
    "Id": 29407,
    "DisplayName": "Darin Dimitrov",
    "Reputation": 768383,
    "Posts": [{
    "Id": 269988,
"CreationDate": "2008-11-06T19:40:47.670",
"AnswerCount": 6
}, {
"Id": 311696,
"CreationDate": "2008-11-22T19:51:53.737",
"AnswerCount": 5
   }, <...> {
"Id": 3220242,
"CreationDate": "2010-07-10T17:47:54.397",
"AnswerCount": 6
   }
]
}, <...> {
"Id": 69083,
"DisplayName": "Guffa",
   "Reputation": 504630,
"Posts": [{
"Id": 2643812,
"CreationDate": "2010-04-15T08:41:19.703",
"AnswerCount": 2
}, {
"Id": 2740709,
"CreationDate": "2010-04-29T20:53:45.680",
"AnswerCount": 1
   }
]
}

]

Durch die Benennung der Spalten kann die Struktur des JSON-Dokuments beeinflusst werden. Sollen die drei Eigenschaften "Id", "DisplayName" und "Reputation" in ein Attribut "User" wandern, so müssen lediglich die Spaltennamen angepasst werden.

Eine komplexe Abfrage mit "FOR JSON PATH"-Zusatz und angepassten Spaltennamen:

SELECT (SELECT Users.Id AS "User.Id"
,    Users.DisplayName AS "User.DisplayName"
,    Users.Reputation AS "User.Reputation"
,    (SELECT Posts.Id, Posts.CreationDate, Posts.AnswerCount
   FROM dbo.Posts
       WHERE Posts.OwnerUserId = Users.Id
       AND Posts.AnswerCount > 0
   ORDER BY Posts.CreationDate
   FOR JSON PATH
       ) "Posts"
FROM dbo.Users
WHERE Users.Reputation > 500000
ORDER BY Users.Reputation desc
FOR JSON PATH
   ) AS Ausgabe;

Auszug aus dem Ergebnis mit angepassten Spaltennamen, bereits mit Notepad++ und JSTool formatiert:

[{
"User": {
"Id": 22656,
   "DisplayName": "Jon Skeet",
   "Reputation": 990402 },
"Posts": [{
"Id": 194484,
"CreationDate": "2008-10-11T19:30:45.407",
"AnswerCount": 37
}, {
"Id": 215548,
"CreationDate": "2008-10-18T20:47:40.003",
"AnswerCount": 42
}, <...> {
"Id": 3438806,
"CreationDate": "2010-08-09T09:40:42.517",
"AnswerCount": 10
   }, {
"Id": 3657778,
"CreationDate": "2010-09-07T10:27:17.253",
"AnswerCount": 9
}
]
}, {
"User": {
"Id": 29407,
   "DisplayName": "Darin Dimitrov",
"Reputation": 768383 },
"Posts": [{
"Id": 269988,
"CreationDate": "2008-11-06T19:40:47.670",
"AnswerCount": 6
   }, {
"Id": 311696,
"CreationDate": "2008-11-22T19:51:53.737",
"AnswerCount": 5
   }, <...> {
"Id": 3220242,
"CreationDate": "2010-07-10T17:47:54.397",
    "AnswerCount": 6
}
]
   }, <...> {
"User": {
   "Id": 69083,
   "DisplayName": "Guffa",
   "Reputation": 504630 },
"Posts": [{
"Id": 2643812,
"CreationDate": "2010-04-15T08:41:19.703",
"AnswerCount": 2
   }, {
"Id": 2740709,
  "CreationDate": "2010-04-29T20:53:45.680",
  "AnswerCount": 1
}
   ]
}
]

Noch schnell ein Beispiel, das überall funktioniert

Sie möchten das gerne mal schnell ausprobieren, ohne die Datenbank StackOverflow zu installieren? Dann lassen Sie sich doch ein paar Eigenschaften der Dateien ihrer Systemdatenbanken anzeigen.

Informationen über die Dateien der Systemdatenbanken als JSON-Dokument:

SELECT (SELECT name AS "DatabaseName"
, (SELECT file_id, type_desc, name, physical_name, size
FROM sys.master_files
WHERE master_files.database_id = databases.database_id
FOR JSON PATH
) "Files"
FROM sys.databases
WHERE database_id <= 4 FOR JSON PATH
) AS Ausgabe;

Ausblick

Der SQL Server kann JSON aber nicht nur generieren, sondern auch wieder zurück in eine relationale Datenstruktur umwandeln. Der Empfänger der Informationen kann diese also nicht nur selbst lesen, sondern auch mit dem SQL Server weiter verarbeiten.

Dieses Thema werden wir aber in einem separaten Artikel beleuchten.

Links/Quellen

JSON-Daten in SQL Server: https://docs.microsoft.com/de-de/sql/relational-databases/json/json-data-sql-server

Formatieren von Abfrageergebnisse als JSON mit FOR JSON (SQL Server): https://docs.microsoft.com/de-de/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server