table - sql server process json




Quando posso salvar dados JSON ou XML em uma tabela SQL (6)

A "regra de ouro" que eu uso, de uma maneira ondulatória, é que, se eu precisar do JSON em seu formato bruto, não há problema em armazenar. Se eu tiver que fazer um ponto especial de analisá-lo, não é.

Por exemplo, se estou criando uma API que envia JSON bruto, e por qualquer motivo esse valor não vai mudar, não há problema em armazená-lo como JSON bruto. Se eu tiver que analisá-lo, alterá-lo, atualizá-lo, etc ... não muito.

Ao usar SQL ou MySQL (ou qualquer banco de dados relacional) - eu entendo que salvar os dados em colunas regulares é melhor para indexação e outras finalidades ...

O problema é carregar e salvar dados JSON às vezes é muito mais simples. e facilita o desenvolvimento.

Existem "regras de ouro" para salvar dados JSON brutos no banco de dados?

é prática absolutamente errada fazer isso?

RESUMO

Respostas muito boas foram dadas, mas sem dúvida a mais bem organizada é a resposta de @Shnugo, que merece a recompensa.

Também gostaria de salientar as respostas dadas por @Gordon Linoff e @Amresh Pandey para explicar outros casos de uso especiais.

Graças a Deus, e bom trabalho a todos!


A pergunta que você deve fazer é:

Estou vinculado a usar apenas esse banco de dados?

FAZ

  1. Se você pode usar um banco de dados diferente para armazenar JSON, use uma solução de armazenamento de documentos como CouchDB, DynamoDB ou MongoDB.
  2. Use a capacidade do banco de dados de armazenamento de documentos para indexar e pesquisar dados hierárquicos.
  3. Use um banco de dados relacional para seus dados relacionais.
  4. Use um banco de dados relacional para geração de relatórios, data warehousing e data mining.

NÃO

  1. Armazene JSON como sequência, se possível.
  2. Tente criar um tamanho máximo de dados JSON.
  3. Use varchar para armazenar JSON (use texto / blob, se necessário).
  4. Tente pesquisar valores JSON armazenados no JSON.
  5. Preocupe-se em escapar do JSON para armazenar como string.

Isso é muito longo para um comentário.

Se estivesse "absolutamente errado", a maioria dos bancos de dados não o suportaria. Ok, a maioria dos bancos de dados suporta vírgulas na cláusula FROM e eu vejo isso como "absolutamente errado". Mas o suporte ao JSON é um novo desenvolvimento, não um "recurso" compatível com versões anteriores.

Um caso óbvio é quando a estrutura JSON é simplesmente um BLOB que é passado de volta para o aplicativo. Portanto, não há debate - além da sobrecarga de armazenamento do JSON, desnecessariamente detalhada para dados estruturados com campos comuns em todos os registros.

Outro caso é o caso de colunas "esparsas". Você tem linhas com muitas colunas possíveis, mas elas variam de linha para linha.

Outro caso é quando você deseja armazenar registros "aninhados" em um registro. JSON é poderoso.

Se o JSON tiver campos comuns nos registros que você deseja consultar, geralmente é melhor colocá-los nas colunas apropriadas do banco de dados. No entanto, os dados são complicados e existe um lugar para formatos como JSON.


Json's não são ótimos em db's relacionais. Se você desdobrar o json em colunas e armazená-lo em um banco de dados, é ótimo, mas armazenar um json como um blob é o próximo a usá-lo como sistema de arquivamento de dados.

Pode haver vários motivos para não desdobrar um json e armazená-lo em uma única coluna, mas a decisão teria sido tomada, pois os valores nesse campo json não seriam usados ​​para nenhuma consulta (ou os valores já foram desdobrados em colunas).

Além disso, a maior parte do processamento do json, se todo o campo foi consultado, estaria fora do ambiente sql, pois o sql não se destina apenas ao processamento do json. A verdadeira questão então se torna: onde eu guardo esse json, deixo que seja como arquivos simples e quando necessário os consulta através de algum outro sistema (spark / hive / etc).

