Da JSON a SQL: istruzioni INSERT, progettazione delle tabelle e trappole nella mappatura dei tipi

In sintesi: decidi quale entità di business rappresenta una riga prima di progettare le tabelle, poi scrivi esplicitamente VALUES su più righe dentro un'unica transazione; fissa la denominazione dei campi e la mappatura dei tipi prima dell'importazione invece di rattopparle in SQL.

JSON descrive la forma; SQL descrive anche i vincoli. Quando sposti la prima nel secondo, ogni vincolo che non hai messo per iscritto torna più tardi sotto forma di dati sporchi.

1. Decidi prima: una tabella o un record per riga JSON

Guarda lo strato più esterno dei dati prima di progettare qualsiasi tabella. Sbaglia questa decisione e ogni INSERT successivo dovrà essere riscritto.

C'è un solo criterio: quale entità di business rappresenta una riga? Se una riga è un ordine, le righe di dettaglio devono vivere in una propria tabella. Se una riga è un utente, i tag appartengono a una tabella figlia o a una colonna JSON, non a una stringa unita con virgole.

2. Da un array a INSERT: il più piccolo modello eseguibile

La forma più sicura elenca sempre esplicitamente i nomi delle colonne e non si affida mai all'ordine delle colonne:

复制CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

INSERT INTO users (id, name, email) VALUES
  (1, 'Ada', 'ada@example.com'),
  (2, 'Linus', NULL);

Tre dettagli contano:

• Scrivi il nome di ogni colonna. Quando lo schema guadagnerà una colonna, nulla finirà nel campo sbagliato.
• VALUES su più righe funziona in SQLite, PostgreSQL e MySQL, e rispetto a un ciclo di inserimenti singoli risparmia un numero enorme di round trip.
• NULL è una parola chiave; non racchiuderla tra virgolette. Scrivere 'NULL' memorizza una stringa di quattro caratteri.

Se le chiavi JSON non corrispondono ai nomi delle colonne (userId contro user_id), aggiungi uno strato di mappatura. Non indovinare con COALESCE dentro l'SQL. Correggere i nomi dei campi nel livello dati costa molto meno che rattopparli nelle query.

3. Oggetti annidati: una tabella padre più una tabella figlia

Gli ordini sono il classico caso uno-a-molti:

复制INSERT INTO orders (id, customer_name, total_cents) VALUES
  (1001, 'Ada', 12900);

INSERT INTO order_items (order_id, sku, quantity) VALUES
  (1001, 'A-1', 2),
  (1001, 'B-7', 1);

Questo corrisponde al JSON {"id":1001,"customer":"Ada","total":129.00,"items":[{"sku":"A-1","quantity":2},{"sku":"B-7","quantity":1}]}.

Punti chiave:

• La lunghezza dell'array items determina il numero di righe figlie; la tabella padre riceve esattamente una riga.
• Memorizza il denaro come centesimi interi (total_cents), mai come FLOAT.
• Dai alla tabella figlia una chiave esterna order_id e conferma entrambi gli inserimenti in un'unica transazione, altrimenti il padre può esistere mentre i suoi elementi mancano.
• Se i dati sono di sola lettura e nessuno filtra per riga di dettaglio, una colonna JSON è accettabile (jsonb di PostgreSQL, JSON di MySQL, oppure TEXT in SQLite con le funzioni json1), ma accetta il costo: niente indici e query scomode.

Appiattisci in colonne gli oggetti annidati fissi come user.address (address_city, address_zip). Separa in una tabella solo quando l'annidamento può continuare a crescere; separare una forma fissa aggiunge solo JOIN.

4. NULL vs campi mancanti: SQL non ha "chiave assente"

JSON consente che una chiave sia del tutto assente. Le colonne SQL sono fisse. Dopo un'importazione entrambi i casi appaiono identici — NULL — anche se possono significare cose diverse:

Due errori comuni:

1. Usare una stringa vuota invece di NULL. Più tardi WHERE email IS NULL manca quelle righe, e la metrica "utenti senza email" sottostima in silenzio.
2. Verificare con col = NULL. Nella logica a tre valori di SQL, NULL = NULL restituisce NULL, non TRUE. Devi scrivere IS NULL:

复制SELECT COUNT(*) FROM users WHERE email IS NULL;

Se devi preservare se una chiave era presente, aggiungi una colonna marcatore booleana invece di dedurlo da NULL. Anche il contratto dell'API dovrebbe dichiararlo; vedi Progettazione di API JSON e denominazione dei campi.

5. Quoting ed escape: non concatenare mai stringhe SQL

Le stringhe JSON possono contenere apici singoli, virgolette doppie, barre rovesciate, a capo e perfino \u0000. Con stringhe costruite a mano, un escape mancante sposta il resto dell'istruzione e, nel caso peggiore, diventa SQL injection.

In SQL standard, un apice singolo si sottopone a escape raddoppiandolo:

复制INSERT INTO notes (id, content) VALUES (1, 'It''s a ''quoted'' value');

Il codice di produzione dovrebbe usare il binding dei parametri e lasciare al driver la gestione di escape e tipi:

复制// PostgreSQL uses $1/$2, MySQL uses ?; this example uses node-postgres
await client.query('INSERT INTO users (id, name) VALUES ($1, $2)', [row.id, row.name]);

Sottoponi a escape a mano solo quando devi esportare un file .sql, e anche allora come minimo:

