Validar, consultar e alterar dados JSON com funções internas (SQL Server)

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstâ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.

Este artigo requer o banco de dados de exemplo AdventureWorks2022, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.

Texto JSON para os exemplos nesta página

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)
);

Validar texto JSON por meio da função ISJSON

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, veja ISJSON (Transact-SQL).

Extrair um valor de texto JSON por meio da função JSON_VALUE

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, veja JSON_VALUE (Transact-SQL).

Extrair um objeto ou uma matriz de texto JSON por meio da função JSON_QUERY

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, veja JSON_QUERY (Transact-SQL).

Analisar coleção JSON aninhadas

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.

Consultar submatrizes JSON hierárquicas aninhadas

É 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.

Comparar JSON_VALUE e JSON_QUERY

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 JSON_VALUE e JSON_QUERY com o banco de dados de exemplo AdventureWorks

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.

Exemplo 1 - Retornar dados JSON e colunas padrão

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;

Exemplo 2- Agregar e filtrar valores JSON

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;

Atualizar valores de propriedade em texto JSON por meio da função JSON_MODIFY

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, veja JSON_MODIFY (Transact-SQL).

Conteúdo relacionado