Da JSON a CSV quando la struttura è annidata: perché non esiste una risposta giusta

Gli stessi due ordini, due volte

Ecco l'input, e resterà lo stesso per tutto l'articolo. Due ordini. Il primo ha id 1, un oggetto customer con name Emma e city Paris, un array tags di due stringhe, e un array lines di due oggetti, ciascuno con uno sku e una qty. Il secondo ha id 2, un oggetto customer con Liam e Berlin, un array tags di una stringa, un array lines di un oggetto, e una chiave in più che il primo record non ha: note, con valore urgent. Nulla di esotico: è la forma di qualsiasi ordine, fattura o payload di evento mai uscito da un'API.

Passala nel convertitore CSV / JSON / YAML e ottieni cinque colonne: id, customer, tags, lines, note. La cella customer della prima riga contiene i caratteri {""name"":""Emma"",""city"":""Paris""} — l'oggetto riserializzato in JSON e poi citato come campo CSV, il che raddoppia ogni virgoletta al suo interno. La cella lines porta l'intero array allo stesso modo. Apri quel file in un foglio di calcolo e hai due righe, cinque colonne e tre celle che non puoi ordinare, filtrare né sommare.

Passa esattamente lo stesso JSON nel convertitore da JSON a CSV con i valori annidati impostati su appiattisci, e ottieni dieci colonne: id, customer.name, customer.city, tags.0, tags.1, lines.0.sku, lines.0.qty, lines.1.sku, lines.1.qty, note. Il secondo ordine ha un tag e una riga, quindi tags.1, lines.1.sku e lines.1.qty sono vuote su quella riga. Ogni valore è ora uno scalare in una colonna propria. Il numero di righe non è cambiato — sono sempre due — e il file è ora modellato dal record più grande e non dallo schema.

Nessuno dei due output è un bug. Serializzare è giusto quando il CSV è un formato di trasporto e qualcosa analizzerà quelle celle più tardi: la forma del record è preservata identica, e un giro di andata e ritorno fra macchine restituisce ciò che è entrato. Appiattire è giusto quando una persona aprirà il file: ogni foglia è ordinabile, filtrabile e sommabile. È un bug fare l'una o l'altra senza sapere quale hai fatto, e scoprire tre settimane dopo che l'analista sta contando righe in un file il cui conteggio di righe risponde a una domanda diversa dalla sua.

Percorsi con punti, indici tra parentesi quadre, e la chiave che contiene già un punto

Una volta deciso di appiattire, bisogna nominare le foglie. Due grafie sono molto diffuse. I percorsi con punti scrivono un indice di array come qualsiasi altra chiave: tags.0, lines.1.sku. Gli indici tra parentesi quadre distinguono i due tipi di passo: tags[0], lines[1].sku. L'appiattitore JSON dedicato di questo sito offre entrambe, più una scelta di separatore — punto, trattino basso o barra — perché un trattino basso sopravvive al passaggio in sistemi che trattano il punto come operatore di percorso, e una barra riprende la sintassi di puntatore già nota da JSON Pointer. La modalità appiattisci del convertitore da JSON a CSV usa sempre il punto per entrambi, la più compatta delle due grafie e quella che i fogli di calcolo hanno meno probabilità di rovinare.

Nella grafia con i punti si nasconde un guasto vero, e conviene dirlo chiaramente perché costa dati. Un percorso con punti è ambiguo: la colonna customer.name può indicare la chiave name dentro l'oggetto customer, oppure una chiave di primo livello il cui nome letterale è customer.name. JSON permette entrambe, nello stesso oggetto. Dai alla modalità appiattisci un record che contiene una chiave letterale a.b con valore 1 accanto a un oggetto annidato a la cui chiave b vale 2, e l'output ha una sola colonna, a.b, contenente 2. Il primo valore è sparito, senza avviso e senza una seconda colonna. È raro, ma non è ipotetico: chiavi con punti compaiono nei campi di log, nei nomi di eventi di analytics e in tutto ciò che deriva da un identificatore con spazio dei nomi.

La difesa è la scelta del separatore. Appiattisci con un trattino basso o una barra invece del punto e la collisione richiede una chiave che contenga letteralmente quel carattere, cosa molto meno probabile. Se non puoi scegliere il separatore — e nella modalità appiattisci del convertitore CSV non puoi — controlla i punti nelle tue chiavi prima di appiattire, non dopo.

Gli array: una colonna per elemento, una sola cella, o una riga per elemento

Un array di scalari ha tre risposte sensate. Serializzarlo: la cella tags diventa i caratteri ["vip","eu"]. Dare una colonna a ogni elemento: tags.0 e tags.1. Oppure unire gli elementi con un separatore che in essi non compare, così la cella tags legge vip|eu e una formula di foglio di calcolo può rispezzarla. I convertitori qui fanno le prime due e nessuno fa la terza: se vuoi una stringa unita, devi produrla prima della conversione. La forma unita è la più leggibile e l'unica il cui numero di colonne non cambia quando cambiano i dati, ed è per questo che tante esportazioni la usano nonostante sia la meno definita.

