JSON in PostgreSQL — direkt in psql einfügen

Was dieses Werkzeug tut

Aus einer JSON-Probe wird ein CREATE TABLE.

{ "id": 1, "firstName": "Ada", "score": 9.5, "address": { "city": "Paris" } }

CREATE TABLE "root" (
  "id" BIGINT NOT NULL,
  "first_name" TEXT NOT NULL,
  "score" DOUBLE PRECISION NOT NULL,
  "address" JSONB NOT NULL
);

Die Namen stehen in snake_case und in Anführungszeichen, was sie exakt hält und ein reserviertes Wort wie "order" ohne Umstände zu einer Spalte macht.

TEXT hat keine Länge zu erfinden

PostgreSQL speichert TEXT und VARCHAR(n) gleich; der einzige Unterschied ist die Einschränkung. Da eine Probe die wirkliche Grenze nicht kennt — nur den längsten Wert, den sie zufällig enthält —, wird keine geschrieben. VARCHAR(40) später zu ergänzen ist eine Entscheidung über Ihre Domäne und kostet keinen Speicher.

Darum fehlt dieser Seite auch das Gegenstück zur MySQL-Notiz über VARCHAR(255): hier gibt es keine Länge, bei der man sich irren könnte.

Alle Zeilen, nicht nur die erste

[{ "a": 1 }, { "b": "x" }]

CREATE TABLE "root_item" (
  "a" BIGINT NULL,
  "b" TEXT NULL
);

Die Tabelle entstand bisher allein aus dem ersten Objekt: die zweite Zeile Ihrer eigenen Probe hatte keinen Platz. Die Spalten sind nun die Vereinigung aller Objekte, und ein Schlüssel, der manchen Zeilen fehlt, ist nullbar — er ist es ja.

Die Typen werden genauso zusammengeführt. Ist derselbe Schlüssel in einem Objekt eine Zahl und in einem anderen eine Zeichenkette, nimmt keine skalare Spalte beides auf: die Spalte wird JSONB. Eine ganze Zahl neben einer Dezimalzahl ist etwas anderes — DOUBLE PRECISION deckt beide ab, und das bekommt sie.

JSONB für das, wofür SQL keine Spalte hat

Verschachtelte Objekte und Arrays werden JSONB. Keine verbundene Tabelle, kein Fremdschlüssel: eine Probe zeigt eine Form, nie eine Beziehung, und einen Join zu erfinden hieße, Tabelle, Schlüssel und Richtung auf einmal zu raten.

JSONB ist die abfragbare Form — ->, ->>, @>, und ein GIN-Index, wenn Sie ihn brauchen. Der Typ json bewahrt stattdessen den Originaltext, was nur zählt, wenn Sie das Dokument unverändert zurückgeben wollen.

Die Zeilen hineinbekommen

PostgreSQL hat kein bq load. COPY ist um zeilenweisen Text und CSV herum gebaut, nicht um ein Array von JSON-Objekten — die Tabelle oben ist also erst die halbe Arbeit.

Der naheliegende Weg ist jsonb_populate_recordset, das JSON-Schlüssel auf Spaltennamen abbildet — genau, Zeichen für Zeichen. Genau da liegt der Haken: Die Tabelle oben hat firstName in first_name umbenannt, also ließe dieser Weg die Spalte wortlos auf NULL.

Die Zuordnung, die zur Tabelle oben passt, ist ausdrücklich und sagt, welcher Schlüssel welche Spalte füllt:

INSERT INTO "root" ("id", "first_name", "score", "address")
SELECT (e->>'id')::bigint,
       e->>'firstName',
       (e->>'score')::double precision,
       e->'address'
FROM jsonb_array_elements(:'doc'::jsonb) AS e;

:'doc' ist eine psql-Variable mit dem Dokument — psql -v doc="$(cat data.json)" -f load.sql, solange die Datei in ein Argument passt. Darüber hinaus kopieren Sie den Text in eine einspaltige Zwischentabelle und wählen von dort; die Form der Abfrage ändert sich nicht.

Was eine Probe nicht sagen kann

• Kein Primärschlüssel. Eine id, die in einem Dokument eindeutig wirkt, verspricht nichts über das nächste.
• Kein Index, kein Vorgabewert, kein CHECK. Alles Aussagen über die Daten, keine Lesungen davon.
• NOT NULL, wo ein Wert stand, NULL für ein null und für einen Schlüssel, der manchen Objekten fehlt.

Datenschutz ab Werk

Alles läuft lokal in deinem Browser mit JavaScript. Deine Daten werden nie hochgeladen, wodurch das Tool auch für sensible Inhalte sicher ist und offline funktioniert.