So konvertieren Sie JSON in SQL-EINFÜGungsanweisungen

Das meiste von dem JSON, mit dem ich mich beschäftige, startet das Leben als API-Antwort und muss in einer Datenbank landen Auf dem Toolz.dev-Backend, das auf Express und TypeORM über PostgreSQL läuft, habe ich die Zeiten, in denen ich einen JSON-Export von einem Dienst hatte, den ich für eine schnelle Abfrage in eine Tabelle laden wollte, einen Migration-Seed oder eine lokale Reproduktion eines Fehlers, mit der Hand zu machen ist düster: Du schreibst einen CREATE TABLE„Dann tippen Sie von Hand INSERT Anweisungen, Zitieren jeder Zeichenfolge, Verdoppeln jedes Apostrophs, Erinnern, welche Felder Zahlen sind, und Verwandeln fehlender Schlüssel in NULL. Holen Sie sich eine Zeile falsch von zweihundert und die gesamte Charge läuft nicht.

Ein JSON-zu-SQL-Konverter erledigt diese mechanische Arbeit für Sie. Sie fügen ein Array von Objekten ein, wählen einen Datenbankdialekt aus und es erzeugt einen CREATE TABLE Mit sinnvollen Spaltentypen plus der INSERT Anweisungen zum Laden jeder Zeile In diesem Handbuch wird erläutert, wie die Konvertierung funktioniert, welche Entscheidungen sie über Typen und Zitate trifft und welche realen Situationen sie einen Nachmittag spart.

tl; dr: der JSON-SQL-Konverter Verwandeln Sie ein JSON-Array von Objekten in CREATE TABLE und INSERT Anweisungen für MySQL, PostgreSQL, SQLite oder Standard-SQL. Es liest Objektschlüssel als Spalten, leitet jeden Spaltentyp aus den Daten ab, entweicht Zeichenfolgen, verwandelt fehlende Schlüssel und Nullstellen in NULL‘und speichert verschachtelte Objekte als JSON. Alles läuft in Ihrem Browser, so dass eine API-Antwort mit realen Daten niemals Ihren Computer verlässt Für die Rückseite oder für tabellarische Quellen siehe CSV zu SQL und JSON zu CSVdrohen

Was macht ein JSON-zu-SQL-Konverter?

JSON - und SQL-Modelldaten auf grundlegend unterschiedliche Weise JSON ist ein Baum aus verschachtelten Objekten und Arrays mit dynamischen, pro-Wert-Typen Eine relationale Tabelle ist ein flaches Raster aus festen, typisierten Spalten Die Konvertierung von einem zum anderen ist eine Reihe kleiner Entscheidungen, und der Wert des Tools trifft diese Entscheidungen konsequent.

Es liest die Schlüssel Ihrer Objekte als Spaltensatz, behandelt jedes Objekt als Zeile, wählt für jede Spalte einen SQL-Typ aus, und schreibt dann die Anweisungen aus Die Ausgabe hat zwei Teile: einen optionalen CREATE TABLE Dadurch werden die Spalten mit ihren abgeleiteten Typen deklariert und die INSERT Anweisungen, die die Werte tragen Sie können das Ergebnis in einen Datenbank-Client einfügen, es als Teil einer Migration speichern oder in ein Pipe einfügen mysql, psql, oder sqlite3 Von der Kommandozeile.

Die Eingabe ist normalerweise eine Anordnung von Objekten, wie z [{ "id": 1, "name": "Ada" }, { "id": 2, "name": "Grace" }]. Ein einzelnes Objekt funktioniert auch und wird zu einer Zeile. Was es nicht akzeptiert, ist ein Array blanker Werte wie z [1, 2, 3]„, weil es keine Schlüssel gibt, die in Spalten umgewandelt werden könnten, und das sagt es Ihnen, anstatt zu raten.

Wie werden Spaltentypen abgeleitet?

Dies ist der Teil, der einen nützlichen Konverter von einem trennt, der alles in Textspalten abgibt, für jede Spalte scannt das Tool den Wert in jeder Zeile und wählt den engsten Typ aus, der für alle passt.

