JSON vers SQL : instructions INSERT, conception de tables et pièges de mapping de types

L'essentiel : décidez quelle entité métier représente une ligne avant de concevoir les tables, puis écrivez des VALUES multi-lignes explicites dans une seule transaction ; réglez le nommage des champs et le mapping de types avant l'import plutôt que de les corriger en SQL.

JSON décrit une forme ; SQL décrit aussi des contraintes. Quand vous faites passer la première dans le second, chaque contrainte que vous n'avez pas écrite revient plus tard sous forme de données sales.

1. Décidez d'abord : une table ou un enregistrement par ligne JSON

Regardez la couche la plus externe des données avant de concevoir quelque table que ce soit. Une mauvaise décision ici oblige à réécrire chaque INSERT ensuite.

Il n'y a qu'un critère : quelle entité métier représente une ligne ? Si une ligne est une commande, les lignes d'articles doivent vivre dans leur propre table. Si une ligne est un utilisateur, les tags appartiennent à une table enfant ou à une colonne JSON, pas à une chaîne jointe par des virgules.

2. D'un tableau à INSERT : le plus petit modèle exécutable

La forme la plus sûre liste toujours explicitement les noms de colonnes et ne s'appuie jamais sur l'ordre des colonnes :

复制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);

Trois détails comptent :

• Écrivez chaque nom de colonne. Quand le schéma gagne plus tard une colonne, rien ne se décale vers le mauvais champ.
• Le VALUES multi-lignes fonctionne dans SQLite, PostgreSQL et MySQL, et économise un grand nombre d'allers-retours par rapport à une boucle d'insertions mono-ligne.
• NULL est un mot-clé ; ne le mettez pas entre guillemets. Écrire 'NULL' stocke une chaîne de quatre caractères.

Si les clés JSON ne correspondent pas aux noms de colonnes (userId contre user_id), ajoutez une couche de mapping. Ne devinez pas avec COALESCE dans le SQL. Corriger les noms de champs dans la couche de données coûte bien moins cher que de les rafistoler dans les requêtes.

3. Objets imbriqués : une table parente plus une table enfant

Les commandes sont le cas un-à-plusieurs classique :

复制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);

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

Points clés :

• La longueur du tableau items détermine le nombre de lignes enfants ; la table parente reçoit exactement une ligne.
• Stockez l'argent en centimes entiers (total_cents), jamais en FLOAT.
• Donnez à la table enfant une clé étrangère order_id et committez les deux inserts dans une seule transaction, sinon le parent peut exister alors que ses articles manquent.
• Si les données sont en lecture seule et que personne ne filtre par ligne d'article, une colonne JSON est acceptable (PostgreSQL jsonb, MySQL JSON, ou SQLite TEXT avec les fonctions json1), mais acceptez le coût : pas d'indexation et requêtes malcommodes.

Aplatissez les objets imbriqués fixes comme user.address en colonnes (address_city, address_zip). Ne découpez en table que lorsque l'imbrication peut continuer à croître ; découper une forme fixe ne fait qu'ajouter des JOIN.

4. NULL contre champ absent : SQL n'a pas de « clé absente »

JSON permet qu'une clé soit entièrement absente. Les colonnes SQL sont fixes. Après un import, les deux cas sont identiques — NULL — même s'ils peuvent signifier des choses différentes :

Deux erreurs courantes :

1. Utiliser une chaîne vide au lieu de NULL. Plus tard, WHERE email IS NULL rate ces lignes, et votre métrique « utilisateurs sans e-mail » sous-compte silencieusement.
2. Tester avec col = NULL. Dans la logique à trois valeurs de SQL, NULL = NULL s'évalue à NULL, pas à TRUE. Il faut écrire IS NULL :

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

Si vous devez préserver le fait qu'une clé était présente ou non, ajoutez une colonne marqueur booléenne plutôt que de l'inférer depuis NULL. Le contrat d'API devrait aussi le préciser ; voir Conception d'API JSON et nommage des champs.

5. Quoting et échappement : ne concaténez jamais des chaînes SQL

Les chaînes JSON peuvent contenir des guillemets simples, des guillemets doubles, des barres obliques inverses, des retours à la ligne et même \u0000. Avec des chaînes construites à la main, un échappement oublié décale le reste de l'instruction, et au pire devient une injection SQL.

En SQL standard, un guillemet simple s'échappe en le doublant :

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

Le code de production devrait utiliser la liaison de paramètres et laisser le pilote gérer l'échappement et les types :

复制// 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]);

N'échappez à la main que lorsque vous devez exporter un fichier .sql, et alors au minimum :

• remplacez ' par '' ;
• si les retours à la ligne sont préservés, confirmez que la base cible accepte les littéraux de chaîne multi-lignes ;
• supprimez \u0000, car la plupart des bases rejettent le NUL dans les chaînes ;
• MySQL traite par défaut la barre oblique inverse comme un caractère d'échappement, donc \ doit devenir \\, ou passez en mode standard.

