JSON zu CSV bei verschachtelter Struktur: warum es keine richtige Antwort gibt

Dieselben zwei Bestellungen, zweimal

Hier ist die Eingabe, und sie bleibt den ganzen Artikel über gleich. Zwei Bestellungen. Die erste hat die id 1, ein customer-Objekt mit name Emma und city Paris, ein tags-Array aus zwei Zeichenketten und ein lines-Array aus zwei Objekten, jedes mit sku und qty. Die zweite hat die id 2, ein customer-Objekt mit Liam und Berlin, ein tags-Array aus einer Zeichenkette, ein lines-Array aus einem Objekt und einen zusätzlichen Schlüssel, den der erste Datensatz nicht hat: note mit dem Wert urgent. Nichts Exotisches — das ist die Form jeder Bestellung, Rechnung und Event-Payload, die je aus einer API kam.

Lauf sie durch den CSV-/JSON-/YAML-Konverter, und du bekommst fünf Spalten: id, customer, tags, lines, note. Die customer-Zelle der ersten Zeile enthält die Zeichen {""name"":""Emma"",""city"":""Paris""} — das Objekt zurück nach JSON serialisiert und dann als CSV-Feld zitiert, was jedes darin enthaltene Anführungszeichen verdoppelt. Die lines-Zelle enthält das ganze Array auf dieselbe Weise. Öffne das in einer Tabellenkalkulation, und du hast zwei Zeilen, fünf Spalten und drei Zellen, die du weder sortieren noch filtern noch summieren kannst.

Lauf genau dasselbe JSON durch den JSON-zu-CSV-Konverter mit verschachtelten Werten auf Abflachen, und du bekommst zehn Spalten: id, customer.name, customer.city, tags.0, tags.1, lines.0.sku, lines.0.qty, lines.1.sku, lines.1.qty, note. Die zweite Bestellung hat ein Tag und eine Position, also sind tags.1, lines.1.sku und lines.1.qty in dieser Zeile leer. Jeder Wert ist jetzt ein Skalar in einer eigenen Spalte. Die Zeilenzahl hat sich nicht geändert — es sind weiter zwei — und die Datei wird nun vom größten Datensatz geformt und nicht vom Schema.

Keine der beiden Ausgaben ist ein Fehler. Serialisieren ist richtig, wenn das CSV ein Transportformat ist und später etwas diese Zellen parst: Die Form des Datensatzes bleibt exakt erhalten, und ein Maschinenrundlauf gibt zurück, was hineinging. Abflachen ist richtig, wenn ein Mensch die Datei öffnet: Jedes Blatt ist sortier-, filter- und summierbar. Ein Fehler ist, das eine oder andere zu tun, ohne zu wissen, was man getan hat — und drei Wochen später zu merken, dass die Analystin Zeilen in einer Datei zählt, deren Zeilenzahl eine andere Frage beantwortet als ihre.

Punktpfade, Klammerindizes und der Schlüssel, der schon einen Punkt enthält

Sobald du dich fürs Abflachen entscheidest, musst du die Blätter benennen. Zwei Schreibweisen sind verbreitet. Punktpfade schreiben einen Array-Index wie jeden anderen Schlüssel: tags.0, lines.1.sku. Klammerindizes unterscheiden die beiden Arten von Schritt: tags[0], lines[1].sku. Der eigene JSON-Flattener dieser Seite bietet beide, dazu die Wahl des Trenners — Punkt, Unterstrich oder Schrägstrich — denn ein Unterstrich übersteht Systeme, die den Punkt als Pfadoperator lesen, und ein Schrägstrich greift die Zeigersyntax auf, die man von JSON Pointer kennt. Der Abflachmodus des JSON-zu-CSV-Konverters verwendet immer Punkte für beides, die kompaktere der zwei Schreibweisen und diejenige, die Tabellenkalkulationen am wenigsten verstümmeln.

In der Punktschreibweise steckt ein echter Fehlschlag, und man sollte ihn klar benennen, weil er Daten kostet. Ein Punktpfad ist mehrdeutig: Die Spalte customer.name kann den Schlüssel name im Objekt customer meinen oder einen Schlüssel oberster Ebene, dessen wörtlicher Name customer.name lautet. JSON erlaubt beides, im selben Objekt. Gib dem Abflachmodus einen Datensatz mit einem wörtlichen Schlüssel a.b und dem Wert 1 neben einem verschachtelten Objekt a, dessen Schlüssel b 2 enthält, und die Ausgabe hat eine Spalte, a.b, mit dem Wert 2. Der erste Wert ist weg, ohne Warnung, ohne zweite Spalte. Selten, aber nicht hypothetisch: Schlüssel mit Punkten tauchen in Logfeldern, in Analytics-Ereignisnamen und in allem auf, was von einer Namensraum-Kennung abgeleitet ist.

