Microsoft SQL Server 2016 e JSON uma combinação bem interessante – Final

Bom dia, comunidade, segunda – feira, alias estamos na última semana de 2015, eita ano complicado, repleto de surpresas não tão boas, mas que esta se encerrando de forma razoável.

Para encerrar este ano, estou retornando com a parte final desta pequena série de posts dedicadas exclusivamente a esta novidade bastante útil e interessante que foi introduziada no Microsoft SQL Server 2016, como você pode observar no título estou me referindo ao suporte para uso do JSON em um ambiente rodando o SQL Server 2016.

Caso você não tenha acessado o post anterior sobre esta novidade ou outros posts dedicados ao Microsoft SQL Server 2016, não perca tempo e a oportunidade de conhecer mais sobre esta nova versão acessando os links abaixo:

https://pedrogalvaojunior.wordpress.com/2015/11/09/microsoft-sql-server-2016-e-json-uma-combinacao-bem-interessante-parte-i/

https://pedrogalvaojunior.wordpress.com/2015/07/10/microsoft-sql-server-2016-lista-de-novidades-parte-i/

https://pedrogalvaojunior.wordpress.com/2015/07/16/microsoft-sql-server-2016-lista-de-novidades-parte-ii/

https://pedrogalvaojunior.wordpress.com/2015/07/30/microsoft-sql-server-2016-lista-de-novidades-final/

 

Caso você já tenha acessado a primeira parte desta série, deverá ter observado que destaquei de forma bastante apronfudada como o JSON se tornou uma tecnologia tão presente e utilizada atualmente no desenvolvimento de software, além disso, apresentei um pouco da sua história e a visão do time de produto do SQL Server perante a comunidade.

Nesta segunda parte, conforme prometido vou destacar um pouco como podemos trabalhar com funções específicas do JSON e de que forma podemos criar uma indexação de dados existentes dentro do formato JSON.

Mais uma vez obrigado por sua visita, espero que este post possa ser útil e traga algum tipo de interesse e curiosidade em conhecer mais sobre o Microsoft SQL Server.

 

O JSON e suas funções

Como toda novidade, e isso também não poderia ser diferente para o JSON, os time de desenvolvimento e engenheiros do SQL Server, adicionaram neste nova versão um conjunto de funções internas exclusivas para se trabalhar com o JSON, sendo estas:

  • ISJSON: Definida e reconhecida como uma função JSON Text, a ISJOSON tem como finalidade verificar se o texto que esta sendo passado no formato JSON pode ser reconhecido como um tipo de dados NVARCHAR. Através desta função é possível podemos implementar restrições(constraints) em colunas com forma de verificação de dados. Importante destacar que esta função esta disponível a partir da versão SQL Server 2016 Community Technology Preview 3.2
  • JSON_Values: Definida e reconhecida como uma função JSON Text, a JSON_Value, possui um papel importante quando trabalhamos com dados JSON, que consiste basicamente na análise e extração de um possível valor escalar que representa um determinado caminho específico reconhecido pelo padrão JavaScript, como forma de obter e reconhecer todo caminho necessário para se obter um valor JSON. Para identificação deste caminnho, torna-se encessário utilizar algo similar a uma sintaxe JavaScript para fazer referência a propriedades em texto JSON. Alguns exemplos são:
  1. ‘$’ – faz referência a todo objeto JSON no texto de entrada;
  2. ‘$.property1’ – referências property1 no objeto JSON;
  3. ‘$[5]’ – referencia 5-th elemento na matriz JSON;
  4. $. property1.property2.array1 [5].property3.array2 [15] .property4 ‘ – referências complexa Propriedade aninhada no objeto JSON.
  • O sinal de cifrão ($) representa o objeto de entrada JSON (semelhante ao iniciar / em XPath). Você pode adicionar qualquer JavaScript como referências de propriedade/matriz após o item de contexto para fazer referência a qualquer propriedade de aninhados. Importante destacar que esta função esta disponível a partir da versão SQL Server 2016 Community Technology Preview 3.2
  • JSON_Query: A função JSON_Query também é uma função texto que possui a finalidade de reconhecer e extrair um determinado valor “objeto” existente no formato JSON, sendo este um único ou um conjunto de caracteres.
  • OPENJSON: Uma das mais importantes funções, a OPENJSON irá fornecer a capacidade de analisar o texto JSON e retornar propriedades como uma tabela relacional usando a função de valor de tabela interna OPENJSON. Através da utilização da OPENJSON podemos ter explicitamente definido o esquema de resultados ou esquema padrão será usada. OPENJSON fornece uma visão de conjunto de linhas sobre um documento JSON. Porque o OPENJSON é um provedor de conjunto de linhas, que pode ser usado em instruções Transact-SQL em qual conjunto de linhas de uma tabela, visão ou até mesmo em conjunto com funções similares ao OPENROWSET.

 

 

