JSON in SQL umwandeln

Der typische Fall ist eine eigene Tabelle, die mit Daten gefüllt werden soll: eine Entwicklungsdatenbank mit Testdaten, ein Fixture für die Testsuite, eine Nachschlagetabelle aus dem, was eine API zurückgegeben hat. Die Tabelle wird selbst angelegt, gebraucht werden nur die Zeilen, ohne sie von Hand abzutippen.

Gerade bei Firmendaten ist dabei wichtig, wo diese Umwandlung stattfindet. Ein JSON-Export mit Kundendaten oder Bestellungen ist genau das, wofür Unternehmen nach DSGVO Rechenschaft ablegen müssen — ein Werkzeug, das die Datei irgendwohin schickt, nur um Anführungszeichen zu verdoppeln, ist dabei ein schlechter Tausch, und hier gibt es diesen Tausch gar nicht erst.

Die Ausgabe besteht ausschließlich aus INSERT-Anweisungen. Das ist eine bewusste Grenze, keine fehlende Funktion. JSON sagt, dass ein Wert eine Zahl ist, aber nicht, ob die Spalte eine Ganzzahl oder eine Dezimalzahl mit zwei Nachkommastellen sein soll, ob sie NULL zulassen darf, welches Feld der Primärschlüssel ist oder wie lang ein Textfeld werden darf. Das sind Entscheidungen, für die ein Schema existiert, und eine Umwandlung, die sie erraten würde, erzeugte ein CREATE TABLE, das man Zeile für Zeile nachprüfen müsste — mehr Arbeit, als es selbst zu schreiben.

In der Praxis bedeutet das: Die Tabelle existiert schon, bevor die Datei läuft. Einmal umwandeln, die Spaltenliste aus der ersten Anweisung ablesen, das CREATE TABLE dazu schreiben, dann ausführen. Das dauert zwei Minuten und legt die Schema-Entscheidungen dorthin, wo sie hingehören — in eine Migration, die man prüfen kann, statt in eine generierte Datei, die zufällig beim ersten Beispiel funktioniert hat.

Woanders könnte er auch nicht herkommen. Ein JSON-Dokument trägt keinen eigenen Namen, also liefert die hochgeladene Datei einen: bestellungen.json ergibt INSERT INTO bestellungen, und export-2024.json wird zu export_2024, weil ein Bindestrich in einem unquotierten Bezeichner nicht erlaubt ist und eine führende Ziffer eine vorangestellt bekommt.

Die Datei vor der Umwandlung umzubenennen ist deshalb der günstigste Weg, das Ergebnis zu steuern. Danach hieße es, jede einzelne Anweisung per Suchen-und-Ersetzen anzupassen — und dabei aufzupassen, keinen Wert zu treffen, der zufällig denselben Text enthält. Dreißig Sekunden im Dateimanager sind schneller als ein regulärer Ausdruck über hunderttausend Zeilen.

Eine relationale Tabelle ist flach, JSON nicht — also wird ein verschachteltes Objekt in den Spaltennamen aufgelöst: Ein Datensatz mit einem kunde-Objekt, das ein stadt-Feld enthält, ergibt eine Spalte kunde_stadt. Der Pfad bleibt erhalten, der Punkt wird zum Unterstrich, weil ein Punkt in einem Bezeichner nicht zulässig ist, und nichts geht dabei verloren.

Eine Verschachtelungsebene ergibt eine Tabelle, die jeder gern anlegen würde. Zwei sind meist noch vertretbar. Darüber hinaus ist die entstehende Spaltenliste eher ein Hinweis als ein brauchbares Ergebnis: Eine vier Ebenen tiefe JSON-Struktur beschreibt Beziehungen, und das passende Schema dafür sind mehrere Tabellen mit Fremdschlüsseln zwischeneinander. In eine einzige breite Tabelle geladen funktioniert es zwar, macht aber jede spätere Abfrage schwerer als nötig.

Ein Datensatz mit drei Schlagworten ergibt tags_0, tags_1 und tags_2. Jeder Wert bleibt erhalten, aber die Form wird mit jedem Datensatz unpraktischer: Der nächste Eintrag mit fünf Schlagworten verbreitert die Tabelle um zwei weitere Spalten, und eine sinnvolle Abfrage nach „dem zweiten Schlagwort" gibt es nicht.