Eu concordo com o seu artista DB, não use RDBMS para arquivamento. Existem opções mais baratas. Além disso, o json blobs pode ficar enorme e começar a atolar o espaço em disco do banco de dados com o tempo.


O novo SQL Server fornece funções para o processamento de texto JSON. As informações formatadas como JSON podem ser armazenadas como texto nas colunas padrão do SQL Server e o SQL Server fornece funções que podem recuperar valores desses objetos JSON.

    DROP TABLE IF EXISTS Person

 CREATE TABLE Person 
 ( _id int identity constraint PK_JSON_ID primary key,
 value nvarchar(max)
 CONSTRAINT [Content should be formatted as JSON]
 CHECK ( ISJSON(value)>0 )
 )

Essa estrutura simples é semelhante à coleção NoSQL padrão que você pode criar nos bancos de dados NoSQL (por exemplo, Azure DocumentDB ou MongoDB), onde você só tem uma chave que representa o ID e o valor que representa JSON.

Observe que o NVARCHAR não é apenas um texto simples. O SQL Server possui um mecanismo interno de compactação de texto que pode compactar de forma transparente os dados armazenados no disco. A compactação depende do idioma e pode subir até 50%, dependendo dos seus dados (consulte Compactação UNICODE).

A principal diferença entre o SQL Server e outros bancos de dados NoSQL simples é que o SQL Server permite usar o modelo de dados híbrido, no qual é possível armazenar vários objetos JSON na mesma "coleção" e combiná-los com colunas relacionais regulares.

Como exemplo, imagine que sabemos que todas as pessoas da sua coleção terão Nome e Sobrenome e que você pode armazenar informações gerais sobre a pessoa como um objeto JSON e números de telefone / endereços de email como objetos separados. No SQL Server 2016, podemos criar facilmente essa estrutura sem nenhuma sintaxe adicional:

DROP TABLE IF EXISTS Person

CREATE TABLE Person (

 PersonID int IDENTITY PRIMARY KEY,

 FirstName nvarchar(100) NOT NULL,

 LastName nvarchar(100) NOT NULL,

 AdditionalInfo nvarchar(max) NULL,

 PhoneNumbers nvarchar(max) NULL,

 EmailAddresses nvarchar(max) NULL
 CONSTRAINT [Email addresses must be formatted as JSON array]
 CHECK ( ISJSON(EmailAddresses)>0 )

 )

Em vez de um único objeto JSON, você pode organizar seus dados nesta "coleção". Se você não deseja verificar explicitamente a estrutura de cada coluna JSON, não precisa adicionar restrição de verificação JSON em todas as colunas (neste exemplo, eu adicionei a restrição CHECK apenas na coluna EmailAddresses).

Se você comparar essa estrutura com a coleção NoSQL padrão, poderá perceber que terá acesso mais rápido a dados com tipos fortes (Nome e Sobrenome). Portanto, essa solução é uma boa opção para modelos híbridos, nos quais é possível identificar algumas informações que são repetidas em todos os objetos e outras informações variáveis ​​podem ser armazenadas como JSON. Dessa forma, você pode combinar flexibilidade e desempenho.

Se você comparar essa estrutura com o esquema do banco de dados AdventureWorks da tabela Person, poderá notar que removemos muitas tabelas relacionadas.

Além da simplicidade do esquema, suas operações de acesso a dados serão mais simples em comparação com a estrutura relacional complexa. Agora você pode ler tabela única em vez de juntar várias tabelas. Quando você precisar inserir uma nova pessoa com informações relacionadas (endereços de e-mail, números de telefone), poderá inserir um único registro em uma tabela em vez de inserir um registro na tabela AdventureWorks Person. , endereços de email etc. Além disso, neste modelo, você pode excluir facilmente linhas de uma pessoa sem exclusões em cascata usando relacionamentos de chave estrangeira.

Os bancos de dados NoSQL são otimizados para operações simples, de leitura, inserção e exclusão - o SQL Server 2016 permite aplicar a mesma lógica no banco de dados relacional.

