Validar, consultar e alterar dados JSON com funções internas (SQL Server)
Aplica-se a: SQL Server 2016 (13.x) e posteriores Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure
O suporte interno para JSON inclui as funções internas a seguir, descritas brevemente neste artigo.
- ISJSON testa se uma cadeia de caracteres contém JSON válido.
- JSON_VALUE extrai um valor escalar de uma cadeia de caracteres JSON.
- JSON_QUERY extrai um objeto ou uma matriz de uma cadeia de caracteres JSON.
- JSON_MODIFY atualiza o valor de uma propriedade em uma cadeia de caracteres JSON e retorna a cadeia de caracteres JSON atualizada.
Para todas as funções JSON, revise Funções JSON.
Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022
ou AdventureWorksDW2022
, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.
Os exemplos nesta página usam o texto JSON semelhante ao conteúdo mostrado no exemplo a seguir:
{
"id": "DesaiFamily",
"parents": [
{ "familyName": "Desai", "givenName": "Prashanth" },
{ "familyName": "Miller", "givenName": "Helen" }
],
"children": [
{
"familyName": "Desai",
"givenName": "Jesse",
"gender": "female",
"grade": 1,
"pets": [
{ "givenName": "Goofy" },
{ "givenName": "Shadow" }
]
},
{
"familyName": "Desai",
"givenName": "Lisa",
"gender": "female",
"grade": 8
}
],
"address": {
"state": "NY",
"county": "Manhattan",
"city": "NY"
},
"creationDate": 1431620462,
"isRegistered": false
}
Esse documento JSON, que contém elementos complexos aninhados, é armazenado na seguinte tabela de exemplo:
CREATE TABLE Families (
id INT identity CONSTRAINT PK_JSON_ID PRIMARY KEY,
[doc] NVARCHAR(MAX)
);
As funções JSON funcionam da mesma forma se o documento JSON estiver armazenado em varchar, nvarchar ou no tipo de dados json nativo.
A função ISJSON
testa se uma cadeia de caracteres contém JSON válido.
O exemplo a seguir retorna linhas nas quais a coluna JSON contém um texto JSON válido. Sem a restrição JSON explícita, é possível inserir qualquer texto na coluna nvarchar:
SELECT *
FROM Families
WHERE ISJSON(doc) > 0;
Para obter mais informações, confira ISJSON.
A função JSON_VALUE
extrai um valor escalar de uma cadeia de caracteres JSON. A seguinte consulta retorna os documentos em que o campo JSON id
corresponde ao valor DesaiFamily
, ordenado pelos campos JSON city
e state
:
SELECT JSON_VALUE(f.doc, '$.id') AS Name,
JSON_VALUE(f.doc, '$.address.city') AS City,
JSON_VALUE(f.doc, '$.address.county') AS County
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC,
JSON_VALUE(f.doc, '$.address.state') ASC
Os resultados dessa consulta são mostrados na seguinte tabela:
Nome | Cidade | Município |
---|---|---|
DesaiFamily |
NY |
Manhattan |
Para obter mais informações, confira JSON_VALUE.
A função JSON_QUERY
extrai um objeto ou uma matriz de uma cadeia de caracteres JSON. O exemplo a seguir mostra como retornar um fragmento JSON nos resultados da consulta.
SELECT JSON_QUERY(f.doc, '$.address') AS Address,
JSON_QUERY(f.doc, '$.parents') AS Parents,
JSON_QUERY(f.doc, '$.parents[0]') AS Parent0
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily';
Os resultados dessa consulta são mostrados na seguinte tabela:
Address | Pais | Parent0 |
---|---|---|
{ "state": "NY", "county": "Manhattan", "city": "NY" } |
[ { "familyName": "Desai", "givenName": "Prashanth" }, { "familyName": "Miller", "givenName": "Helen" } ] |
{ "familyName": "Desai", "givenName": "Prashanth" } |
Para obter mais informações, confira JSON_QUERY.
A função OPENJSON
permite transformar a submatriz JSON no conjunto de linhas e, em seguida, associá-la ao elemento pai. Como um exemplo, é possível retornar todos os documentos da família e “ingressá-los” com seus objetos children
armazenados como uma matriz JSON interna:
SELECT JSON_VALUE(f.doc, '$.id') AS Name,
JSON_VALUE(f.doc, '$.address.city') AS City,
c.givenName,
c.grade
FROM Families f
CROSS APPLY OPENJSON(f.doc, '$.children') WITH (
grade INT,
givenName NVARCHAR(100)
) c
Os resultados dessa consulta são mostrados na seguinte tabela:
Nome | City | givenName | grade |
---|---|---|---|
DesaiFamily |
NY |
Jesse |
1 |
DesaiFamily |
NY |
Lisa |
8 |
São retornadas duas linhas, porque uma linha pai é ingressada com duas linhas filho produzidas analisando dois elementos da submatriz filho. A função OPENJSON
analisa o fragmento children
da coluna doc
e retorna grade
e givenName
de cada elemento como um conjunto de linhas. Esse conjunto de linhas pode ser ingressado com o documento pai.
É possível aplicar várias chamadas CROSS APPLY OPENJSON
para consultar estruturas JSON aninhadas. O documento JSON usado neste exemplo tem uma matriz aninhada chamada children
, em que cada filho tem uma matriz aninhada de pets
. A consulta a seguir analisa os filhos de cada documento, retornará cada objeto de matriz como linha e, em seguida, analisará a matriz pets
:
SELECT c.familyName,
c.givenName AS childGivenName,
p.givenName AS petName
FROM Families f
CROSS APPLY OPENJSON(f.doc) WITH (
familyName NVARCHAR(100),
children NVARCHAR(MAX) AS JSON
) AS a
CROSS APPLY OPENJSON(children) WITH (
familyName NVARCHAR(100),
givenName NVARCHAR(100),
pets NVARCHAR(max) AS JSON
) AS c
OUTER APPLY OPENJSON(pets) WITH (givenName NVARCHAR(100)) AS p;
A primeira chamada OPENJSON
retorna o fragmento da matriz children
que usa a cláusula AS JSON. Esse fragmento de matriz é fornecido à segunda função OPENJSON
que retorna givenName
, firstName
de cada filho, além da matriz de pets
. A matriz de pets
é fornecida à terceira função OPENJSON
que retorna o givenName
do animal de estimação.
Os resultados dessa consulta são mostrados na seguinte tabela:
familyName | childGivenName | petName |
---|---|---|
Desai |
Jesse |
Goofy |
Desai |
Jesse |
Shadow |
Desai |
Lisa |
NULL |
O documento raiz é ingressado com duas linhas children
retornadas pela primeira chamada OPENJSON(children)
que cria duas linhas (ou tuplas). Em seguida, cada linha será ingressada com as novas linhas geradas por OPENJSON(pets)
usando o operador OUTER APPLY
. Jesse tem dois animais de estimação, então (Desai, Jesse)
é ingressado com duas linhas geradas para Goofy
e Shadow
. Lisa não tem animais de estimação, portanto não há linhas retornadas por OPENJSON(pets)
para essa tupla. No entanto, como usamos OUTER APPLY
, obtemos NULL
na coluna. Se colocássemos CROSS APPLY
em vez de OUTER APPLY
, Lisa não seria retornada no resultado, porque não há linhas de animais que pudessem ser ingressadas com essa tupla.
A principal diferença entre JSON_VALUE
e JSON_QUERY
é que JSON_VALUE
retorna um valor escalar, enquanto JSON_QUERY
retorna um objeto ou uma matriz.
Considere o seguinte texto JSON de exemplo.
{
"a": "[1,2]",
"b": [1, 2],
"c": "hi"
}
Neste texto JSON de exemplo, membros de dados "a" e "c" são valores de cadeia de caracteres, enquanto o membro de dados "b" é uma matriz. JSON_VALUE
e JSON_QUERY
retornam os seguintes resultados:
Caminho | Retornos de JSON_VALUE |
Retornos de JSON_QUERY |
---|---|---|
$ |
NULL ou erro |
{ "a": "[1,2]", "b": [1, 2], "c": "hi" } |
$.a |
[1,2] |
NULL ou erro |
$.b |
NULL ou erro |
[1,2] |
$.b[0] |
1 |
NULL ou erro |
$.c |
hi |
NULL ou erro |
Teste as funções internas descritas neste artigo executando os exemplos a seguir com o banco de dados de exemplo AdventureWorks2022
. Para obter informações sobre como adicionar dados JSON para o teste executando um script, consulte Fazer um test drive do suporte interno a JSON.
Nos exemplos a seguir, a coluna Info
na tabela SalesOrder_json
contém texto JSON.
A consulta a seguir retorna os valores de colunas relacionais padrão e de uma coluna JSON.
SELECT SalesOrderNumber,
OrderDate,
Status,
ShipDate,
AccountNumber,
TotalDue,
JSON_QUERY(Info, '$.ShippingInfo') ShippingInfo,
JSON_QUERY(Info, '$.BillingInfo') BillingInfo,
JSON_VALUE(Info, '$.SalesPerson.Name') SalesPerson,
JSON_VALUE(Info, '$.ShippingInfo.City') City,
JSON_VALUE(Info, '$.Customer.Name') Customer,
JSON_QUERY(OrderItems, '$') OrderItems
FROM Sales.SalesOrder_json
WHERE ISJSON(Info) > 0;
A consulta a seguir agrega subtotais por nome do cliente (armazenado em JSON) e status (armazenado em uma coluna comum). Em seguida, ela filtra os resultados por cidade (armazenado em JSON) e OrderDate (armazenado em uma coluna comum).
DECLARE @territoryid INT;
DECLARE @city NVARCHAR(32);
SET @territoryid = 3;
SET @city = N'Seattle';
SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer,
Status,
SUM(SubTotal) AS Total
FROM Sales.SalesOrder_json
WHERE TerritoryID = @territoryid
AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
AND OrderDate > '1/1/2015'
GROUP BY JSON_VALUE(Info, '$.Customer.Name'),
Status
HAVING SUM(SubTotal) > 1000;
A função JSON_MODIFY
atualiza o valor de uma propriedade em uma cadeia de caracteres JSON e retorna a cadeia de caracteres JSON atualizada.
O exemplo a seguir atualiza o valor de uma propriedade JSON em uma variável que contém JSON.
SET @info = JSON_MODIFY(@jsonInfo, '$.info.address[0].town', 'London');
Para obter mais informações, confira JSON_MODIFY.