Convertir JSON en SQL

La sortie est une suite d’instructions INSERT et rien d’autre. C’est une limite délibérée plutôt qu’une fonctionnalité manquante. Le JSON vous dit qu’une valeur est un nombre ; il ne vous dit pas si la colonne est un entier ou un décimal à deux places, si elle accepte le null, quel champ est la clé primaire, quelles sont les clés étrangères ni combien de caractères le texte peut contenir. Ce sont les décisions qu’un schéma est censé enregistrer, et un convertisseur qui les devinerait produirait un CREATE TABLE qu’il faudrait relire ligne à ligne avant de s’y fier — ce qui est plus de travail que de l’écrire soi-même.

Ce que cela signifie en pratique, c’est que la table existe déjà avant que vous n’exécutiez le fichier. Convertissez une fois, lisez la liste des colonnes sur la première instruction, écrivez le DDL en conséquence, puis exécutez. C’est une boucle de deux minutes, et elle place les décisions de schéma là où elles doivent être, dans une migration qu’on peut relire, plutôt que dans un fichier généré qui se trouve fonctionner sur le premier échantillon.

Il n’y a nulle part ailleurs d’où il pourrait venir. Un document JSON n’a pas de nom pour lui-même, si bien que c’est le fichier téléversé qui en fournit un : orders.json produit INSERT INTO orders, et export-2024.json produit INSERT INTO export_2024, parce qu’un trait d’union n’est pas légal dans un identifiant non entouré de guillemets et qu’un chiffre en début ne l’est pas non plus.

La conséquence, c’est que renommer le fichier avant la conversion est le moyen le plus économique d’agir sur la sortie. Le faire après signifie une recherche-remplacement sur chaque instruction du fichier, et le remplacement doit faire attention à ne pas tomber sur une valeur qui se trouve contenir le même mot. Trente secondes dans un explorateur de fichiers battent une expression rationnelle sur cent mille lignes.

Une table relationnelle est plate et JSON ne l’est pas, si bien qu’un objet imbriqué est aplati dans le nom de la colonne : un enregistrement ayant un objet client contenant une ville produit une colonne appelée customer_city. Le chemin est conservé, le point devient un souligné parce qu’un point n’est pas légal dans un identifiant, et rien n’est écarté.

Un niveau d’imbrication produit une table que n’importe qui serait content de déclarer. Deux niveaux, c’est en général encore correct. Au-delà, la liste de colonnes générée est un signal plutôt qu’un résultat : une structure JSON à quatre niveaux décrit des relations, et le schéma qui lui convient est composé de plusieurs tables avec des clés entre elles. La charger dans une table large et unique fonctionne et rend chaque requête plus dure qu’elle ne devait l’être.

Un enregistrement avec trois étiquettes produit tags_0, tags_1 et tags_2. Chaque valeur survit et la forme est fausse d’une manière qui empire : l’enregistrement suivant avec cinq étiquettes élargit la table de deux colonnes supplémentaires, et il n’y a aucune requête sensée sur « la deuxième étiquette ».

Une base de données relationnelle a une réponse normale à cela et c’est une seconde table — une ligne par étiquette, avec l’identifiant parent à côté. Pour y arriver depuis un export JSON, il faut convertir deux fois en ayant extrait le tableau entre les deux, ou charger la table large comme étape de transition et normaliser par une requête. Les deux demandent plus de travail qu’une seule conversion et produisent tous deux un schéma sur lequel on peut effectivement interroger. Si la base cible a un type de colonne JSON natif, stocker le tableau dans une seule colonne est la troisième option, et c’est la bonne quand le tableau est porté plutôt qu’interrogé.

Les enregistrements JSON d’un même export ne sont pas obligés d’être d’accord. Une API omet les champs qui n’ont pas de valeur, si bien que mille enregistrements peuvent présenter une douzaine d’ensembles de clés différents. La conversion les réconcilie en collectant l’union de toutes les clés du fichier et en écrivant NULL là où un enregistrement n’a rien.

