Convertire JSON in SQL

L’output è una serie di istruzioni INSERT e nient’altro. È un limite deliberato invece di una funzionalità mancante. JSON dice che un valore è un numero; non dice se la colonna è un intero o un decimale con due posti, se è nullable, quale campo è la chiave primaria, quali sono le chiavi esterne, o quanto può essere lungo il testo. Quelle sono le decisioni che uno schema esiste per registrare, e un convertitore che le indovinasse produrrebbe un CREATE TABLE che andrebbe letto riga per riga prima di fidarsi, che è più lavoro che scriverlo.

Ciò che significa in pratica è che la tabella esiste già prima di eseguire il file. Convertire una volta, leggere l’elenco delle colonne dalla prima istruzione, scrivere il DDL perché corrisponda, poi eseguire. È un ciclo di due minuti e mette le decisioni di schema dove devono stare, in una migrazione che si può rivedere, invece di in un file generato che per caso funziona sul primo campione, ed è il ciclo che fa risparmiare debug dopo.

Non c’è altro posto da cui potrebbe venire. Un documento JSON non ha un nome per sé, quindi lo fornisce il file caricato: ordini.json produce INSERT INTO ordini, ed esportazione-2024.json produce INSERT INTO esportazione_2024, perché un trattino non è legale in un identificatore non quotato e una cifra iniziale non lo è.

La conseguenza è che rinominare il file prima di convertire è il modo più economico per controllare l’output. Farlo dopo significa una ricerca e sostituzione attraverso ogni istruzione nel file, e la sostituzione deve stare attenta a non colpire un valore che contiene la stessa parola. Trentina secondi in un file browser battono un’espressione regolare su centomila righe, ed è il tipo di scelta che si fa una volta e si dimentica.

Una tabella relazionale è piatta e JSON non lo è, quindi un oggetto annidato è appiattito nel nome della colonna: un record con un oggetto cliente che contiene una città produce una colonna chiamata cliente_città. Il percorso è conservato, il punto diventa un underscore perché un punto non è legale in un identificatore, e nulla è scartato.

Un livello di annidamento produce una tabella che chiunque sarebbe felice di dichiarare. Due è di solito ancora ragionevole. Oltre, l’elenco di colonne generato è un segnale invece di un risultato: una struttura JSON a quattro livelli sta descrivendo relazioni, e lo schema che le si adatta sono diverse tabelle con chiavi fra loro. Caricare tutto in una tabella larga funziona e rende ogni query successiva più difficile di quanto dovesse essere, ed è il tipo di scelta di design che si paga per anni.

Un record con tre tag produce tag_0, tag_1 e tag_2. Ogni valore sopravvive e la forma è sbagliata in un modo che peggiora: il record successivo con cinque tag allarga la tabella di altre due colonne, e non c’è una query sensata su «il secondo tag».

Un database relazionale ha una risposta normale a questo, ed è una seconda tabella, una riga per tag, con l’identificativo del genitore accanto. Arrivarci da un’esportazione JSON significa convertire due volte con l’array estratto nel mezzo, o caricare la tabella larga come passaggio di staging e normalizzare con una query. Entrambe sono più lavoro di una singola conversione, ed entrambe producono uno schema che si può effettivamente interrogare. Se il database di destinazione ha un tipo di colonna JSON nativo, memorizzare l’array in una colonna è la terza opzione, ed è quella adatta quando l’array è trasportato invece che interrogato.

I record JSON in un’esportazione non sono obbligati ad essere d’accordo. Un’API omette i campi che non hanno valore, quindi mille record possono presentare una dozzina di insiemi di chiavi diversi. La conversione li riconcilia raccogliendo l’unione di ogni chiave nel file e scrivendo NULL dove un record non ha nulla.

Quell’uniformità è ciò che rende l’output sicuro da eseguire come batch: l’elenco delle colonne è identico in ogni istruzione, quindi una tabella che accetta la prima riga le accetta tutte. Spiega anche una differenza che si nota, convertire dieci record per un test produce meno colonne che convertendo l’intero file, perché il campione non conteneva i campi più rari. Scrivere il DDL da una conversione dell’intero file, mai da un campione, ed è una regola che vale la pena scrivere nel runbook del progetto.

