JSON_VALUE (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e posterior Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics

Extrai um valor escalar de uma cadeia de caracteres JSON.

Para extrair um objeto ou uma matriz de uma cadeia de caracteres JSON em vez de um valor escalar, confira JSON_QUERY (Transact-SQL). Para obter informações sobre as diferenças entre JSON_VALUE e JSON_QUERY, confira Comparar JSON_VALUE e JSON_QUERY.

Convenções de sintaxe de Transact-SQL

Sintaxe

JSON_VALUE ( expression , path )

Argumentos

expressão

Uma expressão. Normalmente, o nome de uma variável ou de uma coluna que contém o texto JSON.

Se JSON_VALUE encontrar JSON que não é válido na expressão antes de encontrar o valor identificado por path, a função retornará um erro. Se JSON_VALUE não encontrar o valor identificado por path, verificará todo o texto e retornará um erro se encontrar JSON que não é válido em algum lugar da expressão.

caminho

Um demarcador JSON que especifica a propriedade a ser extraída. Para obter mais informações, confira Expressões de demarcador JSON (SQL Server).

No SQL Server 2017 (14.x) e no Banco de Dados SQL do Azure, você pode fornecer uma variável como o valor de path.

Se o formato de path não for válido, JSON_VALUE retornará um erro.

Valor retornado

Retorna um valor de texto único do tipo nvarchar(4000). A ordenação do valor retornado é a mesma que a ordenação da expressão de entrada.

Se o valor tiver mais que 4000 caracteres:

  • No modo lax, JSON_VALUE retorna NULL.

  • No modo estrito, JSON_VALUE retorna um erro.

Se você tiver que retornar valores escalares maiores que 4000 caracteres, use OPENJSON em vez de JSON_VALUE. Para obter mais informações, veja OPENJSON (Transact-SQL).

As funções JSON funcionam da mesma forma se o documento JSON estiver armazenado em varchar, nvarchar ou no tipo de dados json nativo.

Comentários

Modo incerto e modo estrito

Considere o seguinte texto JSON:

DECLARE @jsonInfo NVARCHAR(MAX)

SET @jsonInfo=N'{  
     "info":{    
       "type":1,  
       "address":{    
         "town":"Bristol",  
         "county":"Avon",  
         "country":"England"  
       },  
       "tags":["Sport", "Water polo"]  
    },  
    "type":"Basic"  
 }'

A tabela a seguir compara o comportamento de JSON_VALUE no modo incerto e no modo estrito. Para obter mais informações sobre a especificação de modo de demarcador opcional (incerto ou estrito), confira Expressões de demarcador JSON (SQL Server).

Caminho Valor retornado no modo incerto Valor retornado no modo estrito Obter mais informações
$ NULL Erro Não é um valor escalar.

Use o JSON_QUERY em vez disso.
$.info.type N'1' N'1' N/A
$.info.address.town N'Bristol' N'Bristol' N/A
$.info."address" NULL Erro Não é um valor escalar.

Use o JSON_QUERY em vez disso.
$.info.tags NULL Erro Não é um valor escalar.

Use o JSON_QUERY em vez disso.
$.info.type[0] NULL Erro Não é uma matriz.
$.info.none NULL Erro A propriedade não existe.

Exemplos

Exemplo 1

O exemplo a seguir usa os valores das propriedades JSON town e state nos resultados da consulta. Como JSON_VALUE preserva a ordenação da origem, a ordem de classificação dos resultados depende da ordenação da coluna jsonInfo.

Observação

(Este exemplo pressupõe que uma tabela nomeada Person.Person contenha uma coluna jsonInfo de texto JSON e que esta coluna tenha a estrutura mostrada anteriormente na discussão do modo incerto e do modo estrito. No banco de dados de amostra AdventureWorks, a tabela Person não contém de fato uma coluna jsonInfo.)

SELECT FirstName, LastName,
 JSON_VALUE(jsonInfo,'$.info.address.town') AS Town
FROM Person.Person
WHERE JSON_VALUE(jsonInfo,'$.info.address.state') LIKE 'US%'
ORDER BY JSON_VALUE(jsonInfo,'$.info.address.town')

Exemplo 2

O exemplo a seguir extrai o valor da propriedade JSON town para uma variável local.

DECLARE @jsonInfo NVARCHAR(MAX)
DECLARE @town NVARCHAR(32)

SET @jsonInfo=N'{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}';

SET @town=JSON_VALUE(@jsonInfo,'$.info.address[0].town'); -- Paris
SET @town=JSON_VALUE(@jsonInfo,'$.info.address[1].town'); -- London

Exemplo 3

O exemplo a seguir cria as colunas computadas com base nos valores das propriedades JSON.

CREATE TABLE dbo.Store
 (
  StoreID INT IDENTITY(1,1) NOT NULL,
  Address VARCHAR(500),
  jsonContent NVARCHAR(4000),
  Longitude AS JSON_VALUE(jsonContent, '$.address[0].longitude'),
  Latitude AS JSON_VALUE(jsonContent, '$.address[0].latitude')
 )

Conteúdo relacionado