JSON_MODIFY (Transact-SQL)

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

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 a expressão não contiver JSON válido.

caminho

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.

• <json path>

  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 deve ser varchar, nvarchar ou text.

No modo incerto, JSON_MODIFY excluirá a chave especificada se o novo valor for NULL.

JSON_MODIFY Escapa todos os caracteres especiais no novo valor se o tipo do valor for varchar ou nvarchar. Um valor de texto não será escapado se estiver formatado corretamente como JSON 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 JSON_MODIFY função permite atualizar o valor de uma propriedade existente, inserir um novo par 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
NOT NULL	Sim	Atualize o valor existente.	Atualize o valor existente.
NOT NULL	Não	Tente criar um novo par chave-valor no caminho especificado. Isso pode falhar. Por exemplo, se você especificar o caminho $.user.setting.theme, JSON_MODIFY não inserirá a chave theme se os $.user objetos ou $.user.settings não existirem ou se settings for uma matriz ou um valor escalar.	Erro – INVALID_PROPERTY
NULL	Sim	Exclua a propriedade existente.	Defina o valor existente como nulo.
NULL	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, pode falhar.

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

Exemplos

R. Operações básicas

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

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;

Veja a seguir o conjunto de 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"
}

B. Várias atualizações

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

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;

Veja a seguir o conjunto de resultados.

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

C. Renomear uma chave

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

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;

Veja a seguir o conjunto de 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.

D. 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, você pode excluir a chave antiga definindo o valor da propriedade antiga como NULL.

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;

Veja a seguir o conjunto de resultados.

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

E. Modificar um objeto JSON

JSON_MODIFY trata o argumento newValue como texto simples, mesmo que contenha 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.

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;

Veja a seguir o conjunto de 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 está formatado corretamente em JSON; portanto, não usa escape para o valor.

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;

Veja a seguir o conjunto de resultados.

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

F. 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;

Conteúdo relacionado

• Expressões de demarcador JSON (SQL Server)
• Dados JSON (SQL Server)