Les guillemets doubles de JSON n'ont pas besoin d'échappement à l'intérieur d'une chaîne SQL entre guillemets simples. Ajouter une couche d'échappement supplémentaire est exactement la façon dont des barres obliques inverses parasites finissent dans vos données.

6. Mapping de types : où atterrissent les six valeurs JSON en SQL

JSON n'a que chaîne, nombre, booléen, null, objet et tableau. SQL a un système de types bien plus fin. Les erreurs surviennent surtout sur la précision et la sémantique plutôt que sur la syntaxe :

Les booléens cachent le bug le plus subtil : false en JSON devient 0 dans MySQL TINYINT(1), les allers-retours semblent corrects, et personne ne remarque. Mais dès qu'un code fait if (value) sur la chaîne "false", elle est évaluée comme vraie. Les scripts de conversion devraient appeler explicitement Boolean(value).

PostgreSQL JSONB parse et déduplique les clés, en gardant la dernière occurrence, ce qui convient à l'interrogation ; JSON préserve le texte original et l'ordre des clés. SQLite JSON n'est que du TEXT plus un ensemble de fonctions, sans véritable contrainte de type. Avant de choisir, demandez si cette table doit filtrer sur des champs à l'intérieur du JSON.

7. Insertions en masse et transactions : lotir, committer, rendre réexécutable

À mesure que le volume croît, trois choses décident si un import reste maîtrisable :

1. Lotir. Une boucle de N INSERT mono-ligne coûte N allers-retours réseau. Combinez 500 à 1000 lignes en un seul VALUES multi-lignes, ou utilisez le chargeur en masse de la base (PostgreSQL COPY, MySQL LOAD DATA, insertions multi-lignes dans une transaction SQLite).
2. Utilisez une transaction. Tout ou rien, pour qu'une table à moitié chargée ne survive jamais à une erreur :

复制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. Rendez-le idempotent. Les scripts d'import sont réexécutés constamment. Utilisez un upsert au lieu d'un supprimer-puis-insérer :

ON CONFLICT exige une contrainte d'unicité ou une clé primaire, sinon il ne se déclenche jamais. Quand il n'y a pas de clé naturelle, construisez un index unique sur une clé métier comme email. Quand il n'y a aucune clé, ajoutez un identifiant stable côté JSON — ne comptez jamais sur un identifiant auto-incrémenté pour l'idempotence, car une réexécution insérera des doublons.

8. Différences de dialectes en un coup d'œil

Pour garder un seul fichier SQL fonctionnel sur les trois, évitez au moins ON CONFLICT, les accents graves et SERIAL. Pour les projets multi-bases, générez séparément le schéma et les instructions d'upsert ; n'attendez pas d'un seul script qu'il convienne à tous.

9. Trois vérifications par sondage après l'import

Avant de supprimer le fichier source, vérifiez trois choses :

1. Nombre de lignes. SELECT COUNT(*) FROM users; doit égaler la longueur du tableau JSON. Une ligne manquante signifie généralement que le dernier enregistrement a été tronqué.
2. La colonne au taux de NULL le plus élevé. Cela indique des noms de clés incohérents ou une imbrication non développée. Revenez vérifier si la source utilise userId et user_id.
3. Monnaie et longs identifiants. Prenez les valeurs maximale et minimale et confirmez que ni la conversion en flottant ni celle en entier ne les a tronquées.

Si vous devez d'abord comprendre la forme tabulaire, utilisez l'approche d'aplatissement de Conversion JSON, CSV et données tabulaires. Si le public est constitué de collègues qui veulent simplement lire les données, les quatre façons de convertir JSON en Excel demandent moins de travail que de monter une base de données.

Pourquoi les nombres perdent-ils de la précision après l'import de JSON en SQL ?

Les entiers au-delà de 2^53 ont déjà perdu de la précision pendant le JSON.parse de JavaScript, et les écrire dans BIGINT ne peut pas la restaurer. Ces identifiants devraient être des chaînes en JSON et être convertis en BIGINT ou TEXT pendant l'import.

Que faut-il utiliser comme clé primaire quand le JSON n'a pas d'id ?

Laissez la base en générer une, ou créez un UUID stable avant l'import. N'utilisez jamais l'index de tableau comme id : une réexécution réordonne les données et pointe les mauvaises lignes vers les mauvais enregistrements. Un index unique sur une clé métier (e-mail, numéro de commande) permet aussi les upserts.

Que faire quand un INSERT en masse heurte un conflit de clé unique ?

Remplacez le simple INSERT par l'upsert correspondant à votre dialecte, et confirmez que la colonne en conflit a une contrainte d'unicité. Supprimer d'abord puis insérer fonctionne aussi, mais cela doit se faire dans une seule transaction et vous acceptez une brève fenêtre sans lignes.

Les tableaux imbriqués exigent-ils toujours une table enfant ?

Non. Pour un affichage en lecture seule sans interrogation par sous-élément, une colonne JSON est plus simple. Dès qu'il faut filtrer ou agréger par tag ou SKU, une table enfant avec index est le choix durable. Le facteur décisif est la façon dont vous interrogez, pas la forme des données.