La maggior parte del JSON che mi occupo inizia la vita come risposta API e deve finire in un database Sul backend Toolz.dev, che funziona su Express e TypeORM su PostgreSQL, ho perso il conto delle volte in cui ho avuto un'esportazione JSON da un servizio che volevo caricare in una tabella per una query rapida, un seme di migrazione o una riproduzione locale di un bug Farlo a mano è cupo: scrivi un CREATE TABLEpoi digiti a mano INSERT affermazioni, citando ogni stringa, raddoppiando ogni apostrofo, ricordando quali campi sono numeri e trasformando le chiavi mancanti in NULL. Sbagliare una riga su duecento e l'intero lotto non viene eseguito.
Un convertitore da JSON a SQL fa quel lavoro meccanico per te Incolli una serie di oggetti, scegli un dialetto del database e produce un CREATE TABLE con tipi di colonne sensate più il INSERT dichiarazioni da caricare ogni riga Questa guida spiega come funziona la conversione, le decisioni che prende sui tipi e sulle citazioni e le situazioni reali in cui si salva un pomeriggio.
tl; dr: la Convertitore da JSON a SQL trasforma un array JSON di oggetti in
CREATE TABLEeINSERTistruzioni per MySQL, PostgreSQL, SQLite o SQL standard Legge le chiavi degli oggetti come colonne, deduce ogni tipo di colonna dai dati, sfugge alle stringhe, trasforma le chiavi mancanti e i nulli inNULL, e memorizza gli oggetti nidificati come JSON Tutto viene eseguito nel browser, quindi una risposta API con dati reali non lascia mai la macchina Per il contrario o per le fonti tabulari, vedere CSV a SQL e JSON a CSV.
Che cosa fa un convertitore da JSON a SQL?
Dati del modello JSON e SQL in modi fondamentalmente diversi JSON è un albero di oggetti e array nidificati con tipi dinamici, per valore Una tabella relazionale è una griglia piatta di colonne fisse e tipizzate La conversione dall'una all'altra è una serie di piccole decisioni e il valore dello strumento è prendere tali decisioni in modo coerente.
Legge le chiavi dei tuoi oggetti come set di colonne, tratta ogni oggetto come una riga, sceglie un tipo SQL per ogni colonna e quindi scrive le istruzioni L'output ha due parti: un optional CREATE TABLE ciò dichiara le colonne con i tipi dedotti e il INSERT istruzioni che portano i valori È possibile incollare il risultato in un client di database, salvarlo come parte di una migrazione o inserirlo nel pipe mysql, psql, o sqlite3 dalla riga di comando.
L'input è normalmente una serie di oggetti, come [{ "id": 1, "name": "Ada" }, { "id": 2, "name": "Grace" }]. Anche un singolo oggetto funziona e diventa una riga Ciò che non accetterà è una serie di valori nudi come [1, 2, 3], perché non ci sono chiavi da trasformare in colonne, e questo ti dice così piuttosto che indovinare.
Come si deducono i tipi di colonna?
Questa è la parte che separa un convertitore utile da uno che scarica tutto in colonne di testo Per ogni colonna, lo strumento scansiona il valore in ogni riga e sceglie il tipo più stretto che si adatta a tutte.
Se ogni valore attuale in una colonna è un numero intero, la colonna diventa di tipo intero Se i valori sono numeri ma alcuni hanno decimali, diventa di tipo in virgola mobile Se ogni valore è true oppure falsediventa booleano Se ogni valore è un oggetto o un array nidificato, diventa un tipo JSON Tutto il resto, inclusa una colonna in cui i valori non sono d'accordo, torna al testo quindi il INSERT non può fallire in caso di mancata corrispondenza dei tipi.
null e i tasti mancanti vengono saltati quando si decide il tipo; rendono la colonna annullabile solo in pratica Ciò significa una colonna che contiene numeri nella maggior parte delle righe e null in pochi è ancora digitato come numero, che è ciò che vuoi.
Il nome del tipo concreto dipende dal dialetto Ecco come si mappano i tipi neutri:
| Tipo dedotto | MYSQL | Postgresql | SQLite | SQL standard |
|---|---|---|---|---|
| Intera | INT |
INTEGER |
INTEGER |
INTEGER |
| decimale | DOUBLE |
DOUBLE PRECISION |
REAL |
REAL |
| Booleano | TINYINT(1) |
BOOLEAN |
INTEGER |
BOOLEAN |
| JSON nidificato | JSON |
JSONB |
TEXT |
TEXT |
| Testo | VARCHAR(255) |
TEXT |
TEXT |
TEXT |
È possibile disattivare completamente l'inferenza del tipo, nel qual caso ogni colonna diventa testo. Occasionalmente è ciò che desideri quando carichi i dati in una tabella di stadiazione e prevedi di lanciarli in seguito.
Come gestisce le parti scomode dei dati reali?
Il vero JSON è raramente uniforme e la conversione deve farcela Lo strumento gestisce tre custodie che fanno scattare convertitori ingenui.
Innanzitutto, oggetti con chiavi diverse Se un oggetto ha un email campo e un altro no, il set di colonne è l'unione di ogni chiave che appare, mantenuta nell'ordine visto per la prima volta e qualsiasi oggetto a cui manca una chiave viene ottenuto NULL in quella colonna Lo strumento ti avvisa quando gli oggetti non sono uniformi quindi non sei sorpreso.
In secondo luogo, gli oggetti e gli array nidificati Le colonne SQL sono piatte, quindi un valore come { "street": "1 Main St", "city": "London" } non può diventare diverse colonne automaticamente senza che tu decida la forma Invece il convertitore serializza il valore annidato di nuovo al testo JSON e lo memorizza in una colonna JSON su MySQL, una colonna JSONB su PostgreSQL o una colonna di testo altrove La struttura rimane intatta e puoi interrogarlo in seguito con le tue funzioni JSON database's.
Terzo, tipi misti in una colonna Se un campo è un numero in un record e una stringa in un altro, forzare un tipo numerico farebbe fallire metà degli inserti Lo strumento rileva il disaccordo e digita la colonna come testo, quindi cita ogni valore come stringa letterale in modo che il batch funzioni in modo pulito.
Come vengono sfuggiti i valori?
L'escape corretto è la differenza tra uno script che viene eseguito e uno che genera un errore di sintassi sulla riga 47. le regole applicate dal convertitore sono quelle SQL standard.
I valori del testo sono racchiusi in virgolette singole e qualsiasi singola virgoletta all'interno di un valore viene raddoppiata, quindi O'Brien diventa 'O''Brien'. Questa è la stringa ANSI SQL che sfugge e funziona su tutti e quattro i dialetti Numeri e booleani sono scritti senza virgolette, perché un numero quotato è una stringa nella maggior parte dei motori I booleani rendono come TRUE e FALSE su PostgreSQL e SQL standard e come 1 e 0 su MySQL e SQLite, corrispondendo a come ciascun motore li memorizza. null e le chiavi mancanti diventano la parola chiave non citata NULL.
Gli identificatori, che significano i nomi delle tabelle e delle colonne, sono citati anche, con backtick su MySQL e virgolette doppie ovunque altro. Ciò mantiene una colonna denominata order oppure select dallo scontrarsi con una parola riservata.
Come utilizzare il convertitore da JSON a SQL
Passaggio 1: incolla il tuo JSON
Rilascia una serie di oggetti oppure premi Carica campione per vedere la forma prevista Ogni oggetto diventa una riga.
Passaggio 2: nomina la tabella e scegli un dialetto
Digitare il nome della tabella di destinazione e scegliere MySQL, PostgreSQL, SQLite o SQL standard Il dialetto controlla lo stile di citazione, i nomi dei tipi e il modo in cui vengono scritti i booleani e i JSON nidificati.
Passaggio 3: impostare le opzioni di output
Decidere se includere il CREATE TABLEse dedurre tipi di colonne e se emettere una riga multipla INSERT oppure un'istruzione per riga Un singolo inserto multiriga viene caricato più velocemente; un'istruzione per riga produce un file diff-friendly e consente di eseguire le righe singolarmente.
Passaggio 4: copiare l'SQL
Leggere le istruzioni generate e copiarle Incollare lo script nel client del database o eseguirlo dalla riga di comando per creare e caricare la tabella.
Casi d'uso comuni
Seeding di un database da una risposta API
Stai creando una funzionalità e hai bisogno di righe realistiche su cui lavorare Tira una risposta JSON dall'API upstream, incollalala e hai a CREATE TABLE e inserti pronti per seminare il tuo database locale Nessuna digitazione manuale, nessuna citazione persa.
Trasformare una configurazione o un'esportazione in una tabella
Un servizio esporta i suoi dati come array JSON Si desidera eseguire query SQL ad-hoc contro di esso piuttosto che scrivere JavaScript per filtrare l'array Convertirlo in SQL, caricarlo in SQLite e interrogarlo con il linguaggio creato esattamente per quello.
Costruire un seme di migrazione
Le migrazioni del framework spesso necessitano di un passo seed che inserisca i dati di riferimento Generazione del INSERT le istruzioni di un dispositivo JSON noto-buono ti offrono un blocco stabile e revisionabile da incollare nella migrazione, anziché tradurre i dati a mano.
Riproduzione di un bug di produzione a livello locale
Viene visualizzato un bug solo per un set specifico di record Prendi il JSON per quei record, convertilo in inserti, caricali nel tuo database locale e riproduci il problema senza copiare un database di produzione.
Inserto multi-riga vs per-riga: quale scegliere?
Un inserto multiriga raggruppa ogni riga in un'unica istruzione con molte VALUES tuple È compatto e si carica più velocemente, perché il database analizza e pianifica un'istruzione invece di centinaia Sceglila per il caricamento in blocco.
Un'istruzione per riga è più prolissa ma presenta due vantaggi Produce un file in cui ogni riga è la propria riga, che si legge in modo pulito in una revisione del codice e si differenzia bene nel controllo della versione E se una riga viola un vincolo, le altre riescono comunque, il che è utile quando si caricano dati disordinati e si desidera vedere quali righe falliscono anziché perdere l'intero lotto Sceglilo per i semi che commetterai o per i dati di cui non ti fidi ancora completamente.
Esecuzione dell'SQL generato dalla riga di comando
Una volta che hai lo script, caricarlo è un one-liner in ogni motore Per SQLite, salva l'output in un file e inseriscilo: sqlite3 mydata.db < insert.sql crea la tabella e carica le righe in un file di database fresco Per PostgreSQL, psql -d mydb -f insert.sql esegue l'intero script rispetto al database denominato Per MySQL, mysql -u user -p mydb < insert.sql fa lo stesso Poiché il generatore cita identificatori e sfugge ai valori per il dialetto scelto, lo script viene eseguito senza le correzioni manuali di cui SQL scritto a mano ha solitamente bisogno.
Se hai generato un'istruzione per riga anziché un singolo inserto multiriga, vale la pena di eseguire il wrapping del carico in una transazione Aggiunta BEGIN; in alto e COMMIT; in basso si trasformano centinaia di singoli inserti in un'unica operazione atomica, che è allo stesso tempo più veloce e più sicura: se una riga successiva fallisce un vincolo, l'intero carico torna indietro invece di lasciare la tabella semi popolata Per un inserto multiriga questo è già effettivamente atomico, poiché si tratta di un'unica affermazione.
Prestazioni e limiti con array di grandi dimensioni
La conversione stessa è lineare nella dimensione dell'input e viene eseguita interamente nel tuo browser, quindi alcune migliaia di righe si convertono istantaneamente I limiti pratici non sono nello strumento ma in quello che fai con l'output Un'unica multi-riga INSERT con decine di migliaia di tuple può superare un database's dimensione massima dell'istruzione o limite di pacchetto, MySQL's max_allowed_packet essendo quello che le persone colpiscono per prime Se stai caricando un array molto grande, o passa a un'istruzione per riga in modo che ognuna sia piccola, oppure dividi l'input in lotti di poche centinaia di righe e convertili separatamente.
C'è anche un avvertimento di precisione che vale la pena conoscere I numeri JSON vengono analizzati dal browser's motore JSON in float standard a doppia precisione, quindi un numero intero più grande di circa nove quadrilioni, oltre JavaScript's intervallo intero sicuro, può perdere precisione prima che lo strumento lo veda. Questa è una proprietà dell'analisi JSON in ogni ambiente, non del convertitore, ma significa che un id numerico gigante è più sicuro rappresentato come una stringa nel JSON sorgente, che lo strumento memorizzerà quindi come testo e conserverà esattamente.
Dove questo si adatta ad altri strumenti
JSON to SQL è una direzione di una piccola famiglia Quando il tuo sorgente è un foglio di calcolo o un'esportazione CSV anziché JSON, CSV a SQL fa lo stesso lavoro da input tabulare con lo stesso tipo di inferenza e fuga Quando hai bisogno dei dati come file flat invece di un database, JSON a CSV appiattisce l'array in righe e CSV a JSON torna indietro dall'altra parte Prima di convertire qualsiasi cosa, Formattatore JSON pretty-stamperà e convaliderà il JSON in modo da catturare una virgola vagante prima che diventi un errore di analisi. E una volta che hai l'SQL, Formattatore SQL indent e allineerà le istruzioni per un file di migrazione che sei orgoglioso di impegnare.
Per il ragionamento alla base di fare tutto questo nel browser piuttosto che caricare i dati su un servizio di conversione, il Privacy dei dati negli strumenti online la guida espone il modello lato client e il Toolkit per sviluppatori web raccoglie le utilità che si accoppiano bene con il lavoro di conversione dei dati.
FAQ
Come faccio a convertire JSON in SQL?
Incolla un array di oggetti JSON, imposta il nome della tabella e scegli il tuo dialetto SQL Lo strumento legge i tasti oggetto come nomi di colonna, deduce ogni tipo di colonna e genera una TABELLA CREA più istruzioni INSERISCI che puoi copiare ed eseguire nel tuo database.
Che forma JSON accetta?
Un array di oggetti è l'input normale, dove ogni oggetto è una riga Anche un singolo oggetto funziona e produce una riga Gli array di primitive o forme profondamente non corrispondenti vengono rifiutati con un errore che spiega cosa ci si aspetta.
Quali database SQL sono supportati?
Il convertitore emette istruzioni per MySQL, PostgreSQL, SQLite e SQL standard Il dialetto scelto controlla la citazione dell'identificatore, il tipo e la sintassi booleana e il tipo JSON nativo, quindi lo script viene eseguito in quel database senza modifiche.
Come vengono decisi i tipi di colonna?
Ogni colonna viene controllata rispetto a ogni oggetto Se tutti i valori presenti sono numeri interi diventa un tipo intero, tutti i numeri diventano un tipo decimale, tutti i valori veri o falsi diventano un tipo booleano, tutti gli oggetti o gli array diventano un tipo JSON e qualsiasi cosa mescolata diventa testo.
Cosa succede agli oggetti e agli array nidificati?
Vengono serializzati nuovamente su testo JSON e memorizzati in una colonna JSON su MySQL, una colonna JSONB su PostgreSQL o una colonna TEXT su SQLite e SQL standard Questo mantiene intatta la struttura in modo da poterla interrogare con le funzioni JSON del database in un secondo momento.
E se i miei oggetti hanno chiavi diverse?
Va bene Le colonne sono l'unione di ogni tasto che appare, mantenuto nell'ordine visto per la prima volta Qualsiasi oggetto mancante di uno di quei tasti ottiene NULL in quella colonna, e lo strumento ti avvisa quando gli oggetti non sono uniformi.
Come vengono sfuggiti stringhe e virgolette?
I valori del testo sono avvolti in virgolette singole e qualsiasi singola virgoletta all'interno di un valore viene raddoppiata, che è l'escape SQL standard Numeri e booleani vengono lasciati senza virgolette, null diventa NULL e gli identificatori vengono citati con backtick per MySQL o virgolette doppie per gli altri dialetti.
Il mio JSON è stato caricato da qualche parte?
No. Parsing e generazione SQL vengono eseguiti come JavaScript nel browser, Nulla viene trasmesso, registrato o memorizzato e lo strumento continua a funzionare offline una volta caricato, che è possibile confermare guardando la scheda di rete mentre si converte.