Ist jeder Barwert in einer Spalte eine ganze Zahl, so wird die Spalte zum ganzzahligen Typ, sind die Werte Zahlen, haben aber einige Dezimalstellen, so wird sie zum Gleitkommatyp, ist jeder Wert true oder falseEs wird ein boolescher Wert ist ein verschachteltes Objekt oder Array, wird es ein JSON-Typ Alles andere, einschließlich einer Spalte, in der die Werte nicht übereinstimmen, fällt auf Text zurück, so dass die INSERT Kann bei einer Typinkongruenz nicht fehlschlagen.

null und fehlende Tasten bei der Typentscheidung übersprungen werden; sie machen die Spalte nur in der Praxis nullbar Das bedeutet eine Spalte, die Zahlen in den meisten Zeilen und null In einigen wird immer noch eine Zahl eingegeben, was Sie möchten.

Der konkrete Typenname ist dialektabhängig Hier ist, wie die neutralen Arten abbilden:

Abgeleitete Art	MySQL	PostgreSQL	SQLite	Standard-sql
Ganzzahlig	INT	INTEGER	INTEGER	INTEGER
dezimal	DOUBLE	DOUBLE PRECISION	REAL	REAL
Boolesch	TINYINT(1)	BOOLEAN	INTEGER	BOOLEAN
Geschachtelter JSON	JSON	JSONB	TEXT	TEXT
Text	VARCHAR(255)	TEXT	TEXT	TEXT

Sie können die Typinferenz vollständig deaktivieren. In diesem Fall wird jede Spalte zu Text. Das ist gelegentlich das, was Sie möchten, wenn Sie Daten in eine Staging-Tabelle laden und planen, sie später zu übertragen.

Wie geht es mit den unangenehmen Teilen realer Daten um?

Echtes JSON ist selten einheitlich, und die Konvertierung muss zurechtkommen Das Tool bearbeitet drei Fälle, die naive Konverter auslösen.

Zunächst Objekte mit verschiedenen Tasten Wenn ein Objekt eine email Feld und ein anderes nicht, der Spaltensatz ist die Vereinigung jedes erscheinenden Schlüssels, der in der zuerst gesehenen Reihenfolge beibehalten wird und jedes Objekt, dem ein Schlüssel fehlt, erhält NULL In dieser Spalte. Das Tool warnt Sie, wenn die Objekte nicht einheitlich sind, damit Sie nicht überrascht sind.

Zweitens, verschachtelte Objekte und Arrays SQL-Spalten sind flach, also ein Wert wie { "street": "1 Main St", "city": "London" } Kann nicht automatisch zu mehreren Spalten werden, ohne dass Sie die Form festlegen Stattdessen serialisiert der Konverter den verschachtelten Wert zurück auf JSON-Text und speichert ihn in einer JSON-Spalte auf MySQL, einer JSONB-Spalte auf PostgreSQL oder einer Textspalte an anderer StelleDie Struktur bleibt erhalten und Sie können sie später mit Ihrer Datenbank abfragen' s JSON-Funktionen.

Drittens, gemischte Typen in einer Spalte Wenn ein Feld eine Zahl in einem Datensatz und eine Zeichenfolge in einem anderen ist, würde das Erzwingen eines numerischen Typs dazu führen, dass die Hälfte der Einfügungen fehlschlägt. Das Tool erkennt die Meinungsverschiedenheit und gibt die Spalte als Text ein, dann zitiert es jeden Wert als Zeichenfolgen-Literal, sodass der Stapel sauber ausgeführt wird.

Wie werden Werte maskiert?

Richtiges Escaping ist der Unterschied zwischen einem Skript, das ausgeführt wird und einem, das einen Syntaxfehler in Zeile 47 wirft Die Regeln, die der Konverter anwendet, sind die Standard-SQL-Regeln.

Textwerte werden in einzelne Anführungszeichen gewickelt und jedes einzelne Anführungszeichen innerhalb eines Werts wird verdoppelt O'Brien wird 'O''Brien'. Dies ist ANSI SQL String Escaping und es funktioniert über alle vier Dialekte Zahlen und booleans werden ohne Zitate geschrieben, denn eine zitierte Zahl ist eine Zeichenfolge in den meisten Engines Booleans rendern als TRUE und FALSE auf PostgreSQL und Standard-SQL und als 1 und 0 Auf MySQL und SQLite, passend dazu, wie jede Engine sie speichert. null und fehlende Schlüssel werden zum nicht zitierten Schlüsselwort NULLdrohen

