Partilhar via

Introdução aos recursos JSON

Aplica-se a:Banco de Dados SQL do AzureInstância Gerenciada SQL do Azurebanco de dados SQL no Fabric

O Banco de Dados SQL do Azure, o Banco de Dados SQL no Microsoft Fabric 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:

Formatar 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. Não é mais necessário escrever código de aplicativo que formata resultados 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 para formatar os resultados da FOR JSON 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 FOR JSON PATH cláusula 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 seguinte consulta altera o nome da chave CustomerName no formato JSON de saída e coloca os números de telefone e fax no subobjeto Contact.

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 FOR JSON cláusula é 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 Customer dados e, em seguida, buscar uma lista de dados relacionados Orders, 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. Você pode enviar ou armazenar dados JSON como um nvarchar 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 de 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 trabalhe com dados de cadeia de caracteres também funcionará com dados JSON. JSON pode ser armazenado em qualquer tabela que suporte o tipo nvarchar , 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.

Consultar dados JSON

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

As funções JSON 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 JSON_VALUE função 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 JSON_QUERY função é semelhante a JSON_VALUE. Ao contrário de JSON_VALUE, esta função extrai subobjetos complexos, como arrays ou objetos que são colocados em texto JSON.

A JSON_MODIFY função 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 restrições de verificação padrão e a ISJSON função:

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 ISJSON função 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.

Transformar JSON em formato tabular

Você pode transformar coleções JSON em formato tabular e carregar ou consultar dados JSON.

OPENJSON é uma função T-SQL 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.

Capturas de tela e trechos de código de dados tabulares JSON de exemplo.

No exemplo, podemos especificar onde localizar a matriz JSON que deve ser aberta (no $.Orders caminho), 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.

Conteúdo relacionado