Como indexar documentos e dados JSON

Quando a Microsoft anuncio o suporte ao JSON no SQL Server, muitos se perguntavam e ainda devem estar se perguntando. “Como será possível realizar a indexação ou algum tipo de indexação de dados no formato JSON?”

Esta pode parecer uma pergunta complicado de se responder ou até mesmo imaginar que possa existir uma resposta, mas como grande surpresa e reconhecimento da capacidade profissional do time de desenvolvimento do SQL Server, existe sim um resposta, nada muito diferente do que já estamos acostumados a utilizar no SQL Server.

A capacidade de se trabalhar com índices de diferentes formas e tipos de dados é algo que de longe não existem em nenhum outro SGBD – Sistema Gerenciador de Banco de Dados, característica singular do SQL Server, desde o reconhecimento de forma nativa de índices XML adicionados ao Microsoft SQL Server 2005 e posteriormente os fantásticos índices de cobertura presentes no Microsoft SQL Server 2008 R2, sem se esquecer do ColumnStore Index criados a partir da versão 2012.

Para que possamos fazer uso de índices para nossos dados no formato JSON, inicialmente não precisamos criar nada de diferente em relação a outras situações, basta através do comando Create Index criar um novo índice especificando qual ou quais colunas deverão fazer parte deste índice. A principal diferença é que o uso do índice no momento em que nossa query for executada esta atrelado a declaração da coluna JSON_Value, sendo esta condição para que o query optimizer possa ser notificado e posteriormente venha a reconhecer o uso ou não deste elemento de pesquisa e ordenação de dados.

Observação: Vale ressaltar que todo e qualquer índice será utilizado em uma operação de busca de dados, principalmente se o query optimizer entender a sua necessidade. No caso de um índice com base em dados JSON, a função JSON_Valeu tem que reconhecer de forma idêntica a coluna e caminho no formato JSON para que seja possível obter o dado de uma maneira mais ágil e eficiente.

Agora que você já conhece um pouco sobre as funções específicas para uso do JSON e também a forma maneira que os índices para tipos de dados JSON podem ser utilizados, vamos seguir em frente e brincar um pouco com alguns exemplos apresentados abaixo.

Exemplos

Para estes exemplos, vou utilizar o mesmo cenário apresentado na primeira parte. Vou utilizar novamente minha Virtual Machine criada no Microsoft Azure, rodando o SQL Server 2016 CTP3 (versão lançada em 28/10/2015 – http://blogs.technet.com/b/dataplatforminsider/archive/2015/10/28/sql-server-2016-everything-built-in.aspx) e AdventureWorks2016CTP3 disponível para o 2016, caso você queira montar o mesmo ambiente rodando stand-alone, faça download do SQL Server 2016 e AdventureWorks, ou saber mais sobre esta nova versão acesse:

  1. http://blogs.technet.com/b/dataplatforminsider/archive/2015/10/28/sql-server-2016-everything-built-in.aspx
  2. https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016
  3. https://azure.microsoft.com/en-us/marketplace/partners/microsoft/sqlserver2016ctp3evaluationwindowsserver2012r2/
  4. http://www.microsoft.com/en-us/download/details.aspx?id=49502

Vamos por a mão na massa como eu sempre digo aos meus alunos: “Quem não pratica, não aprende”. Desta forma, let’s go:

— Exemplo 1 – Utilizando a função ISJSON –

CREATE TABLE MyTable(

Id int,

jsonCol1 varchar(MAX) CHECK (ISJSON(jsonCol1)>0),

jsonCol2 varchar(4000));

ALTER TABLE MyTable

ADD CONSTRAINT jsonCol2_is_json CHECK (ISJSON(jsonCol2) > 0);

GO

 

SELECT id, json_col

FROM tab1

WHERE ISJSON(json_col) > 0

GO

 

— Exemplo 2 – Utilizando a função JSON_VALUE –

Declare @VarJSON NVarchar(400)

Set @VarJSON =

N'{

“info”:{

“type”:1,

“address”:{

“town”:”Bristol”,

“county”:”Avon”,

“country”:”England”

},

“tags”:[“Sport”, “Water polo”]

},

“type”:”Basic”

}’

 

SELECT FirstName, LastName,

JSON_VALUE(jsonInfo, ‘$.info.address[0].town’) AS Town

FROM Person.Person

WHERE JSON_VALUE(jsonInfo, ‘$.info.address[0].state’) like ‘US%’