Un array di oggetti è il punto in cui strumenti e database si separano. Con lines a due voci, la modalità appiattisci allarga la tabella: lines.0.sku, lines.0.qty, lines.1.sku, lines.1.qty. Chi viene dai database si aspetterebbe il contrario — una riga di output per articolo, con i campi dell'ordine ripetuti sul blocco, che è ciò che produce una join e ciò che vuole una tabella pivot. Nessuno dei convertitori lo fa, e la differenza non è estetica. Allargare tiene una riga per ordine: un conteggio di righe è un conteggio di ordini. Esplodere dà una riga per articolo: un conteggio di righe è un conteggio di articoli e i campi dell'ordine sono duplicati. Entrambi si usano davvero; solo uno risponde alla domanda quanti ordini abbiamo spedito.

Vale la pena prevedere una conseguenza dell'allargamento: la disposizione delle colonne è fissata dall'array più grande in tutto il file, e cambia quando cambiano i dati. Due record i cui array di tag hanno uno e tre elementi producono le colonne t.0, t.1 e t.2, con due celle vuote sul record corto. Esporta domani la stessa query con un record da quattro tag e il file guadagna una colonna, in silenzio. Tutto ciò che a valle legge le colonne per posizione e non per nome si rompe quel giorno, e l'esportazione che l'ha rotto è identica alla precedente.

Record che non concordano sulle proprie chiavi

JSON non ha schema, quindi un array di oggetti non è una tabella finché non la costruisci. Entrambi i convertitori prendono l'unione di tutte le chiavi che vedono e lasciano un buco dove un record non ce l'ha. Tre record con {id, a}, {id, b} e {id, a, c} danno quattro colonne — id, a, b, c — con celle vuote dove ciascuno tace. È l'unica risposta che non perde nulla, ed è per questo che un CSV esportato da un archivio documentale è di solito molto più largo di qualsiasi suo singolo documento.

L'ordine delle colonne non è ordinato né stabile fra esportazioni. Entrambi i convertitori usano l'ordine di prima comparsa: le chiavi compaiono nell'ordine in cui le presenta il primo record che le contiene. Due record {b, a} e {a, b} producono le colonne b e poi a, perché il primo è stato letto per primo. Cambia l'ordinamento della tua query e l'ordine delle colonne cambia con esso, benché i dati siano identici. Se a valle qualcosa dipende dall'ordine delle colonne, ordina tu le chiavi prima di esportare.

Una cosa si perde qualunque strategia tu scelga, e la colpa è del CSV, non del convertitore. Una chiave a null, una chiave con la stringa vuota e una chiave semplicemente assente diventano tutte la stessa cella vuota. Rileggi quel CSV e ciascuna torna come stringa vuota. Se la distinzione conta — e in un aggiornamento parziale o in una colonna annullabile conta sempre — il CSV è il formato sbagliato per quel campo, e nessuna opzione di appiattimento lo salverà.

Che cosa rileva il lato CSV e che cosa no

Parti dal verso che imbarazzava questo convertitore. Dai al convertitore CSV / JSON / YAML il JSON [1,2,3] e restituisce value / 1 / 2 / 3: una sola colonna sintetizzata, perché un numero non ha una chiave propria da cui ricavare un nome di colonna e value è l'unica cosa onesta da chiamarla. Uno scalare di primo livello, 42, dà value / 42. Un array misto, [1,{"a":2}], dà l'intestazione value,a e due righe — 1 seguito da una cella vuota, poi una cella vuota seguita da 2 —: lo scalare nella colonna inventata, l'oggetto nella sua. È la stessa risposta del convertitore gemello JSON a CSV, quindi i due strumenti del sito ora concordano sul caso che più spesso arriva da un'API che restituisce un semplice elenco di identificatori.

In lettura, il convertitore ora fiuta il proprio delimitatore invece di dare per scontata la virgola: conta le virgole contro i punti e virgola sul primo record, ignorando ciò che sta tra virgolette, quindi name;city su Emma;Paris arriva come {"name":"Emma","city":"Paris"} — cosa che conta, perché Excel scrive CSV con il punto e virgola per impostazione predefinita in cinque dei sei mercati di questo sito. I nomi di colonna ripetuti vengono rinominati anziché scartati: name,name,name su a,b,c restituisce name, name_2 e name_3. Una riga che sfora l'intestazione tiene la cella in più sotto un nome inventato: a,b su 1,2,3 restituisce a, b e column3. Ciò che ancora non fa è valutare un tab o una barra verticale. Lo strumento dedicato CSV a JSON pesa quattro candidati e ti lascia imporne uno; questo convertitore ne pesa due e non ha alcun controllo del delimitatore nell'interfaccia, perciò un file separato da tabulazioni arriva ancora come una sola colonna la cui chiave è l'intera riga di intestazione. E qualunque cosa entri, il CSV che riscrive è separato da virgole.

