Introdução aos recursos JSON no Banco de Dados SQL do Azure e na Instância Gerenciada SQL do Azure

  • Artigo

Aplica-se a:Banco de Dados SQL do Azure Instância Gerenciada SQLdo Azure

O Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure permitem analisar e consultar dados representados no formato JSON (JavaScript Object Notation ) e exportar seus dados relacionais como texto JSON. Os seguintes cenários JSON estão disponíveis:

Formatando dados relacionais no formato JSON

Se você tiver um serviço Web que usa dados da camada de banco de dados e fornece uma resposta no formato JSON, ou estruturas JavaScript do lado do cliente ou bibliotecas que aceitam dados formatados como JSON, você pode formatar o conteúdo do banco de dados como JSON diretamente em uma consulta SQL. Você não precisa mais escrever código de aplicativo que formata resultados do Banco de Dados SQL do Azure ou da Instância Gerenciada SQL do Azure como JSON ou incluir alguma biblioteca de serialização JSON para converter resultados de consulta tabular e, em seguida, serializar objetos para o formato JSON. Em vez disso, você pode usar a cláusula FOR JSON para formatar os resultados da consulta SQL como JSON e usá-la diretamente em seu aplicativo.

No exemplo a seguir, as linhas da Sales.Customer tabela são formatadas como JSON usando a cláusula FOR JSON:

select CustomerName, PhoneNumber, FaxNumber
from Sales.Customers
FOR JSON PATH

A cláusula PARA JSON PATH formata os resultados da consulta como texto JSON. Os nomes das colunas são usados como chaves, enquanto os valores das células são gerados como valores JSON:

[
{"CustomerName":"Eric Torres","PhoneNumber":"(307) 555-0100","FaxNumber":"(307) 555-0101"},
{"CustomerName":"Cosmina Vlad","PhoneNumber":"(505) 555-0100","FaxNumber":"(505) 555-0101"},
{"CustomerName":"Bala Dixit","PhoneNumber":"(209) 555-0100","FaxNumber":"(209) 555-0101"}
]

O conjunto de resultados é formatado como uma matriz JSON onde cada linha é formatada como um objeto JSON separado.

PATH indica que você pode personalizar o formato de saída do resultado JSON usando notação de ponto em aliases de coluna. A consulta a seguir altera o nome da chave "CustomerName" no formato JSON de saída e coloca números de telefone e fax no subobjeto "Contato":

select CustomerName as Name, PhoneNumber as [Contact.Phone], FaxNumber as [Contact.Fax]
from Sales.Customers
where CustomerID = 931
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

A saída desta consulta tem esta aparência:

{
    "Name":"Nada Jovanovic",
    "Contact":{
           "Phone":"(215) 555-0100",
           "Fax":"(215) 555-0101"
    }
}

Neste exemplo, retornamos um único objeto JSON em vez de uma matriz especificando a opção WITHOUT_ARRAY_WRAPPER . Você pode usar essa opção se souber que está retornando um único objeto como resultado da consulta.

O principal valor da cláusula JSON FOR é que ela permite retornar dados hierárquicos complexos do banco de dados formatados como objetos JSON aninhados ou matrizes. O exemplo a seguir mostra como incluir as linhas da Orders tabela que pertencem ao Customer como uma matriz aninhada de Orders:

select CustomerName as Name, PhoneNumber as Phone, FaxNumber as Fax,
        Orders.OrderID, Orders.OrderDate, Orders.ExpectedDeliveryDate
from Sales.Customers Customer
    join Sales.Orders Orders
        on Customer.CustomerID = Orders.CustomerID
where Customer.CustomerID = 931
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER

Em vez de enviar consultas separadas para obter dados do Cliente e, em seguida, buscar uma lista de Pedidos relacionados, você pode obter todos os dados necessários com uma única consulta, conforme mostrado na saída de exemplo a seguir:

