Hoe JSON naar SQL INSERT-verklaringen te converteren

Het grootste deel van de JSON waar ik mee te maken heb, begint het leven als een API-antwoord en moet in een database terechtkomen Op de Toolz.dev-backend, die op Express en TypeORM over PostgreSQL draait, ben ik de tel kwijtgeraakt van de keren dat ik een JSON-export had van één dienst die ik in een tabel wilde laden voor een snelle query, een migratiezaadje of een lokale reproductie van een bug. Het met de hand doen is grimmig: je schrijft een CREATE TABLE, dan typ je met de hand INSERT uitspraken, het citeren van elke string, het verdubbelen van elke apostrof, het onthouden welke velden getallen zijn en het omzetten van ontbrekende sleutels in NULL. Krijg één rij fout van de tweehonderd en de hele batch wordt niet uitgevoerd.

Een JSON naar SQL converter doet dat mechanische werk voor u Je plakt een array van objecten, kiest een database dialect, en het produceert een CREATE TABLE met verstandige kolomtypen plus de INSERT uitspraken om elke rij te laden In deze handleiding wordt uitgelegd hoe de conversie werkt, welke beslissingen er worden genomen over typen en citeren, en de echte situaties waarin het een middag bespaart.

tl;dr: de JSON naar SQL-converter verandert een JSON-array van objecten in CREATE TABLE en INSERT instructies voor MySQL, PostgreSQL, SQLite of standaard SQL. Het leest objectsleutels als kolommen, leidt elk kolomtype uit de gegevens af, ontsnapt aan tekenreeksen, verandert ontbrekende sleutels en nulls in NULL, en slaat geneste objecten op als JSON Alles draait in uw browser, dus een API-reactie met echte gegevens verlaat uw machine nooit Voor het omgekeerde of voor bronnen in tabelvorm, zie CSV naar SQL en JSON naar CSV.

Wat doet een JSON naar SQL converter?

JSON en SQL model data op fundamenteel verschillende manieren JSON is een boom van geneste objecten en arrays met dynamische, per-waarde types Een relationele tabel is een plat raster van vaste, getypte kolommen Converteren van de ene naar de andere is een reeks kleine beslissingen, en de waarde van de tool is het consequent nemen van die beslissingen.

Het leest de toetsen van uw objecten als de kolomset, behandelt elk object als een rij, kiest voor elke kolom een SQL-type, en schrijft vervolgens de uitspraken uit De uitvoer bestaat uit twee delen: een optionele CREATE TABLE dat verklaart de kolommen met hun afgeleide typen, en de INSERT uitspraken die de waarden dragen U kunt het resultaat in een databaseclient plakken, opslaan als onderdeel van een migratie of in pipe het mysql, psql, of sqlite3 van de commandoregel.

De invoer is normaal gesproken een reeks objecten, zoals [{ "id": 1, "name": "Ada" }, { "id": 2, "name": "Grace" }]. Een enkel object werkt ook en wordt één rij Wat het niet accepteert is een reeks kale waarden zoals [1, 2, 3], omdat er geen sleutels zijn om in kolommen te veranderen, en het vertelt je dat in plaats van te raden.

Hoe worden kolomtypen afgeleid?

Dit is het deel dat een nuttige converter scheidt van een die alles in tekstkolommen dumpt, voor elke kolom scant het gereedschap de waarde in elke rij en kiest het smalste type dat op alle kolommen past.

Als elke contante waarde in een kolom een geheel getal is, wordt de kolom een geheel getal. Als de waarden getallen zijn maar sommige decimalen hebben, wordt het een drijvende-kommatype. Als elke waarde dat is true of false, het wordt een boolean Als elke waarde een geneste object of array is, wordt het een JSON type Al het andere, inclusief een kolom waar de waarden het niet mee eens zijn, valt terug naar tekst dus de INSERT kan niet mislukken bij een typemismatch.

null en ontbrekende toetsen worden overgeslagen bij het bepalen van het type; ze maken de kolom alleen in de praktijk nullabel Dat betekent een kolom die getallen in de meeste rijen bevat en null in een paar wordt nog steeds getypt als een getal, dat is wat je wilt.

De concrete typenaam is afhankelijk van het dialect, Hier ziet u hoe de neutrale soorten in kaart brengen:

