Partilhar via

Valide, consulte e altere dados JSON com funções internas

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Azure SQL Database Azure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

O suporte interno para JSON no Mecanismo de Banco de Dados SQL inclui as seguintes funções:

  • 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 as funções JSON (Transact-SQL).

Os exemplos de código neste artigo usam o banco de dados de exemplo AdventureWorks2025 ou AdventureWorksDW2025, que pode ser descarregado da página inicial de Exemplos e Projetos da Comunidade do Microsoft SQL Server.

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
}

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

Validar texto JSON usando a função ISJSON

A ISJSON função testa se uma cadeia de caracteres contém JSON válido.

O exemplo a seguir retorna linhas nas quais a coluna JSON contém texto JSON válido. Sem restrição JSON explícita, você pode inserir qualquer texto na coluna nvarchar :

SELECT *
FROM Families
WHERE ISJSON(doc) > 0;

Para obter mais informações, consulte ISJSON (Transact-SQL).

Extrair um valor do texto JSON usando a função JSON_VALUE

A JSON_VALUE função extrai um valor escalar de uma cadeia de caracteres JSON. A consulta a seguir retorna os documentos em que o id campo JSON corresponde ao valor DesaiFamily, ordenado por city e state campos JSON:

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 desta consulta são mostrados na tabela a seguir:

Name City County
DesaiFamily NY Manhattan

Para obter mais informações, consulte JSON_VALUE.

Extrair um objeto ou uma matriz do texto JSON usando a função JSON_QUERY

A JSON_QUERY função 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 desta consulta são mostrados na tabela a seguir:

Address Parents Parent0
{ "state": "NY", "county": "Manhattan", "city": "NY" } [ { "familyName": "Desai", "givenName": "Prashanth" }, { "familyName": "Miller", "givenName": "Helen" } ] { "familyName": "Desai", "givenName": "Prashanth" }

Para obter mais informações, consulte JSON_QUERY.

Interpretar coleções JSON aninhadas

OPENJSON permite converter uma submatriz JSON num conjunto de linhas e, em seguida, uni-la ao elemento pai. Como exemplo, você pode retornar todos os documentos da família e "juntá-los" com seus children objetos 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 desta consulta são mostrados na tabela a seguir:

Name City givenName grade
DesaiFamily NY Jesse 1
DesaiFamily NY Lisa 8

Duas linhas são retornadas, porque uma linha pai é associada a duas linhas filhas resultantes da análise de dois elementos da submatriz de filhos. OPENJSON função analisa e interpreta children fragmento da doc coluna e retorna grade e givenName de cada elemento como um conjunto de linhas organizadas. Esse conjunto de linhas pode ser unido ao documento pai.

Consultar submatrizes JSON hierárquicas aninhadas

Você pode aplicar várias CROSS APPLY OPENJSON chamadas para consultar estruturas JSON aninhadas. O documento JSON usado neste exemplo tem uma matriz aninhada chamada children, onde cada filho possui uma matriz aninhada de pets. A seguinte consulta faz o parsing dos filhos de cada documento, retorna cada objeto de matriz como linha e, em seguida, faz o parsing da 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 OPENJSON chamada retorna fragmento de children matriz usando a cláusula JSON AS. Este fragmento de matriz é fornecido para a segunda OPENJSON função que retorna givenName, firstName de cada filho, bem como a matriz de pets. A matriz de pets é fornecida para a terceira OPENJSON função que retorna o givenName do animal de estimação.

Os resultados desta consulta são mostrados na tabela a seguir:

familyName nomeDadoDoFilho Nome do Animal
Desai Jesse Goofy
Desai Jesse Shadow
Desai Lisa NULL

O documento raiz é unido com duas children linhas retornadas pela chamada OPENJSON(children) inicial, resultando em duas linhas (ou tuplas). Em seguida, cada linha é unida com as novas linhas geradas por OPENJSON(pets) usando o operador OUTER APPLY. Jesse tem dois animais de estimação, então (Desai, Jesse) é unido com duas linhas geradas para Goofy e Shadow. Lisa não tem os animais de estimação, então não há linhas devolvidas por OPENJSON(pets) nesta 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 devolvida no resultado porque não há linhas de animais de estimação que poderiam ser unidas com esta 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 exemplo de texto JSON.

{
    "a": "[1,2]",
    "b": [1, 2],
    "c": "hi"
}

Neste texto JSON de exemplo, os 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 retornar os seguintes resultados:

Path Devoluções de JSON_VALUE Devoluções 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 AdventureWorks2025 banco de dados de exemplo. Para obter mais informações sobre como adicionar dados JSON para teste executando um script, consulte Test drive built-in JSON support.

Nos exemplos a seguir, a Info coluna na SalesOrder_json tabela contém texto JSON.

Exemplo 1 - Retornar colunas padrão e dados JSON

A consulta a seguir retorna 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, 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 usando a função JSON_MODIFY

A JSON_MODIFY função 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, consulte JSON_MODIFY.

Conteúdo relacionado

  • Last updated on