{
  "Name":"Nada Jovanovic",
  "Phone":"(215) 555-0100",
  "Fax":"(215) 555-0101",
  "Orders":[
    {"OrderID":382,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
    {"OrderID":395,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
    {"OrderID":1657,"OrderDate":"2013-01-31","ExpectedDeliveryDate":"2013-02-01"}
  ]
}

Trabalhar com dados JSON

Se você não tiver dados estritamente estruturados, se tiver subobjetos, matrizes ou dados hierárquicos complexos ou se suas estruturas de dados evoluírem com o tempo, o formato JSON poderá ajudá-lo a representar qualquer estrutura de dados complexa.

JSON é um formato textual que pode ser usado como qualquer outro tipo de cadeia de caracteres no Banco de Dados SQL do Azure e na Instância Gerenciada SQL do Azure. Você pode enviar ou armazenar dados JSON como um NVARCAR padrão:

CREATE TABLE Products (
  Id int identity primary key,
  Title nvarchar(200),
  Data nvarchar(max)
)
go
CREATE PROCEDURE InsertProduct(@title nvarchar(200), @json nvarchar(max))
AS BEGIN
    insert into Products(Title, Data)
    values(@title, @json)
END

Os dados JSON usados neste exemplo são representados usando o tipo NVARCHAR(MAX). JSON pode ser inserido nesta tabela ou fornecido como um argumento do procedimento armazenado usando sintaxe Transact-SQL padrão, conforme mostrado no exemplo a seguir:

EXEC InsertProduct 'Toy car', '{"Price":50,"Color":"White","tags":["toy","children","games"]}'

Qualquer linguagem ou biblioteca do lado do cliente que funcione com dados de cadeia de caracteres no Banco de Dados SQL do Azure e na Instância Gerenciada SQL do Azure também funcionará com dados JSON. JSON pode ser armazenado em qualquer tabela que suporte o tipo NVARCAR, como uma tabela com otimização de memória ou uma tabela com versão do sistema. JSON não introduz nenhuma restrição no código do lado do cliente ou na camada de banco de dados.

Consultando dados JSON

Se você tiver dados formatados como JSON armazenados em tabelas SQL do Azure, as funções JSON permitirão que você use esses dados em qualquer consulta SQL.

As funções JSON disponíveis no Banco de Dados SQL do Azure e na Instância Gerenciada SQL do Azure permitem tratar dados formatados como JSON como qualquer outro tipo de dados SQL. Você pode extrair facilmente valores do texto JSON e usar dados JSON em qualquer consulta:

select Id, Title, JSON_VALUE(Data, '$.Color'), JSON_QUERY(Data, '$.tags')
from Products
where JSON_VALUE(Data, '$.Color') = 'White'

update Products
set Data = JSON_MODIFY(Data, '$.Price', 60)
where Id = 1

A função JSON_VALUE extrai um valor do texto JSON armazenado na coluna Dados. Esta função usa um caminho semelhante ao JavaScript para fazer referência a um valor no texto JSON para extrair. O valor extraído pode ser usado em qualquer parte da consulta SQL.

A função JSON_QUERY é semelhante à JSON_VALUE. Ao contrário de JSON_VALUE, esta função extrai subobjetos complexos, como matrizes ou objetos que são colocados em texto JSON.

A função JSON_MODIFY permite especificar o caminho do valor no texto JSON que deve ser atualizado, bem como um novo valor que substituirá o antigo. Desta forma, você pode facilmente atualizar o texto JSON sem reparar toda a estrutura.

Como o JSON é armazenado em um texto padrão, não há garantias de que os valores armazenados em colunas de texto estejam formatados corretamente. Você pode verificar se o texto armazenado na coluna JSON está formatado corretamente usando as restrições de verificação padrão do Banco de Dados SQL do Azure e a função ISJSON:

ALTER TABLE Products
    ADD CONSTRAINT [Data should be formatted as JSON]
        CHECK (ISJSON(Data) > 0)

Se o texto de entrada estiver formatado corretamente JSON, a função ISJSON retornará o valor 1. Em cada inserção ou atualização da coluna JSON, essa restrição verificará se o novo valor de texto não é JSON malformado.

Transformando JSON em formato tabular

O Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure também permitem transformar coleções JSON em formato tabular e carregar ou consultar dados JSON.

OPENJSON é uma função de valor de tabela que analisa texto JSON, localiza uma matriz de objetos JSON, itera através dos elementos da matriz e retorna uma linha no resultado de saída para cada elemento da matriz.

JSON tabular

No exemplo acima, podemos especificar onde localizar a matriz JSON que deve ser aberta (no $. Caminho de ordens), quais colunas devem ser retornadas como resultado e onde encontrar os valores JSON que serão retornados como células.

Podemos transformar uma matriz JSON na @orders variável em um conjunto de linhas, analisar esse conjunto de resultados ou inserir linhas em uma tabela padrão:

CREATE PROCEDURE InsertOrders(@orders nvarchar(max))
AS BEGIN

    insert into Orders(Number, Date, Customer, Quantity)
    select Number, Date, Customer, Quantity
    FROM OPENJSON (@orders)
     WITH (
            Number varchar(200),
            Date datetime,
            Customer varchar(200),
            Quantity int
     )
END

A coleção de ordens formatadas como uma matriz JSON e fornecidas como um parâmetro para o procedimento armazenado pode ser analisada e inserida na tabela Orders.