ORDER BY JSON_VALUE(jsonInfo, ‘$.info.address[0].town’)

Go

 

— Exemplo 3 – Utilizando a função JSON_Query –

Declare @jsoninfo nvarchar(400)

Set @jsoninfo =

N'{

“info”:{

“type”:1,

“address”:{

“town”:”Bristol”,

“county”:”Avon”,

“country”:”England”

},

“tags”:[“Sport”, “Water polo”]

},

“type”:”Basic”

}’

 

SELECT FirstName, LastName,

JSON_QUERY(@jsoninfo, ‘$.info.address’) AS Address

FROM Person.Person

ORDER BY LastName

Go

Figura-1-JSON

Figura 1 – Utilizando a função JSON_Query.

— Exemplo 4 – Utilizando a função OPENJSON –

SELECT * FROM OPENJSON(‘[“Brasil”,

“United Kingdom”,

“United States”,

“Índia”,

“Singapore”,

“Marrocos”,

“Suriname”]’)

Go

Figura-2-JSON

Figura 2 – Utilizando a função OPENJSON.

— Exemplo 5 – Utilizando a função OPENJSON para transformar JSON Texto em uma relational Table –

Declare @JSalestOrderDetails NVarchar(4000)

 

Set @JSalestOrderDetails =

‘{“OrdersArray”: [

{“Number”:1, “Date”: “8/10/2012”, “Customer”: “Adventure works”, “Quantity”: 1200},

{“Number”:4, “Date”: “5/11/2012”, “Customer”: “Adventure works”, “Quantity”: 100},

{“Number”:6, “Date”: “1/3/2012”, “Customer”: “Adventure works”, “Quantity”: 250},

{“Number”:8, “Date”: “12/7/2012”, “Customer”: “Adventure works”, “Quantity”: 2200}

]}’

 

SELECT Number, Customer, Date, Quantity_

FROM OPENJSON (@JSalestOrderDetails, ‘$.OrdersArray’)

WITH (

Number varchar(200),

Date datetime,

Customer varchar(200),

Quantity int

) AS OrdersArray

Go

Figura-3-JSON

Figura 3 – Transformando um JSON Text em tabela relacional.

— Exemplo 6 – Trabalhando com índices e dados JSON —

CREATE TABLE SalesOrderRecord

( Id int PRIMARY KEY IDENTITY,

OrderNumber NVARCHAR(25) NOT NULL,

OrderDate DATETIME NOT NULL,

JOrderDetails NVARCHAR(4000),

Quantity AS CAST(JSON_VALUE(JOrderDetails, ‘$.Order.Qty’) AS int),

Price AS JSON_VALUE(JOrderDetails, ‘$.Order.Price’))

GO

CREATE INDEX idxJson ON SalesOrderRecord(Quantity) INCLUDE (Price);

Go

 

Conclusão

O Microsoft SQL Server 2016 esta chegando e com ele uma série de novas funcionalidades, melhorias e inovações estão sendo apresentadas, entre estes itens o JSON é um dos mais aguardados.

A tecnologia JSON criada inicialmente como uma forma de se consumir dados de maneira mais simples em comparação ao formato XML, torna-se uma referência mundial e isso fez com que a Microsoft e seu tipo de engenheiros do SQL Server entende-se a necessidade de adicionar este recurso ou melhor dizendo funcionalidade ao produto.

Nesta parte final, você pode conhecer mais sobre o JSON, como é possível indexar um documento ou dados em formato JSON, o uso de funções próprias para se trabalhar com este tecnologia, bem como, o SQL Server consegui tratar, interpretar e apresentar estes dados.

Espero que você tenha gostado deste artigo, que as informações compartilhadas aqui possam lhe ajudar, mostrando como o Microsoft SQL Server esta a cada dia se tornando um ambiente completo de Gerenciamento de Banco de Dados totalmente compatível com diversas tecnologias e linguagens de programação.

 

Agradecimentos,

Agradeço a todos que estiveram visitando meu blog neste ano de 2015, com certeza a presença de vocês é de total importância, mais uma vez conseguir obter um número expressivo de visitantes e leitores, sem falar no número de seguidores que constantemente cresce. Espero em 2016 poder contar ainda mais com a sua presença.

Desejo um Feliz Ano Novo, repleto de Paz, Saúde, Alegria, Conquistas e Realizações.

Nos encontramos em breve.

Até a próxima.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove - Campus São Roque. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Cursando Mestrado em Ciências da Computação - UFSCar - Campus - Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, para versões: 2000, 2005, 2008, 2008 R2, 2012 e 2014. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

Um comentário em “Microsoft SQL Server 2016 e JSON uma combinação bem interessante – Final”

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s