Restrições JSON Nos exemplos anteriores, vimos como adicionar uma restrição simples que valida se o texto armazenado na coluna está formatado corretamente. Embora o JSON não tenha um esquema forte, você também pode adicionar restrições complexas combinando funções que lêem valores das funções JSON e T-SQL padrão:

ALTER TABLE Person
 ADD CONSTRAINT [Age should be number]
 CHECK ( ISNUMERIC(JSON_VALUE(value, '$.age'))>0 )

 ALTER TABLE Person
 ADD CONSTRAINT [Person should have skills]
 CHECK ( JSON_QUERY(value, '$.skills') IS NOT NULL)
First constraint will take the value of $.age property and check is this numeric value. Second constraint will try to find JSON object in $.skills property and verify that it exists. The following INSERT statements will fail due to the violation of constraints:



INSERT INTO Person(value)
 VALUES ('{"age": "not a number", "skills":[]}')

 INSERT INTO Person(value)
 VALUES ('{"age": 35}')

Observe que as restrições CHECK podem atrasar seus processos de inserção / atualização, portanto você pode evitá-las se precisar de um desempenho de gravação mais rápido.

Armazenamento JSON compactado Se você tiver um texto JSON grande, poderá compactar explicitamente o texto JSON usando a função COMPRESS interna. No exemplo a seguir, o conteúdo JSON compactado é armazenado como dados binários e calculamos a coluna que descompacta JSON como texto original usando a função DECOMPRESS:

CREATE TABLE Person

 ( _id int identity constraint PK_JSON_ID primary key,

 data varbinary(max),

 value AS CAST(DECOMPRESS(data) AS nvarchar(max))

 )



 INSERT INTO Person(data)

 VALUES (COMPRESS(@json))

As funções COMPRESS e DECOMPRESS usam a compressão GZip padrão. Se o seu cliente puder lidar com a compactação GZip (por exemplo, navegador que entende o conteúdo gzip), você poderá retornar diretamente o conteúdo compactado. Observe que isso é uma troca de desempenho / armazenamento. Se você consulta dados compactados com frequência, seu desempenho é mais lento porque o texto deve ser descompactado a cada vez.

Nota: As funções JSON estão disponíveis apenas no SQL Server 2016+ e no Banco de Dados SQL do Azure.

Mais informações podem ser lidas na fonte deste artigo

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/23/storing-json-in-sql-server/


Vou acenar minha varinha mágica. Poof! Regras de ouro sobre o uso de JSON:

  • Se o MySQL não precisar procurar dentro do JSON, e o aplicativo simplesmente precisar de uma coleção de coisas, então o JSON está bom, possivelmente ainda melhor.

  • Se você estiver pesquisando dados que estão dentro e tiver o MariaDB 10.0.1 ou MySQL 5.7 (com um tipo de dados e funções JSON), o JSON pode ser prático. As colunas "Dinâmicas" do MariaDB 5.3 são uma variante disso.

  • Se você está fazendo coisas "Entidade-Atributo-Valor", o JSON não é bom, mas é o menor dos vários males. http://mysql.rjweb.org/doc.php/eav

  • Para pesquisar por uma coluna indexada, não ter o valor oculto dentro do JSON é uma grande vantagem.

  • Para pesquisar por um intervalo em uma coluna indexada, ou uma pesquisa FULLTEXT ou SPATIAL , o JSON não é possível.

  • Para WHERE a=1 AND b=2 o índice "composto" INDEX(a,b) é ótimo; provavelmente não pode chegar perto com JSON.

  • JSON funciona bem com dados "esparsos"; O INDEX funciona, mas não tão bem assim. (Estou me referindo a valores que estão 'ausentes' ou NULL para muitas das linhas.)

  • O JSON pode fornecer "matrizes" e "árvores" sem recorrer a tabelas extras. Mas explore essas matrizes / árvores apenas no aplicativo, não no SQL.

  • JSON é um mundo melhor que XML. (Minha opinião)

  • Se você não quiser entrar na string JSON, exceto no aplicativo, recomendo compactá-lo (no cliente) e armazená-lo em um BLOB . Pense nisso como um .jpg - há coisas lá, mas o SQL não se importa.

Declare sua inscrição; talvez possamos ser mais específicos.





structured-data