JSON para SQL: comandos INSERT, projeto de tabelas e armadilhas de mapeamento de tipos

Resumo da ópera: decida qual entidade de negócio uma linha representa antes de projetar as tabelas e depois escreva VALUES múltiplos explícitos dentro de uma única transação; resolva nomes de campos e mapeamento de tipos antes da importação em vez de remendá-los em SQL.

O JSON descreve forma; o SQL também descreve restrições. Quando você transporta o primeiro para o segundo, toda restrição que você não escreveu volta depois como dado sujo.

1. Decida primeiro: uma tabela ou um registro por linha JSON

Olhe a camada mais externa dos dados antes de projetar qualquer tabela. Erre nessa decisão e todo INSERT depois dela precisará ser reescrito.

Há apenas um critério: qual entidade de negócio uma linha representa? Se uma linha é um pedido, os itens de linha precisam viver em sua própria tabela. Se uma linha é um usuário, as tags pertencem a uma tabela filha ou a uma coluna JSON, não a uma string unida por vírgulas.

2. De um array para INSERT: o menor modelo executável

A forma mais segura sempre lista os nomes das colunas explicitamente e nunca depende da ordem das colunas:

复制CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

INSERT INTO users (id, name, email) VALUES
  (1, 'Ada', 'ada@example.com'),
  (2, 'Linus', NULL);

Três detalhes importam:

• Escreva todos os nomes de coluna. Quando o schema ganhar uma coluna depois, nada se desloca para o campo errado.
• VALUES com várias linhas funciona no SQLite, PostgreSQL e MySQL, e economiza um grande número de idas e voltas em comparação com um laço de inserts de uma linha.
• NULL é uma palavra-chave; não a coloque entre aspas. Escrever 'NULL' armazena uma string de quatro caracteres.

Se as chaves JSON não corresponderem aos nomes das colunas (userId versus user_id), adicione uma camada de mapeamento. Não adivinhe com COALESCE dentro do SQL. Corrigir nomes de campo na camada de dados é bem mais barato do que remendá-los nas consultas.

3. Objetos aninhados: uma tabela pai mais uma tabela filha

Pedidos são o caso clássico de um-para-muitos:

复制INSERT INTO orders (id, customer_name, total_cents) VALUES
  (1001, 'Ada', 12900);

INSERT INTO order_items (order_id, sku, quantity) VALUES
  (1001, 'A-1', 2),
  (1001, 'B-7', 1);

Isso corresponde ao JSON {"id":1001,"customer":"Ada","total":129.00,"items":[{"sku":"A-1","quantity":2},{"sku":"B-7","quantity":1}]}.

Pontos-chave:

• O comprimento do array items determina o número de linhas filhas; a tabela pai recebe exatamente uma linha.
• Armazene dinheiro como centavos inteiros (total_cents), nunca como FLOAT.
• Dê à tabela filha uma chave estrangeira order_id e confirme os dois inserts em uma transação, caso contrário o pai pode existir enquanto seus itens estão ausentes.
• Se os dados forem somente leitura e ninguém filtrar por item de linha, uma coluna JSON é aceitável (jsonb no PostgreSQL, JSON no MySQL, ou TEXT no SQLite com as funções json1), mas aceite o custo: sem indexação e consultas desconfortáveis.

Ache objetos aninhados fixos como user.address em colunas (address_city, address_zip). Divida em uma tabela apenas quando o aninhamento puder continuar crescendo; dividir uma forma fixa só acrescenta JOINs.

4. NULL versus campos ausentes: o SQL não tem "chave ausente"

O JSON permite que uma chave esteja totalmente ausente. As colunas SQL são fixas. Após uma importação, os dois casos parecem idênticos — NULL — mesmo que possam significar coisas diferentes:

Dois erros comuns:

1. Usar string vazia em vez de NULL. Depois, WHERE email IS NULL não pega essas linhas, e sua métrica de "usuários sem e-mail" subconta silenciosamente.
2. Testar com col = NULL. Na lógica de três valores do SQL, NULL = NULL avalia para NULL, não TRUE. Você precisa escrever IS NULL:

复制SELECT COUNT(*) FROM users WHERE email IS NULL;

Se você precisa preservar se uma chave estava presente, adicione uma coluna marcadora booleana em vez de inferir a partir de NULL. O contrato da API também deve declarar isso; veja Projeto de API JSON e nomes de campos.

5. Aspas e escaping: nunca concatene strings SQL

Strings JSON podem conter aspas simples, aspas duplas, barras invertidas, quebras de linha e até \u0000. Com strings montadas à mão, um escape esquecido desloca o resto do comando e, no pior caso, vira injeção de SQL.

No SQL padrão, uma aspa simples é escapada duplicando-a:

复制INSERT INTO notes (id, content) VALUES (1, 'It''s a ''quoted'' value');

Código de produção deve usar binding de parâmetros e deixar o driver cuidar do escaping e dos tipos:

复制// PostgreSQL usa $1/$2, MySQL usa ?; este exemplo usa node-postgres
await client.query('INSERT INTO users (id, name) VALUES ($1, $2)', [row.id, row.name]);

Escape à mão apenas quando precisar exportar um arquivo .sql, e então no mínimo:

