JSON a PostgreSQL — pégalo en psql

Qué hace esta herramienta

Una muestra JSON pasa a ser 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
);

Los nombres van en snake_case y entrecomillados, lo que los mantiene exactos y deja que una palabra reservada como "order" sea una columna sin ceremonias.

TEXT no tiene longitud que inventar

PostgreSQL almacena TEXT y VARCHAR(n) de la misma manera; la única diferencia es la restricción. Como una muestra no puede decirte el límite real —solo el valor más largo que contiene—, no se escribe ningún límite. Añadir VARCHAR(40) más tarde es una decisión sobre tu dominio, y no cuesta nada en almacenamiento.

Por eso esta página tampoco tiene el equivalente de la nota de MySQL sobre VARCHAR(255): aquí no hay longitud en la que equivocarse.

Todas las filas, no solo la primera

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

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

La tabla se construía solo con el primer objeto: la segunda fila de tu propia muestra no tenía adónde ir. Ahora las columnas son la unión de todos los objetos, y una clave que falta en algunas filas es nullable, porque lo es.

Los tipos se funden igual. Cuando una misma clave es un número en un objeto y una cadena en otro, ninguna columna escalar acepta ambos: la columna es JSONB. Un entero junto a un decimal es otra historia — DOUBLE PRECISION cubre los dos, y eso es lo que recibe.

JSONB para lo que SQL no tiene columna

Los objetos y arrays anidados pasan a JSONB. Sin tabla relacionada ni clave foránea: una muestra enseña una forma, nunca una relación, e inventar una unión sería adivinar de golpe una tabla, una clave y un sentido.

JSONB es la forma consultable —->, ->>, @>, y un índice GIN cuando haga falta—. El tipo json simple conserva el texto original, lo que solo importa si piensas devolver el documento sin cambios.

Meter las filas

PostgreSQL no tiene bq load. COPY está construido en torno al texto línea por línea y al CSV, no a un array de objetos JSON: la tabla de arriba es solo la mitad del trabajo.

La vía evidente es jsonb_populate_recordset, que empareja claves JSON con nombres de columna — de forma exacta, carácter a carácter. Ahí está la trampa: la tabla de arriba renombró firstName a first_name, así que esa vía dejaría la columna en NULL sin decir nada.

El mapeo que encaja con la tabla de arriba es explícito, y dice qué clave alimenta qué columna:

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' es una variable de psql que lleva el documento — psql -v doc="$(cat data.json)" -f load.sql mientras el archivo quepa en un argumento. Más allá, copia el texto a una tabla de paso de una columna y selecciona desde ahí; la forma de la consulta no cambia.

Lo que una muestra no puede decir

• Sin clave primaria. Un id que parece único en un documento no promete nada del siguiente.
• Ni índice, ni valor por defecto, ni CHECK. Son afirmaciones sobre el dato, no lecturas de él.
• NOT NULL donde había un valor, NULL para un null y para una clave ausente en algunos objetos.

Privado por diseño

Todo se ejecuta localmente en tu navegador con JavaScript. Tus datos nunca se suben a un servidor, lo que hace que la herramienta sea segura para contenido sensible y funcione sin conexión.