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.

Dies ist die Fortsetzung zum ersten Teil: JSON als elegante Formatierung von Abfrage-Ergebnissen zum schnellen Datenaustausch.

Vorab: Meine Beispieldaten

Hier zunächst ein korrekt als JSON-formatierter Auszug der im ersten Teil erzeugten Daten.

Datenpaket 1: Id, Name und Reputation von fünf Nutzern

Datenpaket 2: Id, Name und Reputation von drei Nutzern sowie jeweils einige ihrer Beiträge mit Angabe von Id, Erstellungsdatum und Anzahl der Antworten ​

Aufbau der Zieltabellen

Um beide Datenpakete importieren zu können, werden diese Tabellen benötigt:

Import einer flachen JSON-Struktur

Das erste Datenpaket enthält lediglich Objekte eines Typs und kann daher in eine einzelne Tabelle importiert werden.

Vor dem INSERT schauen wir uns die Daten aber erst einmal an. Die OPENJSON-Funktion zerlegt das Datenpaket in die einzelnen Objekte und zeigt jedes Objekt als eine Zeile an.

Jedes Objekt ist weiterhin ein JSON-String, der in einer Spalte mit dem Namen „value" ausgegeben wird.

Durch die Angabe der optionalen WITH-Klausel kann das Ausgabeschema definiert werden, wir können uns so also die Attribute der Objekte in einzelnen Spalten ausgeben lassen.

So ist es nur noch ein kleiner Schritt, die Daten in die Tabelle einzufügen.

Import einer geschachtelten JSON-Struktur

Einer der großen Vorteile des JSON-Formates ist es, dass auch komplexe Strukturen in einem Dokument verwendet werden können. In unserem Fall werden zu jedem Nutzer noch mehrere Beiträge gespeichert, es handelt sich also um eine klassische 1:n-Relation.

Auch hier zunächst die Ausgabe von OPENJSON.

Hier bekommen wir wieder eine Zeile pro JSON-Objekt. Diese JSON-Objekte bestehen aber wiederum aus zwei weiteren Objekten: „User" und „Posts", deren Inhalt wiederum JSON-Objekte sind.

Die DECLARE-Zeile lasse ich im Folgenden jeweils weg, damit es hier übersichtlicher ist. Das nächste SQL lautet also:

Zu beachten ist hier, dass „User" ein reserviertes Wort ist und daher in eckige Klammern gesetzt werden muss. Zudem wird der Zusatz „AS JSON" benötigt, damit der SQL Server das Ergebnis richtig interpretiert.

Um nun an die einzelnen Informationen zu einem Benutzer zu gelangen, muss der Inhalt der Spalte „User" wieder mit der Funktion OPENJSON zerlegt werden. Hier nutze ich die Funktionalität „CROSS APPLY".

Auch zur Zerlegung der Beiträge kann diese Funktion genutzt werden, indem einfach ein weiteres CROSS APPLY angefügt wird.

Damit steigt allerdings die Anzahl der ausgegebenen Datensätze an, da für jeden Beitrag eine Zeile erzeugt wird. Die Informationen zu den Benutzern erscheinen dabei redundant.

Um nun den Import in die Tabellen vornehmen zu können, müssen lediglich beide Abfragen nacheinander ausgeführt werden: Einmal nur die Benutzer und einmal die Beiträge mit zusätzlicher Angabe der Benutzer-Id.

Fazit

Mit wenig Aufwand kann das Ergebnis einer Abfrage in ein JSON-Dokument umgewandelt werden und mit ebenso wenig Aufwand auch wieder in eine relationale Datenbank importiert werden.

Auch wenn ich die Funktionalität hier am Beispiel des Microsoft SQL Servers gezeigt habe, so ist dies auch bei anderen relationalen Datenbanken möglich.

Links/Quellen

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

Dokumentation der Funktion OPENJSON: https://docs.microsoft.com/de-de/sql/t-sql/functions/openjson-transact-sql