Eine relationale Datenbank hat dafür die übliche Antwort — eine zweite Tabelle, eine Zeile je Eintrag, mit der Kennung des übergeordneten Datensatzes daneben. Von einem JSON-Export dorthin zu kommen bedeutet, zweimal umzuwandeln und das Array vorher herauszulösen, oder die breite Tabelle als Zwischenschritt zu laden und per Abfrage zu normalisieren. Hat die Zieldatenbank einen nativen JSON-Spaltentyp, ist das Array als Ganzes in einer Spalte zu speichern die dritte und oft einfachste Option, wenn es nur mitgeführt und nicht abgefragt werden muss.

JSON-Datensätze in einem Export müssen nicht übereinstimmen. Eine API lässt Felder ohne Wert häufig einfach weg, sodass tausend Datensätze ein Dutzend verschiedene Schlüsselmengen mitbringen können. Die Umwandlung gleicht das aus, indem sie die Vereinigung aller im Dokument vorkommenden Schlüssel bildet und dort NULL schreibt, wo ein Datensatz nichts hat.

Diese Einheitlichkeit macht die Datei als Stapel ausführbar: Die Spaltenliste ist in jeder Anweisung identisch, also nimmt eine Tabelle, die die erste Zeile akzeptiert, auch alle folgenden. Das erklärt auch einen Effekt, den viele bemerken: Zehn Datensätze zum Testen umgewandelt liefern manchmal weniger Spalten als die ganze Datei, weil die Stichprobe die selteneren Felder einfach nicht enthielt. Das CREATE TABLE gehört deshalb an eine Umwandlung der ganzen Datei, nie an eine Stichprobe.

Zahlen werden unverändert geschrieben, Wahrheitswerte als TRUE und FALSE, ein JSON-null als NULL, alles andere als Zeichenkette in einfachen Anführungszeichen mit verdoppelten internen Anführungszeichen. Das Verdoppeln ist die SQL-Standardform, die jede Datenbank versteht — ein Name wie „O’Brien" lädt damit überall korrekt.

Backslashes werden dagegen unverändert durchgereicht, was dem Standard entspricht, aber nicht der Grundeinstellung von MySQL: Dort leitet ein Backslash in der Voreinstellung eine Escape-Sequenz ein, sofern NO_BACKSLASH_ESCAPES nicht gesetzt ist. Wer Windows-Pfade wie C:\Berichte\2024 oder reguläre Ausdrücke in den Daten hat, sollte den Modus für die Sitzung vorher setzen und danach eine betroffene Zeile stichprobenartig prüfen, statt sich darauf zu verlassen.

Hier ist eine JSON-Quelle im Vorteil gegenüber einer tabellenförmigen. Eine Postleitzahl wie „01067" ist im JSON eine Zeichenkette und wird deshalb als quotierte SQL-Zeichenkette geschrieben — die führende Null bleibt in der Datenbank erhalten. Eine CSV derselben Daten kennt dagegen keine Typen, und jedes Werkzeug in der Kette bekommt eine Chance, aus „01067" die Zahl 1067 zu machen.

Denselben Schutz genießen lange numerische Kennungen, sofern sie als Zeichenketten exportiert wurden. Waren sie als JSON-Zahlen exportiert, gilt die übliche Grenze der Gleitkommagenauigkeit — ab etwa sechzehn Stellen ist die Präzision schon vor dieser Umwandlung verloren, und keine Sorgfalt hier holt sie zurück. Lässt sich der Export selbst noch anpassen, ist das Exportieren als Zeichenkette die richtige Stelle für die Korrektur, nicht diese Seite.

Die Ausgabe ist eine Anweisung je Datensatz, ohne Transaktionsrahmen und ohne mehrzeiliges VALUES. Fünfzigtausend einzelne Anweisungen über einen Client laufen zu lassen ist langsam, weil jede einzelne ein Umlauf zum Server ist. Die ganze Datei in BEGIN und COMMIT einzupacken ist eine Zeile an jedem Ende und meist die größte einzelne Verbesserung.

