JSON_MODIFY (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse Analytics

Atualiza o valor de uma propriedade em uma cadeia de caracteres JSON e retorna a cadeia de caracteres JSON atualizada.

Convenções de sintaxe de Transact-SQL

Sintaxe

JSON_MODIFY ( expression , path , newValue )

Argumentos

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

JSON_MODIFY retornará um erro se expression não contiver um JSON válido.

path
Uma expressão de caminho JSON que especifica a propriedade a ser atualizada.

path tem a seguinte sintaxe:

[append] [ lax | strict ] $.<json path>

  • append
    Modificador opcional que especifica que o novo valor deve ser acrescentado à matriz referenciada por <json path>.

  • lax
    Especifica que a propriedade referenciada por <json path> não precisa existir. Se a propriedade não estiver presente, JSON_MODIFY tentará inserir o novo valor no caminho especificado. A inserção poderá falhar se a propriedade não puder ser inserida no caminho. Se você não especificar lax ou strict, lax será o modo padrão.

  • strict
    Especifica que a propriedade referenciada por <json path> deve estar na expressão JSON. Se a propriedade não estiver presente, JSON_MODIFY retornará um erro.

  • <Caminho JSON>
    Especifica o caminho para a propriedade a ser atualizado. 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.

JSON_MODIFY retornará um erro se o formato de path não for válido.

newValue
O novo valor para a propriedade especificada por path.
O novo valor precisa ser um [n]varchar ou texto.

No modo incerto, JSON_MODIFY exclui a chave especificada se o novo valor é NULL.

JSON_MODIFY faz o escape de todos os caracteres especiais no novo valor se o tipo do valor é NVARCHAR ou VARCHAR. Um valor de texto não é seguido de caracteres de escape se ele é um JSON formatado corretamente produzido por FOR JSON, JSON_QUERY ou JSON_MODIFY.

Valor retornado

Retorna o valor atualizado de expression como um texto JSON formatado corretamente.

Comentários

A função JSON_MODIFY permite atualizar o valor de uma propriedade existente, inserir um novo par de chave/valor ou excluir uma chave com base em uma combinação de modos e valores fornecidos.

A tabela a seguir compara o comportamento de JSON_MODIFY 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).

Novo valor O caminho existe Modo incerto Modo estrito
Não NULL Sim Atualize o valor existente. Atualize o valor existente.
Não NULL Não Tente criar um novo par de chave/valor no caminho especificado.

Isso poderá falhar. Por exemplo, se você especificar o caminho $.user.setting.theme, JSON_MODIFY não inserirá a chave theme, caso os objetos $.user ou $.user.settings não existam ou caso as configurações sejam uma matriz ou um valor escalar.		 Erro – INVALID_PROPERTY
NULO Sim Exclua a propriedade existente. Defina o valor existente como nulo.
NULO Não Nenhuma ação. O primeiro argumento é retornado como o resultado. Erro – INVALID_PROPERTY

No modo incerto, JSON_MODIFY tenta criar um novo par de chave/valor, mas em alguns casos, ele pode falhar.

Exemplos

Exemplo – operações básicas

O exemplo a seguir mostra as operações básicas que podem ser executadas com um texto JSON.

Consulta


DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'

PRINT @info

-- Update name  

SET @info=JSON_MODIFY(@info,'$.name','Mike')

PRINT @info

-- Insert surname  

SET @info=JSON_MODIFY(@info,'$.surname','Smith')

PRINT @info

-- Set name NULL 

SET @info=JSON_MODIFY(@info,'strict $.name',NULL)

PRINT @info

-- Delete name  

SET @info=JSON_MODIFY(@info,'$.name',NULL)

PRINT @info

-- Add skill  

SET @info=JSON_MODIFY(@info,'append $.skills','Azure')

PRINT @info

Resultados

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL", "Azure"],
    "surname": "Smith"
}

Exemplo – várias atualizações

Com JSON_MODIFY, você pode atualizar apenas uma propriedade. Se precisar fazer várias atualizações, use várias chamadas JSON_MODIFY.

Consulta

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'

PRINT @info

-- Multiple updates  

SET @info=JSON_MODIFY(JSON_MODIFY(JSON_MODIFY(@info,'$.name','Mike'),'$.surname','Smith'),'append $.skills','Azure')

PRINT @info

Resultados

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL", "Azure"],
    "surname": "Smith"
}

Exemplo – renomear uma chave

O exemplo a seguir mostra como renomear uma propriedade em texto JSON com a função JSON_MODIFY. Primeiro, você pode usar o valor de uma propriedade existente e inseri-lo como um novo par de chave/valor. Em seguida, exclua a chave antiga definindo o valor da propriedade antiga como NULL.

Consulta

DECLARE @product NVARCHAR(100)='{"price":49.99}'

PRINT @product

-- Rename property  

SET @product=
 JSON_MODIFY(
  JSON_MODIFY(@product,'$.Price',CAST(JSON_VALUE(@product,'$.price') AS NUMERIC(4,2))),
  '$.price',
  NULL
 )

PRINT @product

Resultados

{
    "price": 49.99
} {
    "Price": 49.99
}

Se você não converter o novo valor em um tipo numérico, JSON_MODIFY o tratará como texto e o colocará entre aspas duplas.

Exemplo – incrementar um valor

O exemplo a seguir mostra como incrementar o valor de uma propriedade em texto JSON com a função JSON_MODIFY. Primeiro, você pode usar o valor da propriedade existente e inseri-lo como um novo par de chave/valor. Em seguida, exclua a chave antiga definindo o valor da propriedade antiga como NULL.

Consulta

DECLARE @stats NVARCHAR(100)='{"click_count": 173}'

PRINT @stats

-- Increment value  

SET @stats=JSON_MODIFY(@stats,'$.click_count',
 CAST(JSON_VALUE(@stats,'$.click_count') AS INT)+1)

PRINT @stats

Resultados

{
    "click_count": 173
} {
    "click_count": 174
}

Exemplo – modificar um objeto JSON

JSON_MODIFY trata o argumento newValue como um texto sem formatação, mesmo que ele contém um texto JSON formatado corretamente. Como resultado, a saída JSON da função é colocada entre aspas duplas e todos os caracteres especiais têm escape, conforme mostrado no exemplo a seguir.

Consulta

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'

PRINT @info

-- Update skills array

SET @info=JSON_MODIFY(@info,'$.skills','["C#","T-SQL","Azure"]')

PRINT @info

Resultados

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "John",
    "skills": "[\"C#\",\"T-SQL\",\"Azure\"]"
}

Para evitar o escape automático, forneça newValue usando a função JSON_QUERY. JSON_MODIFY sabe que o valor retornado por JSON_QUERY é um JSON formatado corretamente e, portanto, ele não faz o escape do valor.

Consulta

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'

PRINT @info

-- Update skills array  

SET @info=JSON_MODIFY(@info,'$.skills',JSON_QUERY('["C#","T-SQL","Azure"]'))

PRINT @info

Resultados

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "John",
    "skills": ["C#", "T-SQL", "Azure"]
}

Exemplo – atualizar uma coluna JSON

O exemplo a seguir atualiza o valor de uma propriedade em uma coluna de tabela que contém JSON.

UPDATE Employee
SET jsonCol=JSON_MODIFY(jsonCol,'$.info.address.town','London')
WHERE EmployeeID=17

Consulte Também