I numeri sono scritti nudi, i booleani come TRUE e FALSE, un null JSON come NULL, e tutto il resto come stringa tra virgolette singole con le virgolette singole interne raddoppiate. Raddoppiare è la forma SQL standard ed è ciò che ogni motore capisce, quindi un cognome come O’Brien si carica correttamente ovunque.

I backslash sono scritti direttamente, il che è corretto sotto lo standard e non è come MySQL legge una stringa per impostazione predefinita: lì un backslash inizia una sequenza di escape a meno che NO_BACKSLASH_ESCAPES non sia impostato. Dati che contengono percorsi Windows, espressioni regolari o LaTeX verranno quindi caricati diversamente in MySQL rispetto a Postgres. Se questo descrive l’esportazione, impostare la modalità per la sessione prima di eseguire il file, e controllare una riga coinvolta dopo invece di presumere, ed è il tipo di controllo che costa poco e previene grattacapi grossi.

Questo è dove una sorgente JSON è migliore di una delimitata. Un codice prodotto «007» è una stringa JSON, quindi viene scritto come una stringa SQL quotata e arriva nel database con i suoi zeri iniziali. Un CSV degli stessi dati non ha tipi affatto, e ogni strumento nella catena ha la possibilità di decidere che 007 è il numero sette.

La stessa protezione copre gli identificativi numerici lunghi che sono stati esportati come stringhe. Dove sono stati esportati come numeri JSON, però, vale il solito tetto in virgola mobile: qualsiasi cosa oltre le sedici cifre ha già perso precisione prima che questa conversione veda il file, e nessuna attenzione da parte dello scrittore può rimetterla. Se l’esportazione è nostra da modellare, esportare gli identificativi come stringhe è la soluzione, ed è una soluzione alla fonte invece che qui, ed è una distinzione che fa risparmiare ore di debug più tardi.

L’output è un’istruzione per record senza wrapper di transazione e senza VALUES multi-riga. Eseguire cinquantamila istruzioni separate attraverso un client è lento, perché ognuna è un round trip. Racchiudere l’intero file in BEGIN e COMMIT è una riga a ciascuna estremità e di solito il più grande miglioramento singolo disponibile.

Le istruzioni stesse sono deliberatamente semplici e portabili: nessuna virgoletta specifica del motore, nessuna clausola ON CONFLICT, nessun prefisso di schema. Aggiungere ciò che il motore vuole è una ricerca e sostituzione su INSERT INTO, un nome di schema, un ON CONFLICT DO NOTHING per un carico rieseguibile, e partire dalla forma più semplice possibile è ciò che rende quelle modifiche prevedibili, ed è il tipo di base che rende il file riusabile invece che usa e getta.

Per un file di seed, un fixture, o qualche migliaio di righe, le istruzioni sono la forma comoda: sono leggibili, si possono committare in un repository, ed eseguono ovunque un client possa connettersi. È il lavoro per cui questa pagina è scritta, e vale la pena dirlo perché il confine si attraversa in fretta.

Oltre una certa dimensione l’aritmetica cambia. Ogni motore ha un percorso di massa, COPY in Postgres, LOAD DATA in MySQL, un import in uno strumento client, che legge un file delimitato molto più velocemente di quanto esegua insert individuali, e per un carico di un milione di righe la differenza è minuti contro ore. Convertire la stessa esportazione in CSV o TSV in quel caso, o in NDJSON se la destinazione è un warehouse invece di un database relazionale, ed è il calcolo che vale la pena fare prima di impegnare un’ora in INSERT che il database non apprezza.

Le istruzioni sono generate da JavaScript in questa scheda. Niente viene caricato, non c’è account o coda, e il piano gratuito accetta fino a 100 MB, con la memoria come vero tetto perché l’intero documento è analizzato prima che qualcosa sia scritto.

Questo non è un beneficio incidentale per questa coppia. Qualunque cosa stia per essere inserita in un database è, per definizione, un dato che qualcuno ha deciso di conservare, clienti, ordini, transazioni, account. Passarlo attraverso un convertitore di terze parti per vedersi raddoppiare le virgolette sarebbe uno scambio mediocre, e qui non c’è scambio da fare, ed è una di quelle pagine in cui il dettaglio tecnico coincide con il principio.