Die Anweisungen selbst sind bewusst schlicht und portabel gehalten: keine herstellerspezifische Quotierung, kein ON CONFLICT, kein Schemapräfix. Was der eigene Datenbankserver zusätzlich möchte, lässt sich per Suchen-und-Ersetzen auf INSERT INTO ergänzen — ein Schemaname, ein ON CONFLICT DO NOTHING für einen wiederholbaren Ladelauf —, und genau deshalb lohnt es sich, von der einfachsten möglichen Form auszugehen.

Für ein Seed-File, ein Fixture oder ein paar tausend Zeilen sind Anweisungen die praktische Form: lesbar, versionierbar, und lauffähig auf jedem System mit einem Client. Genau dafür ist diese Seite gedacht.

Ab einer gewissen Größe ändert sich die Rechnung. Jede Datenbank hat einen Bulk-Weg — COPY bei Postgres, LOAD DATA bei MySQL, einen Import im Werkzeug des Anbieters — der eine getrennte Datei weit schneller liest, als einzelne Inserts sie ausführen könnten; bei einer Million Zeilen ist das der Unterschied zwischen Minuten und Stunden. In diesem Fall ist die CSV- oder TSV-Ausgabe desselben Exports die richtige Wahl, oder NDJSON, wenn das Ziel ein Data-Warehouse statt einer relationalen Datenbank ist.

Diese Seite geht von einem gewöhnlichen JSON-Dokument aus: einem Array von Objekten oder einem einzelnen Objekt, das als Ganzes eingelesen wird, bevor die erste Anweisung entsteht. Für einen Export mit ein paar tausend Datensätzen ist das die richtige Form, und es ist auch die Form, in der die meisten APIs und Admin-Oberflächen ihre Exportfunktion anbieten.

Ein Export im NDJSON-Format — ein Datensatz pro Zeile, ohne umschließendes Array — löst ein anderes Problem: Er lässt sich Zeile für Zeile verarbeiten, ohne das gesamte Dokument vorher im Speicher zu halten, was bei einem mehrere Gigabyte großen Datenexport der eigentliche Engpass ist. Für diesen Fall gibt es eine eigene Umwandlung, die genau auf diese Zeilenstruktur zugeschnitten ist. Diese Seite hier ist auf die Form eines JSON-Exports spezialisiert, nicht auf seine Menge.

Der häufigste Fall in der Praxis sieht so aus: Eine lokale Datenbank läuft über docker-compose oder ein vergleichbares Setup, ein Kollege hat einen JSON-Export aus der Produktion geschickt, anonymisiert oder mit Testdaten ersetzt, und dieser Export soll als Ausgangszustand für die nächste Testsuite dienen. Die INSERT-Anweisungen werden dabei in ein Migrations- oder Seed-Verzeichnis gelegt, das mit dem Projekt versioniert wird, statt bei jedem Checkout neu erzeugt zu werden.

Wichtig ist dabei, den Export vorher zu prüfen statt blind zu vertrauen: Ein Produktionsexport enthält häufig personenbezogene Daten, und diese in eine gemeinsam genutzte Testdatenbank zu laden ist unabhängig vom technischen Ablauf ein eigenständiges Risiko. Wo möglich lohnt sich eine Anonymisierung vor der Umwandlung, nicht danach — die Spaltennamen ändern sich dabei nicht, nur die Werte darin.

Die Anweisungen entstehen durch JavaScript in diesem Tab. Es gibt kein Hochladen, kein Konto, keine Warteschlange, und die kostenlose Nutzung erlaubt bis zu 100 MB — die eigentliche Grenze ist der Arbeitsspeicher des Geräts, weil das gesamte Dokument geparst wird, bevor überhaupt etwas geschrieben wird.

Das ist bei diesem Format kein Nebeneffekt. Was hier in eine Datenbank eingefügt werden soll, ist per Definition etwas, das jemand bewusst behalten will — Kunden, Bestellungen, Konten. Ein Werkzeug, das das erst irgendwohin schickt, ist bei Firmendaten die falsche Wahl, und dieser Umweg entfällt hier von vornherein.