Afgeleide soort	mysql	Postgresql	SQLite	Standaard SQL
Integer	INT	INTEGER	INTEGER	INTEGER
tientallig	DOUBLE	DOUBLE PRECISION	REAL	REAL
Booleaans	TINYINT(1)	BOOLEAN	INTEGER	BOOLEAN
Geneste JSON	JSON	JSONB	TEXT	TEXT
Tekst	VARCHAR(255)	TEXT	TEXT	TEXT

U kunt type-inferentie volledig uitschakelen, in welk geval elke kolom tekst wordt Dat is af en toe wat u wilt wanneer u gegevens in een ensceneringstabel laadt en van plan bent deze later uit te brengen.

Hoe gaat het om met de lastige delen van echte data?

Echte JSON is zelden uniform, en de conversie moet het hoofd bieden De tool behandelt drie gevallen die naïeve converters uitschakelen.

Ten eerste objecten met verschillende toetsen Als één object een email veld en een ander niet, de kolomset is de vereniging van elke sleutel die verschijnt, in de volgorde wordt gehouden die voor het eerst wordt gezien, en elk object dat een sleutel mist, krijgt NULL in die kolom Het gereedschap waarschuwt u wanneer de objecten niet uniform zijn zodat u niet verrast bent.

Ten tweede, geneste objecten en arrays SQL kolommen zijn vlak, dus een waarde als { "street": "1 Main St", "city": "London" } kan niet automatisch meerdere kolommen worden zonder dat u de vorm bepaalt In plaats daarvan serialiseert de converter de geneste waarde terug naar JSON-tekst en slaat deze op in een JSON-kolom op MySQL, een JSONB-kolom op PostgreSQL of een tekstkolom elders. De structuur blijft intact en u kunt deze later opvragen met uw database's JSON-functies.

Ten derde, gemengde typen in één kolom Als een veld een getal in één record is en een tekenreeks in een ander, zou het forceren van een numeriek type ervoor zorgen dat de helft van de invoegingen mislukt. Het gereedschap detecteert het meningsverschil en typt de kolom als tekst, en citeert vervolgens elke waarde als een letterlijke tekenreeks, zodat de batch netjes wordt uitgevoerd.

Hoe worden waarden ontsnapt?

Correcte escaping is het verschil tussen een script dat draait en een script dat een syntaxisfout op rij 47 gooit. De regels die de converter toepast zijn de standaard SQL-regels.

Tekstwaarden zijn verpakt in enkele aanhalingstekens, en elk afzonderlijk aanhalingsteken binnen een waarde wordt verdubbeld, dus O'Brien HALD 'O''Brien'. Dit is ANSI SQL string escaping en het werkt over alle vier dialecten Getallen en booleans worden zonder aanhalingstekens geschreven, omdat een geciteerd getal een string is in de meeste engines Booleans renderen als TRUE en FALSE op PostgreSQL en standaard SQL, en als 1 en 0 op MySQL en SQLite, overeenkomend met hoe elke engine ze opslaat. null en ontbrekende sleutels worden het niet-geciteerde trefwoord NULL.

Identifiers, dat wil zeggen de tabel - en kolomnamen, worden ook geciteerd, met backticks op MySQL en dubbele aanhalingstekens overal elders Dat houdt een kolom met naam order of select van botsen met een gereserveerd woord.

Hoe de JSON naar SQL Converter te gebruiken

Stap 1: Plak uw JSON

Laat een reeks objecten vallen, of druk op Voorbeeld laden om de verwachte vorm te zien Elk object wordt één rij.

Stap 2: Noem de tabel en kies een dialect

Typ de doeltabelnaam en kies MySQL, PostgreSQL, SQLite of standaard SQL. Het dialect regelt de aanhalingstekensstijl, de typenamen en hoe booleans en geneste JSON worden geschreven.

Stap 3: Stel de uitvoeropties in

Beslis of u de CREATE TABLE, of kolomtypen moeten worden afgeleid en of één meerrij moet worden uitgezonden INSERT of één instructie per rij Een enkele meerrijige invoeging laadt sneller; één instructie per rij levert een diff-vriendelijk bestand op en laat u rijen afzonderlijk uitvoeren.

Stap 4: Kopieer de SQL