• substitua ' por '';
• se quebras de linha forem preservadas, confirme que o banco de destino aceita literais de string multilinha;
• remova \u0000, já que a maioria dos bancos rejeita NUL dentro de strings;
• o MySQL trata a barra invertida como caractere de escape por padrão, então \ precisa virar \\, ou mude para o modo padrão.

Aspas duplas do JSON não precisam de escape dentro de uma string SQL entre aspas simples. Adicionar uma camada extra de escaping é assim que barras invertidas indesejadas acabam nos seus dados.

6. Mapeamento de tipos: onde os seis valores JSON caem no SQL

O JSON tem apenas string, número, booleano, null, objeto e array. O SQL tem um sistema de tipos bem mais fino. Os erros geralmente acontecem em precisão e semântica, não em sintaxe:

Booleanos escondem o bug mais sutil: false em JSON vira 0 no TINYINT(1) do MySQL, as idas e voltas parecem bem e ninguém percebe. Mas no momento em que algum código faz if (value) na string "false", ela é avaliada como verdadeira. Scripts de conversão devem chamar Boolean(value) explicitamente.

O JSONB do PostgreSQL analisa e deduplica chaves, mantendo a última ocorrência, o que convém para consultas; JSON preserva o texto original e a ordem das chaves. O JSON do SQLite é apenas TEXT mais um conjunto de funções, sem restrição real de tipo. Antes de escolher, pergunte se esta tabela precisa filtrar por campos dentro do JSON.

7. Inserções em massa e transações: lotes, commit, torne reexecutável

Conforme o volume cresce, três coisas decidem se uma importação permanece controlável:

1. Lote. Um laço de N comandos INSERT de uma linha custa N idas e voltas de rede. Combine 500 a 1000 linhas em um VALUES de várias linhas, ou use o carregador em massa do próprio banco (COPY do PostgreSQL, LOAD DATA do MySQL, inserts de várias linhas dentro de uma transação SQLite).
2. Use uma transação. Tudo ou nada, para que uma tabela pela metade nunca sobreviva a um erro:

复制BEGIN;
INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Linus');
INSERT INTO order_items (order_id, sku, quantity) VALUES (1, 'A-1', 2);
COMMIT;

3. Torne idempotente. Scripts de importação são reexecutados constantemente. Use um upsert em vez de excluir e depois inserir:

ON CONFLICT exige uma restrição de unicidade ou chave primária, caso contrário nunca dispara. Quando não houver chave natural, crie um índice único em uma chave de negócio como email. Quando não houver chave alguma, adicione um id estável no lado JSON — nunca conte com um id autoincremental para idempotência, porque uma reexecução inserirá duplicatas.

8. Diferenças entre dialetos em resumo

Para manter um arquivo SQL funcionando nos três, evite pelo menos ON CONFLICT, crases e SERIAL. Para projetos entre bancos diferentes, gere schema e comandos de upsert separadamente; não espere que um único script sirva para tudo.

9. Três verificações após a importação

Antes de excluir o arquivo de origem, verifique três coisas:

1. Contagem de linhas. SELECT COUNT(*) FROM users; deve ser igual ao comprimento do array JSON. Uma linha a menos geralmente significa que o registro final foi truncado.
2. A coluna com maior taxa de NULL. Isso aponta para nomes de chave inconsistentes ou aninhamento não expandido. Volte e verifique se a origem usa tanto userId quanto user_id.
3. Dinheiro e identificadores longos. Escolha os valores máximo e mínimo e confirme que nem a conversão para ponto flutuante nem a para inteiro os truncaram.

Se você precisa primeiro entender a forma tabular, use a abordagem de achatamento em Conversão entre JSON, CSV e dados tabulares. Se o público são colegas que só querem ler os dados, as quatro formas de converter JSON para Excel dão menos trabalho do que levantar um banco de dados.

Por que os números perdem precisão depois de importar JSON para SQL?

Inteiros acima de 2^53 já perderam precisão durante o JSON.parse do JavaScript, e escrevê-los em BIGINT não a restaura. Esses ids devem ser strings no JSON e convertidos para BIGINT ou TEXT durante a importação.

O que devo usar como chave primária quando o JSON não tem id?

Deixe o banco gerar uma, ou crie um UUID estável antes de importar. Nunca use o índice do array como id: uma reexecução reordena os dados e aponta as linhas erradas para os registros errados. Um índice único em uma chave de negócio (email, número de pedido) também viabiliza upserts.

O que fazer quando um INSERT em massa encontra um conflito de chave única?

Substitua o INSERT simples pelo upsert correspondente ao seu dialeto e confirme que a coluna em conflito tem uma restrição de unicidade. Excluir primeiro e inserir depois também funciona, mas precisa acontecer dentro de uma transação e você aceita uma breve janela sem linhas.

Arrays aninhados sempre precisam de uma tabela filha?

Não. Para exibição somente leitura sem consultas por subitem, uma coluna JSON é mais simples. No momento em que você precisar filtrar ou agregar por tag ou SKU, uma tabela filha com índices é a escolha sustentável. O fator decisivo é como você consulta, não como os dados são moldados.