Die Abwehr ist die Wahl des Trenners. Flache mit Unterstrich oder Schrägstrich statt Punkt ab, und die Kollision braucht einen Schlüssel, der genau dieses Zeichen wörtlich enthält, was weit unwahrscheinlicher ist. Kannst du den Trenner nicht wählen — und im Abflachmodus des CSV-Konverters kannst du es nicht — prüfe deine Schlüssel vor dem Abflachen auf Punkte, nicht danach.

Arrays: je eine Spalte, eine einzige Zelle oder je eine Zeile

Ein Array aus Skalaren hat drei vernünftige Antworten. Serialisieren — die tags-Zelle wird zu den Zeichen ["vip","eu"]. Jedem Element eine Spalte geben — tags.0 und tags.1. Oder die Elemente mit einem Trenner verbinden, der in ihnen nicht vorkommt, sodass die tags-Zelle vip|eu liest und eine Tabellenformel sie wieder auftrennen kann. Die Konverter hier machen die ersten beiden und keiner die dritte: Willst du eine verbundene Zeichenkette, musst du sie vor der Umwandlung erzeugen. Die verbundene Form ist die lesbarste und die einzige, deren Spaltenzahl sich nicht ändert, wenn sich die Daten ändern — deshalb nutzen sie so viele Exporte, obwohl sie am schlechtesten definiert ist.

Bei einem Array aus Objekten trennen sich Werkzeuge und Datenbanken. Bei lines mit zwei Einträgen verbreitert der Abflachmodus die Tabelle: lines.0.sku, lines.0.qty, lines.1.sku, lines.1.qty. Jemand aus der Datenbankwelt erwartete das Gegenteil — eine Ausgabezeile je Position, mit den Bestellfeldern über den Block wiederholt, was ein Join erzeugt und was eine Pivot-Tabelle will. Keiner der Konverter tut das, und der Unterschied ist nicht kosmetisch. Verbreitern hält eine Zeile je Bestellung: Eine Zeilenzahl ist eine Bestellzahl. Aufsprengen gibt eine Zeile je Position: Eine Zeilenzahl ist eine Positionszahl, und die Bestellfelder sind dupliziert. Beides kommt vor; nur eines beantwortet die Frage, wie viele Bestellungen wir versandt haben.

Eine Folge des Verbreiterns sollte man einplanen: Der Spaltenzuschnitt wird vom größten Array irgendwo in der Datei bestimmt und ändert sich, wenn sich die Daten ändern. Zwei Datensätze, deren Tag-Arrays ein und drei Elemente enthalten, ergeben die Spalten t.0, t.1 und t.2, mit zwei leeren Zellen beim kurzen Datensatz. Exportiere morgen dieselbe Abfrage mit einem Datensatz von vier Tags, und die Datei bekommt stillschweigend eine Spalte dazu. Alles weiter unten, das Spalten nach Position statt nach Name liest, bricht an diesem Tag — und der Export, der es gebrochen hat, sieht aus wie der vorige.

Datensätze, die sich über ihre Schlüssel nicht einig sind

JSON hat kein Schema, ein Array aus Objekten ist also keine Tabelle, bis du eine daraus machst. Beide Konverter bilden die Vereinigung aller gesehenen Schlüssel und lassen eine Lücke, wo ein Datensatz einen nicht hat. Drei Datensätze mit {id, a}, {id, b} und {id, a, c} ergeben vier Spalten — id, a, b, c — mit leeren Zellen dort, wo der jeweilige Datensatz schweigt. Das ist die einzige Antwort, die nichts verliert, und deshalb ist ein aus einem Dokumentenspeicher exportiertes CSV meist viel breiter als jedes einzelne Dokument.