Identifikatoren, also die Tabellen- und Spaltennamen, werden ebenfalls zitiert, mit Hintergrundinformationen zu MySQL und doppelten Anführungszeichen überall sonst. Das behält den Namen einer Spalte order oder select Von der Kollision mit einem zurückhaltenden Wort.

So verwenden Sie den JSON to SQL Converter

Schritt 1: Fügen Sie Ihren JSON ein

Ein Array von Objekten einwerfen, oder Lastprobe drücken, um die erwartete Form zu sehen Jedes Objekt wird zu einer Zeile.

Schritt 2: Benennen Sie die Tabelle und wählen Sie einen Dialekt aus

Geben Sie den Namen der Zieltabelle ein und wählen Sie MySQL, PostgreSQL, SQLite oder Standard-SQL. Der Dialekt steuert den Anführungsstil, die Typnamen und wie Booleans und verschachteltes JSON geschrieben werden.

Schritt 3: Legen Sie die Ausgabeoptionen fest

Entscheiden Sie, ob die aufgenommen werden soll CREATE TABLE, ob Spaltentypen abgeleitet werden sollen und ob eine mehrreihige Ausgabe erfolgen soll INSERT oder eine Anweisung pro Zeile Eine einzelne mehrreihige Einfügung lädt schneller; eine Anweisung pro Zeile erzeugt eine diff-freundliche Datei und lässt Sie Zeilen einzeln ausführen.

Schritt 4: Kopieren Sie das SQL

Lesen Sie die generierten Anweisungen und kopieren Sie sie Fügen Sie das Skript in Ihren Datenbank-Client ein oder führen Sie es von der Befehlszeile aus aus, um die Tabelle zu erstellen und zu laden.

Häufige Anwendungsfälle

Seeding einer Datenbank aus einer API-Antwort

Sie erstellen eine Funktion und benötigen realistische Zeilen, mit denen Sie arbeiten können Ziehen Sie eine JSON-Antwort von der Upstream-API, fügen Sie sie ein und Sie haben eine CREATE TABLE und fügt bereit, Ihre lokale Datenbank zu säen Kein Handtippen, keine verpassten Zitate.

Eine Konfiguration oder einen Export in eine Tabelle umwandeln

Ein Dienst exportiert seine Daten als JSON-Array, Sie möchten Ad-hoc-SQL-Abfragen dagegen ausführen, anstatt JavaScript zu schreiben, um das Array zu filtern Konvertieren Sie es in SQL, laden Sie es in SQLite und fragen Sie es mit der Sprache ab, die genau dafür erstellt wurde.

Aufbau eines Migrationssaatguts

Framework-Migrationen benötigen oft einen Seed-Schritt, der Referenzdaten einfügt Generieren der INSERT Anweisungen aus einer bekannten JSON-Leuchte geben Ihnen einen stabilen, überprüfbaren Block zum Einfügen in die Migration, anstatt die Daten von Hand zu übersetzen.

Einen Produktionsfehler lokal reproduzieren

Ein Fehler erscheint nur für einen bestimmten Satz von Datensätzen Nehmen Sie den JSON für diese Datensätze, konvertieren Sie ihn in Einfügungen, laden Sie sie in Ihre lokale Datenbank und reproduzieren Sie das Problem, ohne eine Produktionsdatenbank zu kopieren.

Mehrreihiger vs. pro Reihe EINFÜGEN: Welche Wahl?

Eine mehrreihige Einfügung bündelt jede Zeile zu einer Anweisung mit vielen VALUES Tupel Es ist kompakt und lädt am schnellsten, denn die Datenbank analysiert und plant eine Anweisung statt Hunderter Wählen Sie es für das Laden in großen Mengen.