Lees de gegenereerde instructies en kopieer ze Plak het script in uw database client of voer het uit vanaf de opdrachtregel om de tabel te bouwen en te laden.

Veelvoorkomende gebruiksgevallen

Een database uit een API-antwoord plaatsen

U bouwt een functie en heeft realistische rijen nodig om tegen te werken Trek een JSON-antwoord uit de upstream API, plak het in en u hebt een CREATE TABLE en inserts klaar om uw lokale database te seeden Geen hand-typen, geen gemiste offertes.

Een config of export in een tabel veranderen

Een dienst exporteert zijn gegevens als een JSON-array U wilt er ad-hoc SQL-query's tegen uitvoeren in plaats van JavaScript te schrijven om de array te filteren Converteer het naar SQL, laad het in SQLite en bevraag het met de taal die precies daarvoor is gebouwd.

Een migratiezaadje bouwen

Framework migraties hebben vaak een startstap nodig die referentiegegevens invoegt Genereren van de INSERT uitspraken van een bekende goede JSON-armatuur geven u een stabiel, controleerbaar blok om in de migratie te plakken, in plaats van de gegevens met de hand te vertalen.

Een productiebug lokaal reproduceren

Een bug verschijnt alleen voor een specifieke set records Neem de JSON voor die records, converteer het naar inserts, laad ze in uw lokale database en reproduceer het probleem zonder een productiedatabase te kopiëren.

Multi-rij versus per-rij INSERT: welke kiezen?

Een invoegplaat met meerdere rijen bundelt elke rij in één instructie met veel VALUES tupels Het is compact en laadt het snelst, omdat de database één instructie parseert en plant in plaats van honderden Kies het voor bulk laden.

Eén instructie per rij is uitgebreider maar heeft twee voordelen Het levert een bestand op waarbij elke rij een eigen regel is, die in een code review netjes leest en goed diffundeert in versiebeheer En als één rij een beperking overtreedt, slagen de andere nog steeds, wat handig is als je rommelige gegevens laadt en wilt zien welke rijen falen in plaats van de hele batch te verliezen Kies het voor zaden die je gaat vastleggen of voor gegevens die je nog niet volledig vertrouwt.

Het uitvoeren van de gegenereerde SQL vanaf de opdrachtregel

Als je het script hebt, is het laden een oneliner in elke engine Voor SQLite sla je de uitvoer op in een bestand en pijp je deze in sqlite3 mydata.db < insert.sql maakt de tabel aan en laadt de rijen in een vers databasebestand Voor PostgreSQL, psql -d mydb -f insert.sql voert het hele script uit tegen de benoemde database. Voor MySQL, mysql -u user -p mydb < insert.sql doet hetzelfde Omdat de generator identifiers citeert en waarden voor het dialect dat u hebt gekozen, ontsnapt, wordt het script uitgevoerd zonder de handmatige fixups die handgeschreven SQL meestal nodig heeft.

Als u één instructie per rij hebt gegenereerd in plaats van één invoeging met meerdere rijen, is het inpakken van de lading in een transactie de moeite waard. Toevoegen BEGIN; aan de top en COMMIT; onderaan verandert honderden individuele inzetstukken in één atomaire bewerking, wat zowel sneller als veiliger is: als een latere rij een beperking niet doorstaat, rolt de hele belasting terug in plaats van de tabel half gevuld te laten. Voor een inzetstuk met meerdere rijen is dit al effectief atomair, aangezien het één enkele verklaring is.

Prestaties en limieten met grote arrays

De conversie zelf is lineair in de grootte van de invoer en draait volledig in uw browser, dus een paar duizend rijen converteren direct De praktische limieten zitten niet in de tool maar in wat u met de uitvoer doet Een enkele meerrij INSERT met tienduizenden tupels kan een database worden overschreden's maximale instructiegrootte of pakketlimiet, MySQL's max_allowed_packet als degene die als eerste wordt geraakt Als u een zeer grote array laadt, schakelt u over naar één instructie per rij, zodat elke instructie klein is, of splitst u de invoer in batches van een paar honderd rijen en converteert u deze afzonderlijk.