• sostituisci ' con '';
• se conservi gli a capo, conferma che il database di destinazione accetti letterali di stringa multiriga;
• elimina \u0000, dato che la maggior parte dei database rifiuta il NUL dentro le stringhe;
• MySQL tratta la barra rovesciata come carattere di escape per impostazione predefinita, quindi \ deve diventare \\, oppure passa alla modalità standard.

Le virgolette doppie di JSON non necessitano di escape dentro una stringa SQL tra apici singoli. Aggiungere uno strato extra di escape è il modo in cui finiscono barre rovesciate spurie nei tuoi dati.

6. Mappatura dei tipi: dove finiscono i sei valori JSON in SQL

JSON ha solo stringa, numero, booleano, null, oggetto e array. SQL ha un sistema di tipi molto più fine. Gli errori di solito avvengono nella precisione e nella semantica più che nella sintassi:

I booleani nascondono il bug più sottile: il false JSON diventa 0 in TINYINT(1) di MySQL, il round trip sembra a posto e nessuno se ne accorge. Ma nel momento in cui un codice fa if (value) sulla stringa "false", questa vale come vero. Gli script di conversione dovrebbero chiamare esplicitamente Boolean(value).

JSONB di PostgreSQL analizza e deduplica le chiavi, conservando l'ultima occorrenza, il che si presta alle query; JSON preserva il testo originale e l'ordine delle chiavi. Il JSON di SQLite è solo TEXT più una serie di funzioni, senza alcun vincolo di tipo reale. Prima di scegliere, chiediti se questa tabella deve filtrare sui campi interni al JSON.

7. Inserimenti massivi e transazioni: raggruppa, conferma, rendi rieseguibile

Man mano che il volume cresce, tre cose decidono se un'importazione resta controllabile:

1. Raggruppa. Un ciclo di N istruzioni INSERT singole costa N round trip di rete. Combina da 500 a 1000 righe in un unico VALUES multi-riga, oppure usa il caricatore massivo nativo del database (COPY di PostgreSQL, LOAD DATA di MySQL, inserimenti multi-riga dentro una transazione SQLite).
2. Usa una transazione. Tutto o niente, così una tabella caricata a metà non sopravvive mai a un errore:

复制BEGIN;
INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Linus');
INSERT INTO order_items (order_id, sku, quantity) VALUES (1, 'A-1', 2);
COMMIT;

3. Rendila idempotente. Gli script di importazione vengono rieseguiti continuamente. Usa un upsert invece di delete-then-insert:

ON CONFLICT richiede un vincolo di unicità o una chiave primaria, altrimenti non si attiva mai. Quando non esiste una chiave naturale, costruisci un indice unico su una chiave di business come email. Quando non esiste alcuna chiave, aggiungi un id stabile sul lato JSON — non affidarti mai a un id auto-incrementale per l'idempotenza, perché una riesecuzione inserirà dei duplicati.

8. Le differenze tra dialetti in sintesi

Per mantenere un unico file SQL funzionante su tutti e tre, evita almeno ON CONFLICT, i backtick e SERIAL. Per i progetti multi-database, genera separatamente schema e istruzioni di upsert; non aspettarti che un unico script vada bene per tutti.

9. Tre controlli a campione dopo l'importazione

Prima di eliminare il file sorgente, verifica tre cose:

1. Numero di righe. SELECT COUNT(*) FROM users; dovrebbe essere uguale alla lunghezza dell'array JSON. Una riga mancante di solito significa che l'ultimo record è stato troncato.
2. La colonna con il più alto tasso di NULL. Indica nomi di chiave incoerenti o annidamenti non espansi. Torna indietro e controlla se la sorgente usa sia userId sia user_id.
3. Denaro e identificatori lunghi. Prendi i valori massimo e minimo e conferma che né la conversione in virgola mobile né quella in intero li abbiano troncati.

Se devi prima capire la forma tabellare, usa l'approccio di appiattimento in Conversione di JSON, CSV e dati tabellari. Se il pubblico sono colleghi che vogliono solo leggere i dati, i quattro modi per convertire JSON in Excel richiedono meno lavoro che mettere in piedi un database.

Perché i numeri perdono precisione dopo aver importato JSON in SQL?

Gli interi oltre 2^53 hanno già perso precisione durante JSON.parse di JavaScript, e scriverli in BIGINT non può ripristinarla. Tali id dovrebbero essere stringhe in JSON e convertiti in BIGINT o TEXT durante l'importazione.

Cosa devo usare come chiave primaria quando il JSON non ha un id?

Lascia che il database ne generi una, oppure crea un UUID stabile prima di importare. Non usare mai l'indice dell'array come id: una riesecuzione riordina i dati e punta le righe sbagliate ai record sbagliati. Un indice unico su una chiave di business (email, numero d'ordine) consente anche gli upsert.

Cosa faccio quando un INSERT massivo incontra un conflitto di chiave unica?

Sostituisci il semplice INSERT con l'upsert corrispondente al tuo dialetto e conferma che la colonna in conflitto abbia un vincolo di unicità. Eliminare prima e inserire dopo funziona ugualmente, ma deve avvenire dentro un'unica transazione e accetti una breve finestra senza righe.

Gli array annidati richiedono sempre una tabella figlia?

No. Per la visualizzazione di sola lettura senza query per sotto-elemento, una colonna JSON è più semplice. Non appena devi filtrare o aggregare per tag o SKU, una tabella figlia con indici è la scelta sostenibile. Il fattore decisivo è come interroghi, non come sono fatti i dati.