JSON naar SQL omzetten

De uitvoer is een reeks INSERT-statements en verder niets. Dat is een bewuste grens, geen ontbrekende functie. JSON vertelt je dat een waarde een getal is; het vertelt niet of de kolom een geheel getal of een decimaal met twee cijfers is, of hij nullable is, welk veld de primaire sleutel is. Dat zijn precies de beslissingen waarvoor een schema bestaat.

In de praktijk betekent dit dat de tabel al bestaat voordat je het bestand draait. Zet één keer om, lees de kolomlijst uit het eerste statement, schrijf de DDL erop af, draai dan. Dat is een lus van twee minuten en legt de schemabeslissingen waar ze horen: in een migratie die je kunt beoordelen.

Er is nergens anders waar hij vandaan kan komen. Een JSON-document heeft geen eigen naam, dus het geüploade bestand levert er een: orders.json geeft INSERT INTO orders, en export-2024.json geeft INSERT INTO export_2024, want een koppelteken is niet toegestaan in een ongequote identifier.

Het gevolg: het bestand hernoemen voor het omzetten is de goedkoopste manier om de uitvoer te sturen. Achteraf betekent zoeken-en-vervangen over elk statement in het bestand, en dat moet oppassen geen waarde te raken die toevallig hetzelfde woord bevat.

Een relationele tabel is plat en JSON niet, dus een genest object wordt platgeslagen in de kolomnaam: een record met een klantobject met een woonplaats erin levert een kolom customer_city op. Het pad blijft behouden, de punt wordt een underscore.

Eén niveau nesting geeft een tabel die iedereen graag zou declareren. Twee is meestal nog redelijk. Daarboven is de gegenereerde kolomlijst een signaal in plaats van een resultaat: een structuur vier niveaus diep beschrijft relaties, en het passende schema is meerdere tabellen met sleutels ertussen.

Een record met drie tags levert tags_0, tags_1 en tags_2 op. Elke waarde blijft behouden en de vorm wordt slechter naarmate het erger wordt: het volgende record met vijf tags verbreedt de tabel met twee kolommen extra, en er is geen zinnige query over «de tweede tag».

Een relationele database heeft daar een normaal antwoord op: een tweede tabel, één rij per tag met de ouder-id ernaast. Heeft je doeldatabase een native JSON-kolomtype, dan is de array in één kolom bewaren de derde optie, en die is juist als de array meegedragen wordt in plaats van bevraagd.

JSON-records in één export hoeven het niet met elkaar eens te zijn. Een API laat velden zonder waarde weg, dus duizend records kunnen tientallen verschillende sleutelsets tonen. De omzetting verzoent dat door de vereniging van alle sleutels in het bestand te verzamelen en NULL te schrijven waar een record niets heeft.

Die uniformiteit maakt de uitvoer veilig om als batch te draaien: de kolomlijst is in elk statement identiek. Het verklaart ook een verschil dat mensen opmerken: tien records omzetten voor een test kan minder kolommen opleveren dan het hele bestand. Schrijf de DDL altijd op basis van een omzetting van het complete bestand, nooit een steekproef.

Getallen worden kaal geschreven, booleans als TRUE en FALSE, een JSON null als NULL, en de rest als tekst tussen enkele aanhalingstekens met interne aanhalingstekens verdubbeld. Verdubbelen is de SQL-standaardvorm en wordt door elke engine begrepen.

Backslashes worden ongewijzigd doorgeschreven, correct onder de standaard maar niet hoe MySQL standaard een string leest: daar begint een backslash een ontsnappingsteken tenzij NO_BACKSLASH_ESCAPES is ingesteld. Data met Windows-paden of reguliere expressies laadt dus anders in MySQL dan in Postgres.

Hier is een JSON-bron beter dan een gescheiden bestand. Een productcode «007» is een JSON-string, dus wordt hij als gequote SQL-string geschreven en komt hij met zijn voorloopnullen in de database. Een CSV van dezelfde data heeft helemaal geen typen.

Dezelfde bescherming geldt voor lange numerieke id’s die als string zijn geëxporteerd. Zijn ze als JSON-getallen geëxporteerd, dan geldt het gebruikelijke drijvendekommaplafond — alles voorbij zestien cijfers heeft al precisie verloren voordat deze omzetting het ziet.

De uitvoer is één statement per record zonder transactie-omhulsel en zonder VALUES-clausule met meerdere rijen. Vijftigduizend losse statements door een client draaien is traag, want elk is een round trip. Het hele bestand in BEGIN en COMMIT wikkelen is één regel aan elk uiteinde en meestal de grootste enkele verbetering.

De statements zelf zijn bewust plat en overdraagbaar: geen engine-specifieke aanhalingstekens, geen ON CONFLICT-clausule. Wat je engine nodig heeft toevoegen is een zoek-en-vervang op INSERT INTO.

Voor een seed-bestand, een fixture of een paar duizend rijen zijn statements de handige vorm: leesbaar, in te checken in een repository, overal uit te voeren waar een client verbinding kan maken.

Boven een bepaalde omvang verandert de rekensom. Elke engine heeft een bulkpad — COPY in Postgres, LOAD DATA in MySQL — dat een gescheiden bestand veel sneller leest dan individuele inserts uitvoert, en bij een miljoen rijen is het verschil minuten tegenover uren.

De statements worden gegenereerd door JavaScript in dit tabblad. Er wordt niets geüpload, er is geen account of wachtrij, en het gratis niveau accepteert tot 100 MB, met het geheugen als echte grens omdat het hele document ontleed wordt voor er iets geschreven wordt.

Dat is geen bijkomstig voordeel voor dit paar. Wat straks in een database komt, is per definitie data die iemand heeft besloten te bewaren — klanten, bestellingen, rekeningen. Die door een externe omzetter halen zou een slechte ruil zijn, en hier is er geen ruil nodig.