Er is ook een precisievoorbehoud dat de moeite waard is om te weten. JSON-nummers worden door de browser ontleed. ' S JSON-engine in standaard dubbele precisie-floats, dus een geheel getal groter dan ongeveer negen biljard, buiten JavaScript' s veilige gehele bereik, kan de precisie verliezen voordat de tool het ooit ziet. Dat is een eigenschap van JSON die in elke omgeving parseert, niet van de converter, maar het betekent dat een gigantische numerieke id veiliger wordt weergegeven als een string in de bron-JSON, die de tool vervolgens als tekst zal opslaan en precies zal bewaren.

Waar dit past bij andere tools

JSON naar SQL is één richting van een kleine familie Wanneer uw bron een spreadsheet of CSV-export is in plaats van JSON, kan CSV naar SQL doet hetzelfde werk van tabellarische invoer met hetzelfde type gevolgtrekking en ontsnappen Wanneer u de gegevens nodig hebt als een plat bestand in plaats van een database, JSON naar CSV maakt de array plat in rijen, en CSV naar JSON gaat de andere kant op Voordat je iets converteert, JSON-formatter zal de JSON mooi afdrukken en valideren, zodat je een verdwaalde komma oppakt voordat het een ontleedfout wordt. En zodra je de SQL hebt, SQL-formatter zal de verklaringen inspringen en uitlijnen voor een migratiebestand dat u met trots kunt vastleggen.

Voor de redenering achter het doen van dit alles in de browser in plaats van het uploaden van gegevens naar een converter service, de Gegevensprivacy in online tools gids schetst het model aan de clientzijde, en de Webontwikkelaar Toolkit verzamelt de hulpprogramma's die goed aansluiten bij dataconversiewerk.

FAQ

Hoe converteer ik JSON naar SQL?

Plak een JSON-array van objecten, stel de tabelnaam in en kies uw SQL-dialect De tool leest de objecttoetsen als kolomnamen, leidt elk kolomtype af en genereert een CREATE TABLE plus INSERT-instructies die u in uw database kunt kopiëren en uitvoeren.

Welke JSON-vorm accepteert het?

Een array van objecten is de normale invoer, waarbij elk object één rij is, Een enkel object werkt ook en produceert één rij Arrays van primitieven of diep niet-overeenkomende vormen worden afgewezen met een fout die uitlegt wat er verwacht wordt.

Welke SQL databases worden ondersteund?

De converter voert instructies uit voor MySQL, PostgreSQL, SQLite en standaard SQL. Het dialect dat u kiest, regelt het aanhalen van de identificatiegegevens, het type en de Booleaanse syntaxis en het oorspronkelijke JSON-type, zodat het script zonder bewerkingen in die database wordt uitgevoerd.

Hoe worden kolomtypen bepaald?

Elke kolom wordt tegen elk object gecontroleerd Als alle huidige waarden hele getallen zijn wordt het een geheel getaltype, alle getallen worden een decimaal type, alle ware of valse waarden worden een Booleaans type, alle objecten of arrays worden een JSON-type, en alles wat gemengd is wordt tekst.

Wat gebeurt er met geneste objecten en arrays?

Ze worden terug geserialiseerd naar JSON tekst en opgeslagen in een JSON kolom op MySQL, een JSONB kolom op PostgreSQL, of een TEKST kolom op SQLite en standaard SQL Dit houdt de structuur intact zodat u deze later kunt opvragen met uw database JSON functies.

Wat als mijn objecten verschillende sleutels hebben?

Dat is prima De kolommen zijn de vereniging van elke sleutel die verschijnt, bewaard in de volgorde die voor het eerst gezien werd Elk object dat een van die toetsen mist krijgt NULL in die kolom, en het gereedschap waarschuwt je wanneer de objecten niet uniform zijn.

Hoe worden strings en quotes ontsnapt?

Tekstwaarden zijn verpakt in enkele aanhalingstekens en elk afzonderlijk aanhalingsteken binnen een waarde wordt verdubbeld, wat de standaard SQL-escape is. Getallen en booleans worden niet geciteerd, null wordt NULL en identificatiegegevens worden geciteerd met backticks voor MySQL of dubbele aanhalingstekens voor de andere dialecten.

Is mijn json ergens geüpload?

Nee. Parsing en SQL-generatie worden uitgevoerd als JavaScript in uw browser Niets wordt verzonden, gelogd of opgeslagen, en de tool blijft offline werken zodra deze is geladen, wat u kunt bevestigen door het tabblad netwerk te bekijken terwijl u converteert.

---