Cette uniformité est ce qui rend la sortie sûre à exécuter en lot : la liste des colonnes est identique dans chaque instruction, de sorte qu’une table qui accepte la première ligne accepte toutes les autres. Cela explique aussi une différence que les gens remarquent — convertir dix enregistrements pour un test peut produire moins de colonnes que convertir le fichier entier, parce que l’échantillon n’a pas eu les champs les plus rares. Écrivez le DDL d’après une conversion du fichier complet, jamais d’après un échantillon.

Les nombres sont écrits à nu, les booléens en TRUE et FALSE, un JSON null en NULL, et tout le reste en chaîne entre apostrophes simples, les apostrophes internes étant doublées. Doubler les apostrophes est la forme standard SQL et c’est ce que chaque moteur comprend, si bien qu’un nom de famille comme O’Brien se charge correctement partout.

Les barres obliques inverses sont écrites telles quelles, ce qui est correct selon la norme et qui n’est pas la lecture par défaut de MySQL : là, une barre oblique inversée commence une séquence d’échappement à moins que NO_BACKSLASH_ESCAPES ne soit activé. Les données contenant des chemins Windows, des expressions régulières ou du LaTeX se chargeront donc différemment dans MySQL et dans Postgres. Si c’est le cas de votre export, activez le mode pour la session avant d’exécuter le fichier, et vérifiez une ligne concernée ensuite plutôt que de présumer.

C’est là qu’une source JSON est meilleure qu’une source délimitée. Un code produit « 007 » est une chaîne JSON, donc il est écrit en tant que chaîne SQL entourée d’apostrophes et arrive dans la base avec ses zéros initiaux. Un CSV des mêmes données n’a pas de types du tout, et chaque outil de la chaîne a l’occasion de décider que 007 est le nombre sept.

La même protection couvre les identifiants numériques longs qui ont été exportés en chaînes. Là où ils ont été exportés en nombres JSON, cependant, le plafond habituel en virgule flottante s’applique — au-delà de seize chiffres, la précision a déjà été perdue avant que cette conversion ne voie le fichier, et aucune précaution de l’écriture ne peut la restituer. Si l’export est le vôtre, exporter les identifiants en chaînes est la correction, et c’est une correction à la source plutôt qu’ici.

La sortie est une instruction par enregistrement, sans enrobage de transaction et sans clause VALUES à lignes multiples. Faire passer cinquante mille instructions distinctes par un client est lent, parce que chacune fait l’aller-retour. Englober le fichier entier dans BEGIN et COMMIT est une ligne à chaque bout et représente en général la plus grande amélioration unitaire disponible.

Les instructions elles-mêmes sont volontairement simples et portables : pas de citation propre à un moteur, pas de clause ON CONFLICT, pas de préfixe de schéma. Ce que votre moteur attend est une recherche-remplacement sur INSERT INTO — un nom de schéma, un ON CONFLICT DO NOTHING pour un chargement ré-exécutable — et partir de la forme la plus simple est ce qui rend ces modifications prévisibles.

Pour un fichier de seed, une fixture ou quelques milliers de lignes, les instructions sont la forme commode : elles sont lisibles, peuvent être confiées à un dépôt, et s’exécutent partout où un client peut se connecter. C’est le travail pour lequel cette page est écrite.

Au-delà d’une certaine taille, l’arithmétique change. Chaque moteur a un chemin en masse — COPY dans Postgres, LOAD DATA dans MySQL, un import dans un outil client — qui lit un fichier délimité bien plus vite qu’il n’exécute des insertions individuelles, et pour un chargement d’un million de lignes la différence se compte en minutes contre heures. Convertissez le même export en CSV ou TSV dans ce cas, ou en NDJSON si la destination est un entrepôt plutôt qu’une base relationnelle.

Les instructions sont générées par du JavaScript dans cet onglet. Rien n’est téléversé, il n’y a ni compte ni file d’attente, et le niveau gratuit accepte jusqu’à 100 Mo, la mémoire servant de véritable plafond puisque le document entier est analysé avant qu’une seule ligne ne soit écrite.

Ce n’est pas un bénéfice accessoire pour cette paire. Tout ce qui va être inséré dans une base de données est, par définition, une donnée que quelqu’un a décidé de conserver — clients, commandes, transactions, comptes. La faire passer par un convertisseur tiers pour qu’on lui double ses apostrophes serait une piètre transaction, et ici il n’y a aucune transaction à faire.