JSON in PostgreSQL — incollalo in psql

Che cosa fa questo strumento

Un campione JSON diventa un 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
);

I nomi sono in snake_case e citati, il che li tiene esatti e permette a una parola riservata come "order" di essere una colonna senza cerimonie.

TEXT non ha lunghezza da inventare

PostgreSQL memorizza TEXT e VARCHAR(n) allo stesso modo; l’unica differenza è il vincolo. Poiché un campione non può dirti il limite vero — solo il valore più lungo che gli capita di contenere — nessun limite viene scritto. Aggiungere VARCHAR(40) più tardi è una decisione sul tuo dominio, e non costa nulla in spazio.

Per questo la pagina non ha l’equivalente della nota MySQL su VARCHAR(255): qui non c’è lunghezza su cui sbagliare.

Tutte le righe, non solo la prima

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

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

La tabella nasceva dal solo primo oggetto: la seconda riga del tuo stesso campione non aveva dove andare. Ora le colonne sono l’unione di tutti gli oggetti, e una chiave che manca ad alcune righe è nullable — perché lo è.

I tipi si fondono allo stesso modo. Quando la stessa chiave è un numero in un oggetto e una stringa in un altro, nessuna colonna scalare li prende entrambi: la colonna diventa JSONB. Un intero accanto a un decimale è un altro discorso — DOUBLE PRECISION li copre tutti e due, ed è quello che ottiene.

JSONB per ciò di cui SQL non ha una colonna

Oggetti e array annidati diventano JSONB. Nessuna tabella collegata, nessuna chiave esterna: un campione mostra una forma, mai una relazione, e inventare una join vorrebbe dire indovinare in un colpo tabella, chiave e verso.

JSONB è la forma interrogabile — ->, ->>, @>, e un indice GIN quando serve. Il tipo json semplice conserva invece il testo originale, il che conta solo se pensi di restituire il documento immutato.

Far entrare le righe

PostgreSQL non ha un bq load. COPY è costruito attorno al testo riga per riga e al CSV, non a un array di oggetti JSON: la tabella qui sopra è solo metà del lavoro.

La via ovvia è jsonb_populate_recordset, che accoppia le chiavi JSON ai nomi di colonna — in modo esatto, carattere per carattere. È lì l’inghippo: la tabella qui sopra ha rinominato firstName in first_name, quindi quella via lascerebbe la colonna a NULL senza dire nulla.

La mappatura che si accorda con la tabella qui sopra è esplicita, e dice quale chiave alimenta quale colonna:

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' è una variabile di psql che porta il documento — psql -v doc="$(cat data.json)" -f load.sql finché il file sta in un argomento. Oltre, copia il testo in una tabella di appoggio a una colonna e seleziona da lì; la forma della query non cambia.

Ciò che un campione non può dire

• Nessuna chiave primaria. Un id che sembra unico in un documento non promette nulla sul successivo.
• Nessun indice, nessun default, nessun CHECK. Sono affermazioni sul dato, non letture.
• NOT NULL dove c’era un valore, NULL per un null e per una chiave assente da alcuni oggetti.

Privato per progettazione

Tutto viene eseguito localmente nel browser con JavaScript. I tuoi dati non vengono mai caricati, quindi lo strumento è sicuro per contenuti sensibili e funziona anche offline.