Von Andreas Jordan auf Montag, 20. August 2018
Kategorie: Data Management

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