Domande frequenti

Devo serializzare i valori annidati o appiattirli?

Chiediti chi apre il file. Se la risposta è un programma che lo analizzerà di nuovo, serializza: la forma del record è preservata identica e l'andata e ritorno restituisce ciò che è entrato. Se la risposta è una persona in un foglio di calcolo, appiattisci: deve ordinare per customer.city e sommare lines.0.qty, e con un blocco JSON in una cella non può fare né l'una né l'altra. Se la risposta è entrambe, produci due file invece di un compromesso, perché il compromesso — appiattire un livello e serializzare il resto — è la versione che nessuno saprà interpretare sei mesi dopo. E se il file è un archivio e non un report, serializza: appiattire fissa le lunghezze di array di oggi nella disposizione delle colonne, e l'esportazione del mese prossimo non combacerà.

Posso ottenere una riga per articolo invece di colonne in più?

Da nessuno dei due convertitori: entrambi allargano la tabella e nessuno esplode un array in righe. Il motivo è che esplodere non è una scelta di formattazione ma un cambio di grana: il file risultante risponde a un'altra domanda, e il convertitore dovrebbe decidere quale array esplodere quando un record ne contiene due. Fallo prima della conversione, in ciò che produce il JSON: emetti un oggetto per articolo, ciascuno con i campi dell'ordine che gli servono. La questione dell'appiattimento sparisce allora, perché l'array non c'è più. Se hai solo il JSON, un breve script che associa ogni ordine ai suoi articoli e concatena i risultati è un lavoro da cinque righe e lascia la decisione visibile nel tuo codice invece che sepolta nei default di uno strumento.

Perché il mio file ha guadagnato una colonna fra due esportazioni della stessa query?

Perché la disposizione delle colonne di un'esportazione appiattita è una proprietà dei dati, non della query. Le colonne sono l'unione di tutti i percorsi presenti, e i percorsi di array sono numerati fino alla lunghezza dell'array più lungo nel risultato. Un record con quattro tag, dove la volta prima ce n'erano al massimo tre, aggiunge tags.3 a tutte le righe. Lo stesso meccanismo aggiunge una colonna quando un solo record contiene una chiave facoltativa che nessuno aveva usato prima. Due difese: leggere le colonne per nome e non per posizione ovunque a valle, e, se una disposizione stabile conta davvero, definire esplicitamente l'elenco delle colonne e proiettare su di esso, invece di lasciare che l'esportatore lo deduca da ciò che si trovava nel risultato.

Posso riportare il CSV appiattito al JSON originale?

In parte, e le lacune sono prevedibili. L'appiattitore JSON ha una modalità ricostruisci che rifà l'annidamento dalle chiavi-percorso: un segmento che è un semplice numero costruisce un array, qualsiasi altro costruisce un oggetto, quindi customer.name e tags.0 tornano come oggetto e array. Tre cose non tornano. I tipi sono spariti, perché ogni cella di un CSV è testo: un numero scritto 1 torna come la stringa "1" a meno che tu non lo converta. La distinzione fra null, stringa vuota e chiave assente è sparita, come visto sopra. E un array vuoto o un oggetto vuoto non lascia alcun percorso in un appiattimento con punti, quindi non è ricostruibile; l'appiattitore scrive un [] o un {} visibile proprio per questo, ma solo se hai appiattito con quello strumento. Il giro di andata e ritorno con la strategia di serializzazione non perde nulla di tutto ciò, ed è tutto il suo argomento.

Il mio CSV usa i punti e virgola. Devo convertirlo prima?

No. Il convertitore CSV / JSON / YAML conta le virgole contro i punti e virgola sul primo record, fuori dai campi tra virgolette, e tiene il vincitore: un export di Excel francese, tedesco, spagnolo, italiano o portoghese si legge bene senza toccare nulla. Il caso che ancora fallisce è il tab o la barra verticale: nessuno dei due è tra i candidati che valuta, quindi un file separato da tabulazioni dà un solo campo per riga, la cui chiave è l'intera riga di intestazione. Il sintomo è inconfondibile una volta che lo conosci: una sola chiave con dei tab nel nome. Due vie d'uscita. Converti prima il delimitatore con il convertitore di delimitatori, che analizza come si deve e rimette le virgolette dove servono. Oppure usa lo strumento dedicato CSV a JSON, che pesa quattro candidati e ti lascia imporne uno. Nota anche che, qualunque cosa entri, il CSV che questo convertitore riscrive è separato da virgole.