JSON para PostgreSQL — cole direto no psql

O que esta ferramenta faz

Uma amostra JSON vira um 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
);

Os nomes vão em snake_case e entre aspas, o que os mantém exatos e deixa uma palavra reservada como "order" ser coluna sem cerimônia.

TEXT não tem comprimento a inventar

O PostgreSQL guarda TEXT e VARCHAR(n) do mesmo jeito; a única diferença é a restrição. Como uma amostra não sabe o limite real — só o valor mais longo que por acaso contém —, nenhum limite é escrito. Acrescentar VARCHAR(40) depois é decisão sobre o seu domínio, e não custa nada em armazenamento.

Por isso esta página também não tem o equivalente da nota do MySQL sobre VARCHAR(255): aqui não há comprimento em que errar.

Todas as linhas, não só a primeira

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

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

A tabela era construída só com o primeiro objeto: a segunda linha da sua própria amostra não tinha para onde ir. Agora as colunas são a união de todos os objetos, e uma chave que falta em algumas linhas é nullable — porque é.

Os tipos se fundem do mesmo jeito. Quando a mesma chave é um número num objeto e uma string noutro, nenhuma coluna escalar aceita os dois: a coluna vira JSONB. Um inteiro ao lado de um decimal é outra coisa — DOUBLE PRECISION cobre ambos, e é o que ele recebe.

JSONB para aquilo que o SQL não tem coluna

Objetos e arrays aninhados viram JSONB. Sem tabela ligada nem chave estrangeira: uma amostra mostra uma forma, nunca uma relação, e inventar uma junção seria adivinhar de uma vez uma tabela, uma chave e um sentido.

JSONB é a forma consultável — ->, ->>, @>, e um índice GIN quando precisar. O tipo json simples guarda o texto original, o que só importa se você planeja devolver o documento inalterado.

Fazer as linhas entrarem

O PostgreSQL não tem bq load. O COPY é construído em torno de texto linha a linha e de CSV, não de um array de objetos JSON: a tabela acima é só metade do trabalho.

O caminho óbvio é o jsonb_populate_recordset, que casa chaves JSON com nomes de coluna — de forma exata, caractere por caractere. É aí que está a armadilha: a tabela acima renomeou firstName para first_name, então esse caminho deixaria a coluna em NULL sem dizer nada.

O mapeamento que combina com a tabela acima é explícito, e diz qual chave alimenta qual coluna:

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' é uma variável do psql que carrega o documento — psql -v doc="$(cat data.json)" -f load.sql enquanto o arquivo couber num argumento. Além disso, copie o texto para uma tabela de passagem de uma coluna e selecione dali; a forma da consulta não muda.

O que uma amostra não consegue dizer

• Sem chave primária. Um id que parece único num documento não promete nada sobre o seguinte.
• Sem índice, sem valor padrão, sem CHECK. São afirmações sobre o dado, não leituras dele.
• NOT NULL onde havia valor, NULL para um null e para uma chave ausente em alguns objetos.

Privado por padrão

Tudo é executado localmente no seu navegador com JavaScript. Os seus dados nunca são enviados para um servidor, o que torna a ferramenta segura para conteúdo sensível e funciona offline.