Eine Anweisung pro Zeile ist ausführlicher, hat aber zwei Vorteile Sie erzeugt eine Datei, in der jede Zeile eine eigene Zeile ist, die in einer Codeüberprüfung sauber liest und sich in der Versionskontrolle gut ausbreitet Und wenn eine Zeile gegen eine Einschränkung verstößt, gelingt es den anderen trotzdem, was praktisch ist, wenn Sie unordentliche Daten laden und sehen möchten, welche Zeilen fehlschlagen, anstatt den gesamten Stapel zu verlieren Wählen Sie es für Seeds, die Sie festlegen möchten, oder für Daten, denen Sie noch nicht vollständig vertrauen.

Ausführen des generierten SQL über die Befehlszeile

Wenn man das Skript hat, ist das Laden in jeder Engine ein Einzeiler, bei SQLite speichert man die Ausgabe in eine Datei und leitet sie ein: sqlite3 mydata.db < insert.sql Tabelle erstellt und die Zeilen in eine frische Datenbankdatei lädt Für PostgreSQL, die psql -d mydb -f insert.sql Führt das ganze Skript gegen die benannte Datenbank aus Für MySQL, mysql -u user -p mydb < insert.sql Tut das gleiche Weil der Generator Kennungen angibt und Werte für den Dialekt, den Sie ausgewählt haben, ausweicht, läuft das Skript ohne die manuellen Fixups, die handgeschriebenes SQL normalerweise benötigt.

Wenn Sie eine Anweisung pro Zeile anstelle einer einzelnen mehrreihigen Einfügung generiert haben, lohnt es sich, die Last in eine Transaktion einzuwickeln. Hinzufügen BEGIN; Oben und COMMIT; Unten werden Hunderte einzelner Einsätze in einen atomaren Vorgang umgewandelt, der sowohl schneller als auch sicherer ist: Wenn eine spätere Zeile eine Einschränkung nicht erfüllt, rollt die gesamte Last zurück, anstatt die Tabelle halb gefüllt zu lassen. Für einen mehrreihigen Einsatz ist dies bereits effektiv atomar, da es sich um eine einzelne Anweisung handelt.

Leistung und Grenzen bei großen Arrays

Die Konvertierung selbst ist linear in der Größe der Eingabe und läuft komplett in Ihrem Browser, also ein paar tausend Zeilen konvertieren sofort Die praktischen Grenzen liegen nicht im Tool sondern in dem, was Sie mit der Ausgabe machen Eine einzige Mehrzeile INSERT Mit Zehntausenden von Tupeln kann eine Datenbank überschritten werden. & #39;s maximale Anweisungsgröße oder Paketgrenze, MySQL's max_allowed_packet Derjenige zu sein, der zuerst getroffen wird Wenn Sie ein sehr großes Array laden, wechseln Sie entweder zu einer Anweisung pro Zeile, sodass jede klein ist, oder teilen Sie die Eingabe in Chargen von einigen hundert Zeilen auf und konvertieren Sie sie separat.

Es gibt auch eine Präzisionsvorbehalt, die es wert ist, bekannt zu sein JSON-Nummern werden vom Browser analysiert's JSON-Engine in Standard-Schwimmer mit doppelter Genauigkeit, sodass eine ganze Zahl größer als etwa neun Billiarden, jenseits von JavaScript's sicherem Ganzzahlbereich, möglicherweise an Präzision verliert, bevor das Tool sie jemals sieht Das ist eine Eigenschaft des JSON-Parsings in jeder Umgebung, nicht des Konverters, aber es bedeutet, dass eine riesige numerische ID sicherer als Zeichenfolge im Quell-JSON dargestellt wird, die das Tool dann als Text speichert und genau bewahrt.

Wo dies mit anderen Tools passt

JSON zu SQL ist eine Richtung einer kleinen Familie Wenn Ihre Quelle eine Tabellenkalkulation oder ein CSV-Export und nicht JSON ist, CSV zu SQL Erledigt den gleichen Auftrag aus tabellarischer Eingabe mit gleicher Typinferenz und Escape, Wenn Sie die Daten als flache Datei anstelle einer Datenbank benötigen, wird JSON zu CSV Flacht das Array in Zeilen ab und CSV zu JSON Geht in die andere Richtung zurück Bevor du etwas umwandelst JSON-Formater Wird hübsch drucken und validieren Sie das JSON, so dass Sie ein Streuspeicher-Komma fangen, bevor es zu einem Analysefehler wird Und sobald Sie das SQL haben, SQL-Formater Anweisungen für eine Migrationsdatei einrücken und ausrichten wird, auf deren Zusage Sie stolz sind.

