A maior parte do JSON com o qual lido começa a vida como uma resposta de API e precisa acabar em um banco de dados No backend Toolz.dev, que roda em Express e TypeORM sobre PostgreSQL, perdi a conta das vezes que tive uma exportação JSON de um serviço que EU queria carregar em uma tabela para uma consulta rápida, uma semente de migração ou uma reprodução local de um bug Fazendo isso à mão é sombrio: você escreve um CREATE TABLE, então você digita à mão INSERT declarações, citando cada string, dobrando cada apóstrofo, lembrando quais campos são números e transformando chaves ausentes em NULL. Erre uma linha em duzentas e todo o lote não funciona.
Um conversor JSON para SQL faz esse trabalho mecânico para você. Você cola uma matriz de objetos, escolhe um dialeto de banco de dados e ele produz um CREATE TABLE com tipos de coluna sensíveis mais o INSERT instruções para carregar cada linha Este guia explica como funciona a conversão, as decisões que toma sobre tipos e citações e as situações reais em que salva uma tarde.
tl;dr: o Conversor JSON para SQL transforma uma matriz JSON de objetos em
CREATE TABLEeINSERTinstruções para MySQL, PostgreSQL, SQLite ou SQL padrão. Ele lê chaves de objeto como colunas, infere cada tipo de coluna a partir dos dados, escapa de strings, transforma chaves ausentes e nulos emNULL, e armazena objetos aninhados como JSON Tudo é executado em seu navegador, então uma resposta de API com dados reais nunca sai de sua máquina Para o inverso ou para fontes tabulares, veja CSV para SQL e json para csv.
O que faz um conversor JSON para SQL?
JSON e dados de modelos SQL de maneiras fundamentalmente diferentes JSON é uma árvore de objetos aninhados e arrays com tipos dinâmicos, por valor Uma tabela relacional é uma grade plana de colunas fixas e digitadas Converter de uma para outra é uma série de pequenas decisões, e o valor da ferramenta é tomar essas decisões de forma consistente.
Ele lê as chaves de seus objetos como o conjunto de colunas, trata cada objeto como uma linha, escolhe um tipo SQL para cada coluna e, em seguida, escreve as instruções A saída tem duas partes: uma opcional CREATE TABLE isso declara as colunas com seus tipos inferidos e o INSERT instruções que carregam os valores Você pode colar o resultado em um cliente de banco de dados, salvá-lo como parte de uma migração ou canalizá-lo para mysql, psql, ou sqlite3 da linha de comando.
A entrada normalmente é uma matriz de objetos, como [{ "id": 1, "name": "Ada" }, { "id": 2, "name": "Grace" }]. Um único objeto funciona também e se torna uma linha O que ele não aceitará é uma matriz de valores nus, como [1, 2, 3], porque não há chaves para transformar em colunas, e isso diz isso em vez de adivinhar.
Como os tipos de coluna são inferidos?
Esta é a parte que separa um conversor útil de um que despeja tudo em colunas de texto Para cada coluna, a ferramenta verifica o valor em cada linha e escolhe o tipo mais estreito que se encaixa em todas elas.
Se todo valor presente em uma coluna é um número inteiro, a coluna se torna um tipo inteiro Se os valores são números, mas alguns têm decimais, torna-se um tipo de ponto flutuante Se cada valor é true ou false, torna-se um booleano Se cada valor é um objeto ou array aninhado, torna-se um tipo JSON Qualquer outra coisa, incluindo uma coluna onde os valores discordam, cai de volta para texto assim o INSERT não é possível falhar em uma incompatibilidade de tipo.
null e as chaves ausentes são ignoradas ao decidir o tipo; elas só tornam a coluna anulável na prática Isso significa uma coluna que contém números na maioria das linhas e null em alguns ainda é digitado como um número, que é o que você deseja.
O nome do tipo concreto depende do dialeto. Aqui está como os tipos neutros mapeiam:
| Tipo inferido | mysql | PostgreSQL | SQLite | SQL padrão |
|---|---|---|---|---|
| Inteiro | INT |
INTEGER |
INTEGER |
INTEGER |
| decimal | DOUBLE |
DOUBLE PRECISION |
REAL |
REAL |
| Booleano | TINYINT(1) |
BOOLEAN |
INTEGER |
BOOLEAN |
| JSON aninhado | JSON |
JSONB |
TEXT |
TEXT |
| Texto | VARCHAR(255) |
TEXT |
TEXT |
TEXT |
Você pode desligar a inferência de tipo inteiramente, caso em que cada coluna se torna texto Isso é ocasionalmente o que você deseja quando você está carregando dados em uma tabela de preparação e planeja lançá-lo mais tarde.
Como ele lida com as partes estranhas dos dados reais?
JSON real raramente é uniforme, e a conversão tem que lidar com a ferramenta lida com três casos que tropeçam conversores ingênuos.
Primeiro, objetos com teclas diferentes. Se um objeto tiver um email campo e outro não, o conjunto de colunas é a união de cada chave que aparece, mantida na ordem vista pela primeira vez, e qualquer objeto que falte a uma chave obtém NULL nessa coluna A ferramenta avisa quando os objetos não são uniformes para que você não fique surpreso.
Segundo, objetos aninhados e arrays As colunas SQL são planas, então um valor como { "street": "1 Main St", "city": "London" } não pode se tornar várias colunas automaticamente sem você decidir a forma Em vez disso, o conversor serializa o valor aninhado de volta para o texto JSON e armazena-o em uma coluna JSON no MySQL, uma coluna JSONB no PostgreSQL ou uma coluna de texto em outro lugar A estrutura permanece intacta e você pode consultá-lo mais tarde com seu banco de dados' s funções JSON.
Terceiro, tipos mistos em uma coluna Se um campo é um número em um registro e uma string em outro, forçar um tipo numérico faria com que metade das inserções falhassem A ferramenta detecta a discordância e digita a coluna como texto, então cita cada valor como uma string literal para que o lote seja executado de forma limpa.
Como os valores são escapados?
O escape correto é a diferença entre um script que é executado e um que lança um erro de sintaxe na linha 47. as regras que o conversor aplica são as SQL padrão.
Os valores do texto são agrupados em aspas simples e qualquer aspa única dentro de um valor é duplicada O'Brien torna-se 'O''Brien'. Este é ANSI SQL string escapando e funciona em todos os quatro dialetos Números e booleanos são escritos sem aspas, porque um número citado é uma string na maioria dos motores Os booleanos renderizam como TRUE e FALSE no PostgreSQL e SQL padrão, e como 1 e 0 no MySQL e SQLite, combinando como cada mecanismo os armazena. null e as chaves ausentes tornam-se a palavra-chave não citada NULL.
Identificadores, ou seja, os nomes das tabelas e colunas, também são citados, com backticks no MySQL e aspas duplas em todos os outros lugares Isso mantém uma coluna nomeada order ou select de colidir com uma palavra reservada.
Como usar o JSON to SQL Converter
Passo 1: Cole seu JSON
Coloque em uma matriz de objetos ou pressione Load Sample para ver a forma esperada Cada objeto se torna uma linha.
Passo 2: Nomeie a tabela e escolha um dialeto
Digite o nome da tabela de destino e escolha MySQL, PostgreSQL, SQLite ou SQL padrão O dialeto controla o estilo de citação, os nomes dos tipos e como os booleanos e o JSON aninhado são escritos.
Passo 3: Defina as opções de saída
Decida se deve incluir o CREATE TABLE, se deve inferir tipos de colunas e se deve emitir uma linha múltipla INSERT ou uma instrução por linha Uma única inserção de várias linhas carrega mais rápido; uma instrução por linha produz um arquivo amigável para diferenças e permite executar linhas individualmente.
Passo 4: Copie o SQL
Leia as instruções geradas e copie-as Cole o script no seu cliente de banco de dados ou execute-o a partir da linha de comando para construir e carregar a tabela.
Casos de uso comuns
Semeando um banco de dados a partir de uma resposta de API
Você está construindo um recurso e precisa de linhas realistas para trabalhar contra Puxar uma resposta JSON da API upstream, colá-lo, e você tem um CREATE TABLE e inserções prontas para semear seu banco de dados local Sem digitação manual, sem citações perdidas.
Transformando uma configuração ou exportação em uma tabela
Um serviço exporta seus dados como uma matriz JSON Você deseja executar consultas SQL ad-hoc contra ele, em vez de escrever JavaScript para filtrar a matriz Convertê-lo para SQL, carregá-lo em SQLite, e consultá-lo com a linguagem construída para exatamente isso.
Construindo uma semente de migração
As migrações de framework geralmente precisam de uma etapa inicial que insira dados de referência Gerando o INSERT as instruções de um dispositivo JSON de bem conhecido fornecem um bloco estável e revisável para colar na migração, em vez de traduzir os dados manualmente.
Reproduzindo um bug de produção localmente
Um bug só aparece para um conjunto específico de registros Pegue o JSON para esses registros, converta-o em inserções, carregue-os em seu banco de dados local e reproduza o problema sem copiar um banco de dados de produção.
Inserção multi-linha vs por linha: qual escolher?
Uma inserção de várias linhas agrupa cada linha em uma instrução com muitas VALUES tuplas. é compacto e carrega mais rápido, porque o banco de dados analisa e planeja uma instrução em vez de centenas Escolha-o para carregamento em massa.
Uma instrução por linha é mais prolixa, mas tem duas vantagens Ela produz um arquivo onde cada linha é sua própria linha, que lê de forma limpa em uma revisão de código e diffs bem no controle de versão E se uma linha viola uma restrição, os outros ainda ter sucesso, o que é útil quando você está carregando dados confusos e quer ver quais linhas falham em vez de perder todo o lote Escolha-o para sementes que você vai cometer ou para dados que você não confia totalmente ainda.
Executando o SQL gerado a partir da linha de comando
Depois de ter o script, carregá-lo é um one-liner em cada mecanismo Para SQLite, salve a saída em um arquivo e pipe-lo em: sqlite3 mydata.db < insert.sql cria a tabela e carrega as linhas em um novo arquivo de banco de dados. Para PostgreSQL, psql -d mydb -f insert.sql executa todo o script contra o banco de dados nomeado. Para MySQL, mysql -u user -p mydb < insert.sql faz o mesmo Como o gerador cita identificadores e escapa de valores para o dialeto que você escolheu, o script é executado sem os consertos manuais que o SQL escrito à mão geralmente precisa.
Se você gerou uma instrução por linha em vez de uma única inserção de várias linhas, vale a pena fazer o empacotamento da carga em uma transação Adicionando BEGIN; no topo e COMMIT; na parte inferior transforma centenas de inserções individuais em uma operação atômica, que é mais rápida e segura: se uma linha posterior falhar em uma restrição, toda a carga rola para trás em vez de deixar a mesa meio povoada Para uma inserção de várias linhas isso já é efetivamente atômico, uma vez que é uma única declaração.
Desempenho e limites com grandes matrizes
A conversão em si é linear no tamanho da entrada e é executada inteiramente no seu navegador, então alguns milhares de linhas se convertem instantaneamente Os limites práticos não estão na ferramenta, mas no que você faz com a saída Uma única linha múltipla INSERT com dezenas de milhares de tuplas podem exceder um banco de dados' s tamanho máximo da instrução ou limite de pacotes, MySQL's max_allowed_packet sendo o único que as pessoas acertaram primeiro Se você estiver carregando uma matriz muito grande, ou mude para uma instrução por linha para que cada uma seja pequena, ou divida a entrada em lotes de algumas centenas de linhas e converta-as separadamente.
Há também uma ressalva de precisão que vale a pena conhecer Os números JSON são analisados pelo navegador & #39; s Motor JSON em flutuadores padrão de precisão dupla, portanto, um número inteiro maior que cerca de nove quatrilhões, além do JavaScript & #39; s faixa inteira segura, pode perder precisão antes que a ferramenta o veja Isso é uma propriedade da análise JSON em todos os ambientes, não do conversor, mas significa que um ID numérico gigante é mais seguro representado como uma string no JSON de origem, que a ferramenta armazenará como texto e preservará exatamente.
Onde isso se encaixa com outras ferramentas
JSON para SQL é uma direção de uma pequena família Quando sua fonte é uma planilha ou exportação CSV em vez de JSON, CSV para SQL faz o mesmo trabalho a partir de entrada tabular com o mesmo tipo de inferência e escape Quando você precisa dos dados como um arquivo plano em vez de um banco de dados, json para csv achata a matriz em linhas e csv para json volta para o outro lado Antes de converter qualquer coisa, formatador json irá imprimir e validar o JSON para que você pegue uma vírgula perdida antes que ela se torne um erro de análise. E assim que você tiver o SQL, formatador sql irá recuar e alinhar as instruções de um arquivo de migração que você tem orgulho de confirmar.
Para o raciocínio por trás de fazer tudo isso no navegador, em vez de carregar dados para um serviço conversor, o Privacidade de dados em ferramentas online o guia apresenta o modelo do lado do cliente e o Kit de ferramentas para desenvolvedores web coleta os utilitários que combinam bem com o trabalho de conversão de dados.
FAQ
Como faço para converter JSON para SQL?
Cole uma matriz JSON de objetos, defina o nome da tabela e escolha seu dialeto SQL A ferramenta lê as teclas de objeto como nomes de coluna, infere cada tipo de coluna e gera uma TABELA CREATE mais instruções INSERT que você pode copiar e executar em seu banco de dados.
Que forma JSON aceita?
Uma matriz de objetos é a entrada normal, onde cada objeto é uma linha Um único objeto também funciona e produz uma linha Matrizes de primitivas ou formas profundamente incompatíveis são rejeitadas com um erro explicando o que é esperado.
Quais bancos de dados SQL são suportados?
O conversor emite instruções para MySQL, PostgreSQL, SQLite e SQL padrão. O dialeto escolhido controla a citação do identificador, o tipo e a sintaxe booleana e o tipo JSON nativo, portanto o script é executado nesse banco de dados sem edições.
Como os tipos de coluna são decididos?
Cada coluna é verificada em relação a cada objeto Se todos os valores presentes são números inteiros torna-se um tipo inteiro, todos os números tornam-se um tipo decimal, todos os valores verdadeiros ou falsos tornam-se um tipo booleano, todos os objetos ou arrays tornam-se um tipo JSON, e qualquer coisa misturada torna-se texto.
O que acontece com objetos aninhados e arrays?
Eles são serializados de volta para o texto JSON e armazenados em uma coluna JSON no MySQL, uma coluna JSONB no PostgreSQL ou uma coluna TEXT no SQLite e SQL padrão Isso mantém a estrutura intacta para que você possa consultá-la com suas funções JSON do banco de dados mais tarde.
E se os meus objetos tiverem chaves diferentes?
Isso está bem As colunas são a união de cada chave que aparece, mantida na ordem vista pela primeira vez Qualquer objeto faltando uma dessas chaves obtém NULL nessa coluna, e a ferramenta avisa quando os objetos não são uniformes.
Como as strings e as citações são escapadas?
Os valores de texto são embrulhados em aspas simples e qualquer aspa única dentro de um valor é duplicada, que é o escape padrão do SQL Os números e booleanos são deixados sem aspas, nulo torna-se NULO e os identificadores são citados com backticks para MySQL ou aspas duplas para os outros dialetos.
Meu JSON foi carregado em algum lugar?
No. Parsing e geração de SQL são executados como JavaScript no seu navegador Nada é transmitido, registrado ou armazenado, e a ferramenta continua funcionando offline uma vez carregada, o que você pode confirmar assistindo a guia de rede enquanto você converte.
