JSON_MODIFY (Transact-SQL)

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores do Banco de Dados SQL doAzure Azure Instância Gerenciada de SQLdo Azure Synapse Analytics ponto deextremidade de análise de SQL no Microsoft Fabric Warehouse no Banco de Dados SQLdo Microsoft Fabricno Microsoft Fabric

A JSON_MODIFY sintaxe 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

Syntax

JSON_MODIFY ( expression , path , newValue )

Arguments

expression

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.

path

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

O caminho 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 frouxo ou estrito, frouxo 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, consulte Expressões de caminho JSON.

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

  JSON_MODIFY retornará um erro se o formato do caminho não for válido.

newValue

O novo valor da propriedade especificada por caminho.

O novo valor deve ser varchar, nvarchar, char, tinyint, smallint, int, bigint, bit, decimal/numeric ou real/float. Não há suporte para o tipo de dados de texto .

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

JSON_MODIFY escapará 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 de retorno

Retorna o valor atualizado da expressão como texto JSON formatado corretamente.

Remarks

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 do modo de caminho opcional (frouxo ou estrito), consulte Expressões de Caminho JSON.

Novo valor	O caminho existe	Modo lax	Modo rigoroso
NOT NULL	Yes	Atualize o valor existente.	Atualize o valor existente.
NOT NULL	No	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	Yes	Exclua a propriedade existente.	Defina o valor existente como nulo.
NULL	No	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 é armazenado em varchar, nvarchar ou no tipo de dados json nativo.

Examples

A. 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. Múltiplas 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 sem formatação, mesmo que ele 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 JSON_QUERY função. 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 caminho JSON
• Dados JSON no SQL Server