Für die Begründung, all dies im Browser zu tun, anstatt Daten auf einen Konverterdienst hochzuladen, ist die Datenschutz in Online-Tools Der Leitfaden legt das clientseitige Modell und die Webentwickler-Toolkit Sammelt die Dienstprogramme, die gut mit der Datenkonvertierungsarbeit gekoppelt sind.

FAQ

Wie konvertiere ich JSON in SQL?

Fügen Sie ein JSON-Array von Objekten ein, legen Sie den Tabellennamen fest und wählen Sie Ihren SQL-Dialekt aus. Das Tool liest die Objektschlüssel als Spaltennamen, leitet jeden Spaltentyp ab und generiert eine CREATE TABLE plus INSERT-Anweisungen, die Sie kopieren und in Ihrer Datenbank ausführen können.

Welche JSON-Form akzeptiert es?

Ein Array von Objekten ist die normale Eingabe, wobei jedes Objekt eine Zeile ist Ein einzelnes Objekt funktioniert auch und erzeugt eine Zeile Arrays von Grundelementen oder tief nicht übereinstimmenden Formen werden mit einem Fehler abgelehnt, der erklärt, was erwartet wird.

Welche SQL-Datenbanken werden unterstützt?

Der Konverter gibt Anweisungen für MySQL, PostgreSQL, SQLite und Standard-SQL aus. Der von Ihnen ausgewählte Dialekt steuert die Angabe der Kennung, den Typ und die boolesche Syntax sowie den nativen JSON-Typ, sodass das Skript in dieser Datenbank ohne Änderungen ausgeführt wird.

Wie werden Spaltentypen entschieden?

Jede Spalte wird gegen jedes Objekt geprüft, wenn alle Gegenwartswerte ganze Zahlen sind wird es ein ganzzahliger Typ, alle Zahlen werden zu einem Dezimaltyp, alle wahren oder falschen Werte werden zu einem booleschen Typ, alle Objekte oder Arrays werden zu einem JSON-Typ, und alles Gemischte wird zu Text.

Was passiert mit verschachtelten Objekten und Arrays?

Sie werden wieder auf JSON-Text serialisiert und in einer JSON-Spalte auf MySQL, einer JSONB-Spalte auf PostgreSQL oder einer TEXT-Spalte auf SQLite und Standard-SQL gespeichert. Dadurch bleibt die Struktur intakt, sodass Sie sie später mit Ihren JSON-Funktionen in der Datenbank abfragen können.

Was ist, wenn meine Objekte unterschiedliche Schlüssel haben?

Das ist in Ordnung Die Spalten sind die Vereinigung jedes Schlüssels, der erscheint, in der Reihenfolge gehalten, die zuerst gesehen wurde Jedes Objekt, dem einer dieser Schlüssel fehlt, erhält NULL in dieser Spalte, und das Werkzeug warnt Sie, wenn die Objekte nicht einheitlich sind.

Wie werden Zeichenfolgen und Zitate entkommen?

Textwerte werden in einfache Anführungszeichen gewickelt und jedes einzelne Anführungszeichen innerhalb eines Wertes wird verdoppelt, was das Standard-SQL-Escape ist. Zahlen und Booleans bleiben unzitiert, Null wird zu NULL und Identifikatoren werden mit Hintergrundinformationen für MySQL oder doppelten Anführungszeichen für die anderen Dialekte zitiert.

Wird mein JSON irgendwo hochgeladen?

Nr. Parsen und SQL-Generierung laufen in Ihrem Browser als JavaScript, es wird nichts übertragen, protokolliert oder gespeichert, und das Tool funktioniert nach dem Laden offline weiter, was Sie bestätigen können, indem Sie während der Konvertierung die Registerkarte Netzwerk ansehen.

---