Processar dados JSON com funções internas
Considere um cenário em que seu aplicativo de comércio eletrônico armazena as preferências do cliente e os metadados de pedido como documentos JSON. Um aplicativo móvel envia dados de carrinho de compras no formato JSON e seu sistema de relatórios precisa exportar catálogos de produtos como JSON para uma API Web. Trabalhar diretamente com JSON em seu banco de dados elimina a necessidade de transformações de camada de aplicativo e mantém o processamento de dados eficiente.
Os bancos de dados SQL Server, SQL do Azure e SQL no Fabric fornecem suporte JSON interno que permite analisar, consultar, criar e transformar dados JSON diretamente no T-SQL. Nesta unidade, você aprenderá a usar funções JSON para extrair valores, construir saída JSON, agregar dados em matrizes JSON e validar o conteúdo JSON.
Extrair valores com JSON_VALUE e JSON_QUERY
Ao trabalhar com JSON armazenado em seu banco de dados, você precisa extrair valores específicos para filtragem, junção ou exibição. O SQL Server fornece duas funções para esta finalidade:
JSON_VALUE() extrai um valor escalar (cadeia de caracteres, número, booliano) de uma cadeia de caracteres JSON:
DECLARE @json NVARCHAR(MAX) = N'{
"customer": {
"id": 12345,
"name": "Contoso Ltd",
"active": true
},
"orderTotal": 1599.99
}';
SELECT
JSON_VALUE(@json, '$.customer.id') AS CustomerID,
JSON_VALUE(@json, '$.customer.name') AS CustomerName,
JSON_VALUE(@json, '$.orderTotal') AS OrderTotal;
O conjunto de resultados será:
CustomerID CustomerName OrderTotal
---------- ------------ ----------
12345 Contoso Ltd 1599.99
A função navega pela estrutura JSON usando a expressão de caminho e retorna o valor como uma cadeia NVARCHAR(4000) de caracteres. Você pode converter o resultado em outros tipos de dados conforme necessário para cálculos ou comparações.
JSON_QUERY() extrai um objeto JSON ou matriz (valores nãocalares):
DECLARE @json NVARCHAR(MAX) = N'{
"customer": {
"id": 12345,
"name": "Contoso Ltd"
},
"items": [
{"product": "Widget", "qty": 5},
{"product": "Gadget", "qty": 3}
]
}';
SELECT
JSON_QUERY(@json, '$.customer') AS CustomerObject,
JSON_QUERY(@json, '$.items') AS ItemsArray;
O conjunto de resultados será:
CustomerObject ItemsArray
-------------------------------------- ------------------------------------------------
{"id": 12345,"name": "Contoso Ltd"} [{"product": "Widget", "qty": 5},{"product": "Gadget", "qty": 3}]
Ao contrário JSON_VALUE()de , JSON_QUERY() preserva a estrutura JSON, retornando objetos e matrizes como cadeias de caracteres JSON válidas que você pode armazenar, passar para outras funções ou retornar para aplicativos.
A expressão de caminho usa $ para representar o elemento raiz, com notação de ponto para propriedades aninhadas e notação de colchete para elementos de matriz, como no exemplo a seguir:
-- Access array elements by index (0-based)
SELECT JSON_VALUE(@json, '$.items[0].product') AS FirstProduct;
O resultado será:
FirstProduct
------------
Widget
Os índices de matriz começam em 0, portanto $.items[0] , refere-se ao primeiro elemento. Use essa sintaxe para extrair itens específicos quando souber a posição ou combinar com OPENJSON quando precisar processar todos os elementos da matriz.
Dica
Use JSON_VALUE() quando precisar de um valor escalar para comparações ou cálculos. Use JSON_QUERY() quando precisar preservar a estrutura JSON de objetos aninhados ou matrizes.
Analisar matrizes JSON com OPENJSON
OPENJSON é uma função com valor de tabela que transforma dados JSON em um conjunto de linhas relacional. Use essa função para unir dados JSON com tabelas relacionais ou elementos de matriz de processo individualmente.
A consulta a seguir analisa uma matriz JSON em linhas com o esquema padrão:
DECLARE @json NVARCHAR(MAX) = N'[
{"id": 1, "name": "Widget", "price": 29.99},
{"id": 2, "name": "Gadget", "price": 49.99},
{"id": 3, "name": "Gizmo", "price": 19.99}
]';
SELECT * FROM OPENJSON(@json);
O conjunto de resultados será:
key value type
--- -------------------------------------------- ----
0 {"id": 1, "name": "Widget", "price": 29.99} 5
1 {"id": 2, "name": "Gadget", "price": 49.99} 5
2 {"id": 3, "name": "Gizmo", "price": 19.99} 5
Sem um esquema, OPENJSON retorna três colunas: key (o índice da matriz ou o nome da propriedade), value (o conteúdo JSON) e type (um número que indica o tipo de dados JSON: 0=null, 1=string, 2=number, 3=boolean, 4=array, 5=object).
A consulta a seguir define um esquema explícito para extrair colunas específicas com tipos de dados adequados:
SELECT
ProductID,
ProductName,
Price
FROM OPENJSON(@json)
WITH (
ProductID INT '$.id',
ProductName NVARCHAR(100) '$.name',
Price DECIMAL(10,2) '$.price'
);
O conjunto de resultados será:
ProductID ProductName Price
--------- ----------- ------
1 Widget 29.99
2 Gadget 49.99
3 Gizmo 19.99
A WITH cláusula mapeia as propriedades JSON para colunas digitadas. Essa abordagem fornece tipos de dados adequados para cálculos e comparações e permite que você selecione apenas as propriedades necessárias.
Combine OPENJSON com os dados da tabela usando CROSS APPLY:
-- Assuming Orders table has a JSON column called OrderDetails
SELECT
o.OrderID,
o.CustomerID,
items.ProductName,
items.Quantity,
items.UnitPrice
FROM Orders AS o
CROSS APPLY OPENJSON(o.OrderDetails)
WITH (
ProductName NVARCHAR(100) '$.product',
Quantity INT '$.qty',
UnitPrice DECIMAL(10,2) '$.price'
) AS items;
Observação
Ao usar OPENJSON com CROSS APPLY, as linhas da tabela principal que têm NULL ou valores JSON vazios não são exibidos nos resultados. Use OUTER APPLY se precisar incluir linhas sem dados JSON.
Construir JSON com JSON_OBJECT e JSON_ARRAY
SQL Server 2022 introduziu as funções JSON_OBJECT e JSON_ARRAY para construção JSON intuitiva:
JSON_OBJECT() cria um objeto JSON com base em pares chave-valor, o exemplo a seguir mostra como criar um objeto JSON para um produto:
SELECT JSON_OBJECT(
'id': ProductID,
'name': Name,
'price': ListPrice,
'available': CASE WHEN SellEndDate IS NULL THEN 'true' ELSE 'false' END
) AS ProductJson
FROM SalesLT.Product
WHERE ProductID = 680;
O resultado será:
ProductJson
---------------------------------------------------------------------------
{"id":680,"name":"HL Road Frame - Black, 58","price":1431.50,"available":"true"}
A função manipula automaticamente a conversão de tipo de dados e o escape JSON adequado para caracteres especiais em valores de string.
JSON_ARRAY() cria uma matriz JSON com base em valores, o exemplo a seguir cria uma matriz JSON:
SELECT JSON_ARRAY(
'SQL Server',
'Azure SQL Database',
'SQL Database in Fabric'
) AS Platforms;
O resultado será:
Platforms
---------------------------------------------------------
["SQL Server","Azure SQL Database","SQL Database in Fabric"]
Você pode passar valores de coluna, variáveis ou valores literais para JSON_ARRAY(). A função cria uma matriz JSON formatada corretamente, independentemente dos tipos de entrada.
Em seguida, combine essas funções para criar estruturas JSON aninhadas. O exemplo a seguir constrói um objeto JSON de pedido completo com informações de cliente e totais:
SELECT JSON_OBJECT(
'orderId': soh.SalesOrderID,
'orderDate': soh.OrderDate,
'customer': JSON_OBJECT(
'id': c.CustomerID,
'name': c.CompanyName
),
'totals': JSON_OBJECT(
'subtotal': soh.SubTotal,
'tax': soh.TaxAmt,
'total': soh.TotalDue
)
) AS OrderJson
FROM SalesLT.SalesOrderHeader AS soh
INNER JOIN SalesLT.Customer AS c
ON soh.CustomerID = c.CustomerID
WHERE soh.SalesOrderID = 71774;
O resultado será:
OrderJson
--------------------------------------------------------------------------------
{"orderId":71774,"orderDate":"2008-06-01","customer":{"id":29825,"name":"Contoso"},"totals":{"subtotal":880.35,"tax":70.43,"total":972.79}}
O aninhamento de chamadas JSON_OBJECT cria estruturas hierárquicas que correspondem ao formato esperado do aplicativo. Essa abordagem é mais limpa que a concatenação de cadeia de caracteres e garante uma saída JSON válida.
Agregar dados com JSON_ARRAYAGG
JSON_ARRAYAGG agrega valores de várias linhas em uma única matriz JSON. Essa função é útil para criar uma saída JSON desnormalizada de dados relacionais normalizados:
SELECT
c.CustomerID,
c.CompanyName,
JSON_ARRAYAGG(soh.SalesOrderID) AS OrderIds
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
ON c.CustomerID = soh.CustomerID
GROUP BY c.CustomerID, c.CompanyName;
O resultado será:
CustomerID CompanyName OrderIds
---------- ------------------- ------------------
29825 Contoso Retail [71774,71776,71780]
29847 Adventure Works [71782,71784]
A função coleta todos os valores correspondentes das linhas agrupadas e os combina em uma única matriz JSON. Isso é útil para criar respostas de API desnormalizadas de tabelas de banco de dados normalizadas.
Você pode combinar JSON_ARRAYAGG com JSON_OBJECT para criar matrizes de objetos complexos:
SELECT
pc.Name AS Category,
JSON_ARRAYAGG(
JSON_OBJECT(
'id': p.ProductID,
'name': p.Name,
'price': p.ListPrice
)
) AS Products
FROM SalesLT.ProductCategory AS pc
INNER JOIN SalesLT.Product AS p
ON pc.ProductCategoryID = p.ProductCategoryID
GROUP BY pc.ProductCategoryID, pc.Name;
O seguinte resultado será:
Category Products
-------------- --------------------------------------------------------------------------
Road Bikes [{"id":749,"name":"Road-150 Red, 62","price":3578.27},{"id":750,"name":"Road-150 Red, 44","price":3578.27}]
Mountain Bikes [{"id":771,"name":"Mountain-100 Silver, 38","price":3399.99},{"id":772,"name":"Mountain-100 Black, 38","price":3374.99}]
Importante
JSON_ARRAYAGG e JSON_OBJECT/JSON_ARRAY as funções estão disponíveis no SQL Server 2022 e posterior, no Banco de Dados SQL do Azure e nos bancos de dados SQL no Microsoft Fabric. Para versões anteriores, use FOR JSON PATH para funcionalidades semelhantes.
Validar e verificar JSON com JSON_CONTAINS
Dados JSON de fontes externas podem ser malformados, propriedades esperadas ausentes ou conter valores inesperados. Tentar extrair valores de JSON inválido ou caminhos ausentes pode causar falhas de consulta ou retornar resultados enganosos NULL que mascaram problemas de dados.
O processamento JSON robusto requer codificação defensiva: valide se o JSON está bem formado antes de analisá-lo, verifique se existem caminhos esperados antes de extrair valores e verifique se os valores correspondem às suas expectativas antes de usá-los na lógica de negócios. O SQL Server fornece várias funções para ajudá-lo a validar o conteúdo JSON em cada estágio de processamento.
Entender modos de caminhos permissivos versus restritos
Você pode usar expressões de caminho JSON em dois modos que controlam o tratamento de erros:
DECLARE @json NVARCHAR(MAX) = N'{"name": "Widget", "price": 29.99}';
-- Lax mode (default): Returns NULL for missing paths
SELECT JSON_VALUE(@json, 'lax $.description') AS LaxResult;
-- Strict mode: Raises an error for missing paths
SELECT JSON_VALUE(@json, 'strict $.description') AS StrictResult;
O resultado será:
LaxResult
---------
NULL
-- Strict mode raises: Property cannot be found on the specified JSON path.
Use o modo lax (o padrão) quando as propriedades ausentes forem esperadas e devem fornecer NULL. Use o strict modo quando as propriedades ausentes indicarem um problema de dados que deve gerar um erro.
ISJSON valida se uma cadeia de caracteres contém JSON válido. O exemplo a seguir mostra como usar ISJSON:
SELECT
ISJSON('{"name": "test"}') AS ValidJson, -- Returns 1
ISJSON('not valid json') AS InvalidJson, -- Returns 0
ISJSON(NULL) AS NullJson; -- Returns NULL
O resultado será:
ValidJson InvalidJson NullJson
--------- ----------- --------
1 0 NULL
Use ISJSON em WHERE cláusulas para filtrar linhas com JSON válido ou em CASE expressões para lidar com dados inválidos de forma elegante.
JSON_PATH_EXISTS verifica se existe um caminho específico em um documento JSON, como o exemplo a seguir:
DECLARE @json NVARCHAR(MAX) = N'{"customer": {"name": "Contoso", "tier": "Gold"}}';
SELECT
JSON_PATH_EXISTS(@json, '$.customer.name') AS HasName,
JSON_PATH_EXISTS(@json, '$.customer.email') AS HasEmail;
O resultado será:
HasName HasEmail
------- --------
1 0
Essa função retornará 1 se o caminho existir, 0 se não existir. Use-o antes de chamar JSON_VALUE no modo estrito ou para processar condicionalmente o JSON com estruturas variadas.
Use JSON_CONTAINS para verificar se um documento JSON contém um valor ou objeto específico, como o exemplo a seguir:
DECLARE @json NVARCHAR(MAX) = N'{"tags": ["sql", "database", "azure"]}';
SELECT
JSON_CONTAINS(@json, '"sql"', '$.tags') AS HasSqlTag,
JSON_CONTAINS(@json, '"python"', '$.tags') AS HasPythonTag;
O resultado será:
HasSqlTag HasPythonTag
--------- ------------
1 0
Otimizar consultas JSON com colunas computadas
Quando você consulta frequentemente propriedades JSON específicas, o mecanismo de banco de dados deve analisar o documento JSON para cada linha em cada consulta. Para tabelas com milhares ou milhões de linhas, essa análise repetida cria uma sobrecarga significativa. As colunas computadas permitem extrair valores JSON uma vez e armazená-los em um formato que pode ser consultado que dá suporte à indexação.
Por que a análise JSON afeta o desempenho
Considere uma tabela com 100.000 registros de produto em que cada linha contém um documento JSON com atributos de produto. Uma filtragem de consulta por categoria deve:
- Ler cada linha da tabela
- Analisar o documento JSON para localizar a propriedade de categoria
- Extrair e comparar o valor
Sem otimização, até mesmo filtros simples exigem verificações de tabela completas com análise JSON em cada linha.
Criar colunas computadas para propriedades JSON
Uma coluna computada extrai automaticamente uma propriedade JSON e a disponibiliza como uma coluna regular, como o exemplo a seguir:
-- Add a computed column that extracts a JSON property
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category');
-- The column is now available in queries
SELECT ProductID, ProductName, ProductCategory
FROM Products
WHERE ProductCategory = 'Electronics';
O resultado será:
ProductID ProductName ProductCategory
--------- ------------------- ---------------
101 Wireless Mouse Electronics
102 USB Keyboard Electronics
103 HD Monitor Electronics
Por padrão, as colunas computadas são virtuais. O banco de dados calcula o valor em tempo de consulta, mas pode otimizar a extração JSON. Para obter um desempenho ainda melhor, você pode persistir a coluna computada como no exemplo a seguir:
-- Persisted computed column stores the extracted value physically
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED;
As colunas persistentes armazenam o valor extraído no disco, portanto, o JSON é analisado somente durante INSERT e UPDATE operações, não durante SELECT consultas.
Adicionar índices para filtragem mais rápida
O ganho real de desempenho vem da indexação de colunas computadas:
-- Create an index on the computed column
CREATE INDEX IX_Products_Category ON Products(ProductCategory);
-- Now this query uses an index seek instead of a table scan
SELECT ProductID, ProductName
FROM Products
WHERE ProductCategory = 'Electronics';
Sem o índice, a consulta examina todas as 100.000 linhas. Com o índice, o mecanismo de consulta executa uma busca de índice e recupera apenas linhas correspondentes. Isso pode reduzir o tempo de consulta de segundos para milissegundos.
Indexar várias propriedades JSON
Para consultas que filtram várias propriedades JSON, crie colunas computadas e um índice composto:
-- Extract multiple properties
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED,
ProductBrand AS JSON_VALUE(ProductData, '$.brand') PERSISTED,
ProductPrice AS CAST(JSON_VALUE(ProductData, '$.price') AS DECIMAL(10,2)) PERSISTED;
-- Create a composite index for common query patterns
CREATE INDEX IX_Products_Category_Brand ON Products(ProductCategory, ProductBrand);
-- Create an index for price range queries
CREATE INDEX IX_Products_Price ON Products(ProductPrice);
Agora, as consultas filtradas por categoria e marca ou classificação por preço podem usar esses índices com eficiência.
Dica
Para propriedades JSON acessadas com frequência, colunas computadas com índices podem melhorar o desempenho da consulta em comparação com a análise de JSON no momento da consulta. Monitore os padrões de consulta e crie colunas computadas para propriedades usadas em WHERE, JOIN ou em cláusulas ORDER BY.
Transformar dados relacionais em JSON com FOR JSON
Para uma saída JSON abrangente de consultas, use FOR JSON PATH ou FOR JSON AUTO:
SELECT
p.ProductID,
p.Name,
p.ListPrice,
pc.Name AS CategoryName
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 1000
FOR JSON PATH, ROOT('products');
O resultado será:
{"products":[{"ProductID":749,"Name":"Road-150 Red, 62","ListPrice":3578.27,"CategoryName":"Road Bikes"},{"ProductID":750,"Name":"Road-150 Red, 44","ListPrice":3578.27,"CategoryName":"Road Bikes"}]}
FOR JSON PATH oferece controle sobre a estrutura JSON por meio de aliases de coluna. Use notação de ponto em aliases para criar objetos aninhados:
SELECT
p.ProductID AS 'product.id',
p.Name AS 'product.name',
pc.Name AS 'product.category'
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID = 680
FOR JSON PATH;
O resultado será:
[{"product":{"id":680,"name":"HL Road Frame - Black, 58","category":"Road Frames"}}]
O alias 'product.id' de coluna cria um objeto aninhado product com uma propriedade id. Essa técnica permite que você modele a saída para corresponder ao formato esperado da API sem pós-processamento.
Para obter mais informações sobre funções JSON no SQL Server, consulte os dados JSON no SQL Server e noJSON Functions.