Die Spaltenreihenfolge ist weder sortiert noch über Exporte hinweg stabil. Beide Konverter nutzen die Reihenfolge des ersten Auftretens: Die Schlüssel erscheinen so, wie der erste Datensatz, der sie enthält, sie präsentiert. Zwei Datensätze {b, a} und {a, b} ergeben die Spalten b und dann a, weil der erste zuerst gelesen wurde. Ändere die Sortierung deiner Abfrage, und die Spaltenreihenfolge ändert sich mit, obwohl die Daten identisch sind. Hängt weiter unten etwas von der Spaltenreihenfolge ab, sortiere die Schlüssel selbst vor dem Export.

Eines geht verloren, gleich welche Strategie du wählst, und schuld ist CSV, nicht der Konverter. Ein Schlüssel mit null, ein Schlüssel mit der leeren Zeichenkette und ein schlicht fehlender Schlüssel werden alle zur selben leeren Zelle. Lies dieses CSV zurück, und jeder von ihnen kommt als leere Zeichenkette wieder. Wenn der Unterschied zählt — und bei einer Teilaktualisierung oder einer nullbaren Spalte zählt er immer — ist CSV für dieses Feld das falsche Format, und keine Abflachoption rettet es.

Was die CSV-Seite erkennt und was nicht

Beginnen wir mit der Richtung, die diesem Konverter früher peinlich war. Gib dem CSV-/JSON-/YAML-Konverter das JSON [1,2,3], und er liefert value / 1 / 2 / 3: eine einzelne erzeugte Spalte, denn eine Zahl hat keinen eigenen Schlüssel, der zum Spaltennamen werden könnte, und value ist der einzige ehrliche Name dafür. Ein Skalar auf oberster Ebene, 42, ergibt value / 42. Ein gemischtes Array, [1,{"a":2}], ergibt die Kopfzeile value,a und zwei Zeilen — 1 gefolgt von einer leeren Zelle, dann eine leere Zelle gefolgt von 2 —: der Skalar in der erfundenen Spalte, das Objekt in seiner eigenen. Genau das liefert auch der Schwesterkonverter JSON zu CSV, die beiden Werkzeuge dieser Seite sind sich beim häufigsten Fall also einig: einer API, die eine blanke Liste von Kennungen zurückgibt.

Beim Lesen schnüffelt der Konverter sein Trennzeichen jetzt heraus, statt das Komma anzunehmen: Er zählt Kommas gegen Semikolons im ersten Datensatz und übergeht dabei alles in Anführungszeichen, name;city über Emma;Paris kommt also als {"name":"Emma","city":"Paris"} an — was zählt, denn Excel schreibt in fünf der sechs Märkte dieser Seite standardmäßig Semikolon-CSV. Wiederholte Spaltennamen werden umbenannt statt verworfen: name,name,name über a,b,c liefert name, name_2 und name_3. Eine Zeile, die über die Kopfzeile hinausläuft, behält die überzählige Zelle unter einem erfundenen Namen: a,b über 1,2,3 liefert a, b und column3. Was er weiterhin nicht bewertet, ist ein Tabulator oder ein Pipe. Das eigens gebaute Werkzeug CSV zu JSON wiegt vier Kandidaten ab und lässt dich einen erzwingen; dieser Konverter wiegt zwei ab und hat in seiner Oberfläche überhaupt keine Trennzeichenauswahl, eine tabulatorgetrennte Datei kommt also weiter als eine einzige Spalte an, deren Schlüssel die ganze Kopfzeile ist. Und was auch hineingeht: Das CSV, das er zurückschreibt, ist kommagetrennt.

Häufige Fragen

Verschachtelte Werte serialisieren oder abflachen?

Frag, wer die Datei öffnet. Ist die Antwort ein Programm, das sie erneut parst, serialisiere: Die Form des Datensatzes bleibt exakt erhalten, und der Rundlauf gibt zurück, was hineinging. Ist die Antwort ein Mensch in einer Tabellenkalkulation, flache ab: Er muss nach customer.city sortieren und lines.0.qty summieren, und beides geht mit einem JSON-Klumpen in einer Zelle nicht. Ist die Antwort beides, erzeuge zwei Dateien statt eines Kompromisses, denn der Kompromiss — eine Ebene abflachen und den Rest serialisieren — ist die Fassung, die sechs Monate später niemand mehr durchschaut. Und ist die Datei ein Archiv statt eines Berichts, serialisiere: Abflachen brennt die heutigen Array-Längen in den Spaltenzuschnitt, und der Export des nächsten Monats passt nicht dazu.

Kann ich eine Zeile je Position statt zusätzlicher Spalten bekommen?

