Converter JSON para SQL

A saída é uma sequência de comandos INSERT e nada além disso. É um limite deliberado, não uma funcionalidade faltando. O JSON diz que um valor é um número; não diz se a coluna é inteiro ou decimal com duas casas, se aceita nulo, qual campo é a chave primária, quais são as chaves estrangeiras nem quantos caracteres o texto pode ter. Essas são decisões que um esquema existe para registrar, e um conversor que chutasse produziria um CREATE TABLE que você teria que ler linha por linha antes de confiar — mais trabalho do que escrever na mão.

Na prática isso significa que a tabela já existe antes de você rodar o arquivo. Converta uma vez, leia a lista de colunas no primeiro comando, escreva o DDL correspondente, depois rode. É um ciclo de dois minutos, e coloca as decisões de esquema onde elas pertencem, numa migração que dá para revisar, em vez de num arquivo gerado que por acaso funciona na primeira amostra.

Não há de onde mais isso viria. Um documento JSON não tem nome próprio, então o arquivo enviado fornece um: pedidos.json gera INSERT INTO pedidos, e exportacao-2024.json gera INSERT INTO exportacao_2024, porque um hífen não é permitido num identificador sem aspas e um dígito no começo também não é.

A consequência é que renomear o arquivo antes de converter é o jeito mais barato de controlar a saída. Fazer isso depois significa um localizar-e-substituir em todo comando do arquivo, e a substituição precisa ter cuidado para não pegar um valor que por acaso contenha a mesma palavra. Trinta segundos no gerenciador de arquivos ganham de uma expressão regular em cem mil linhas.

Uma tabela relacional é plana e o JSON não é, então um objeto aninhado é achatado no nome da coluna: um registro com um objeto cliente contendo uma cidade produz uma coluna chamada cliente_cidade. O caminho é preservado, o ponto vira sublinhado porque um ponto não é permitido num identificador, e nada é descartado.

Um nível de aninhamento produz uma tabela que qualquer um ficaria feliz de declarar. Dois níveis ainda costuma ser razoável. Além disso, a lista de colunas gerada é um sinal, não um resultado: uma estrutura JSON com quatro níveis de profundidade está descrevendo relacionamentos, e o esquema que combina com isso é várias tabelas com chaves entre elas. Carregar tudo numa tabela larga só funciona e deixa toda consulta seguinte mais difícil do que precisava ser.

Um registro com três etiquetas produz etiquetas_0, etiquetas_1 e etiquetas_2. Todo valor sobrevive e o formato está errado de um jeito que só piora: o próximo registro com cinco etiquetas alarga a tabela em mais duas colunas, e não existe consulta sensata sobre «a segunda etiqueta».

Um banco relacional tem uma resposta normal para isso, e é uma segunda tabela — uma linha por etiqueta, com o identificador do pai ao lado. Chegar lá a partir de uma exportação JSON significa converter duas vezes com o vetor extraído no meio, ou carregar a tabela larga como etapa intermediária e normalizar com uma consulta. As duas opções dão mais trabalho que uma conversão só, e as duas produzem um esquema que dá para consultar de verdade. Se o banco de destino tem um tipo de coluna JSON nativo, guardar o vetor inteiro numa coluna é a terceira opção, e é a certa quando o vetor é carregado como dado, não consultado campo a campo.

Registros JSON numa mesma exportação não são obrigados a concordar entre si. Uma API omite campo sem valor, então mil registros podem apresentar uma dúzia de conjuntos diferentes de chaves. A conversão reconcilia isso juntando a união de toda chave do arquivo e escrevendo NULL onde um registro não tem nada.

Essa uniformidade é o que torna a saída segura para rodar como lote: a lista de colunas é idêntica em todo comando, então uma tabela que aceita a primeira linha aceita todas elas. Isso também explica uma diferença que as pessoas notam: converter dez registros para um teste pode gerar menos colunas do que converter o arquivo inteiro, porque a amostra por acaso não continha os campos mais raros. Escreva o DDL a partir de uma conversão do arquivo inteiro, nunca de uma amostra.

Número sai sem aspas, booleano como TRUE e FALSE, um null do JSON como NULL, e o resto como string entre aspas simples com aspas simples internas duplicadas. Duplicar é a forma padrão do SQL, e é o que todo banco entende, então um sobrenome como D'Ávila carrega correto em qualquer lugar.

A barra invertida é escrita sem tocar, o que está correto sob o padrão e não é como o MySQL lê uma string por padrão: lá, uma barra invertida inicia uma sequência de escape, a menos que NO_BACKSLASH_ESCAPES esteja ativo. Dado contendo caminho de arquivo do Windows, expressão regular ou LaTeX vai carregar diferente no MySQL e no Postgres. Se isso descreve a sua exportação, ajuste o modo da sessão antes de rodar o arquivo, e confira uma linha afetada depois em vez de supor.

É aqui que uma origem JSON ganha de uma delimitada. Um código de produto «007» é uma string no JSON, então é escrito como string com aspas e chega ao banco com os zeros à esquerda intactos. Um CSV do mesmo dado não tem tipo nenhum, e cada ferramenta da cadeia tem a chance de decidir que 007 é o número sete.

A mesma proteção cobre identificador numérico longo que foi exportado como string. Onde foi exportado como número do JSON, porém, vale o teto de ponto flutuante de sempre — qualquer coisa além de dezesseis dígitos já perdeu precisão antes de esta conversão sequer ver o dado, e nenhum cuidado no gerador consegue trazer isso de volta. Se a exportação é sua para moldar, exportar identificador como string é a correção, e é uma correção na origem, não aqui.

A saída é um comando por registro, sem envolver numa transação e sem agrupar vários valores num só VALUES. Rodar cinquenta mil comandos separados por um cliente é lento, porque cada um é uma ida e volta. Envolver o arquivo inteiro em BEGIN e COMMIT é uma linha em cada ponta e costuma ser a maior melhoria isolada disponível.

Os comandos em si são deliberadamente simples e portáveis: sem aspas específicas de um banco, sem cláusula ON CONFLICT, sem prefixo de esquema. Adicionar o que o seu banco quer é um localizar-e-substituir em INSERT INTO — um nome de esquema, um ON CONFLICT DO NOTHING para uma carga que pode rodar de novo — e começar pela forma mais simples possível é o que torna essas edições previsíveis.

Para um arquivo de dados iniciais, um teste ou alguns milhares de linhas, comandos são o formato conveniente: são legíveis, podem ser versionados num repositório, e rodam em qualquer lugar que um cliente consiga conectar. É para isso que esta página foi escrita.

Passado um certo tamanho a conta muda. Todo banco tem um caminho de carga em massa — COPY no Postgres, LOAD DATA no MySQL, um importador numa ferramenta cliente — que lê um arquivo delimitado bem mais rápido do que executa inserções individuais, e para uma carga de um milhão de linhas a diferença é minutos contra horas. Nesse caso, converta a mesma exportação para CSV ou TSV, ou para NDJSON se o destino é um data warehouse em vez de um banco relacional.

Os comandos são gerados por JavaScript nesta aba. Nada é enviado, não existe conta ou fila, e o plano gratuito aceita até 100 MB, com a memória do computador como o teto real, porque o documento inteiro é analisado antes de qualquer coisa ser escrita.

Isso não é um benefício incidental para este par. O que está prestes a entrar num banco de dados é, por definição, dado que alguém decidiu guardar — cliente, pedido, transação, conta. Passar isso por um conversor de terceiros só para duplicar aspas seria uma troca ruim, e aqui não há troca nenhuma a fazer.