Von keinem der Konverter — beide verbreitern die Tabelle, keiner sprengt ein Array in Zeilen. Der Grund: Aufsprengen ist keine Formatierungsentscheidung, sondern ein Wechsel der Granularität. Die entstehende Datei beantwortet eine andere Frage, und der Konverter müsste entscheiden, welches Array er sprengt, wenn ein Datensatz zwei enthält. Mach es vor der Umwandlung, in dem, was das JSON erzeugt: gib ein Objekt je Position aus, jedes mit den Bestellfeldern, die es braucht. Dann verschwindet die Abflachfrage, weil das Array weg ist. Hast du nur das JSON, ist ein kurzes Skript, das jede Bestellung auf ihre Positionen abbildet und die Ergebnisse aneinanderhängt, eine Fünfzeilenarbeit — und lässt die Entscheidung in deinem eigenen Code sichtbar statt in den Voreinstellungen eines Werkzeugs vergraben.

Warum hat meine Datei zwischen zwei Exporten derselben Abfrage eine Spalte dazubekommen?

Weil der Spaltenzuschnitt eines abgeflachten Exports eine Eigenschaft der Daten ist, nicht der Abfrage. Die Spalten sind die Vereinigung aller vorhandenen Pfade, und Array-Pfade werden bis zur Länge des längsten Arrays im Ergebnis nummeriert. Ein Datensatz mit vier Tags, wo es beim letzten Lauf höchstens drei waren, fügt jeder Zeile tags.3 hinzu. Derselbe Mechanismus fügt eine Spalte hinzu, wenn ein einziger Datensatz einen optionalen Schlüssel enthält, den vorher niemand benutzt hat. Zwei Abwehrmaßnahmen: Spalten überall weiter unten nach Namen statt nach Position lesen, und wenn ein stabiler Zuschnitt wirklich zählt, die Spaltenliste ausdrücklich definieren und darauf projizieren, statt sie den Exporteur aus dem gerade vorliegenden Ergebnis ableiten zu lassen.

Kann ich das abgeflachte CSV wieder in das ursprüngliche JSON verwandeln?

Teilweise, und die Lücken sind vorhersehbar. Der JSON-Flattener hat einen Aufbau-Modus, der die Verschachtelung aus Pfadschlüsseln rekonstruiert: Ein Segment, das eine reine Zahl ist, baut ein Array, alles andere ein Objekt, also kommen customer.name und tags.0 als Objekt und Array zurück. Drei Dinge kommen nicht zurück. Die Typen sind weg, weil jede Zelle eines CSV Text ist — eine als 1 geschriebene Zahl kommt als Zeichenkette "1" zurück, solange du sie nicht umwandelst. Der Unterschied zwischen null, leerer Zeichenkette und fehlendem Schlüssel ist weg, wie oben beschrieben. Und ein leeres Array oder leeres Objekt hinterlässt in einer Punkt-Abflachung überhaupt keinen Pfad, ist also nicht rekonstruierbar; der Flattener schreibt genau deshalb ein sichtbares [] oder {}, aber nur wenn du mit diesem Werkzeug abgeflacht hast. Der Rundlauf über die Serialisierungsstrategie verliert nichts davon, und das ist ihr ganzes Argument.

Mein CSV verwendet Semikolons. Muss ich es vorher umwandeln?

Nein. Der CSV-/JSON-/YAML-Konverter zählt im ersten Datensatz Kommas gegen Semikolons, außerhalb aller zitierten Felder, und nimmt den Sieger: Ein französischer, deutscher, spanischer, italienischer oder portugiesischer Excel-Export liest sich ohne jede Einstellung richtig. Was weiterhin scheitert, ist ein Tabulator oder ein Pipe: Beide gehören nicht zu den bewerteten Kandidaten, eine tabulatorgetrennte Datei ergibt also ein einziges Feld je Zeile, dessen Schlüssel deine ganze Kopfzeile ist. Das Symptom ist unverwechselbar, sobald man es kennt: ein einzelner Schlüssel mit Tabulatoren im Namen. Zwei Auswege. Wandle das Trennzeichen zuerst mit dem Trennzeichen-Konverter um, der sauber parst und alles Nötige neu in Anführungszeichen setzt. Oder nimm das eigens gebaute Werkzeug CSV zu JSON, das vier Kandidaten abwägt und dich einen erzwingen lässt. Und noch dies: Was auch hineingeht, das CSV, das dieser Konverter zurückschreibt, ist kommagetrennt.