JSON_MODIFY (Transact-SQL)

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL analytics endpoint in Microsoft FabricWarehouse em Microsoft FabricSQL database in 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.

Transact-SQL convenções de sintaxe

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 texto JSON.

JSON_MODIFY retorna 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.

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 pode falhar se a propriedade não puder ser inserida no caminho. Se você não especificar lax ou strict,lax é 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 atualizada. 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 Retorna um erro se o formato do caminho não for válido.

newValue

O novo valor para a propriedade especificada por caminho.

O novo valor deve ser varchar, nvarchar, char, tinyint, smallint, int, bigint, bit, decimal/numéricoou/real float. O tipo de dados de texto não é suportado.

No modo lax, JSON_MODIFY exclui a chave especificada se o novo valor for NULL.

JSON_MODIFY Escapa de 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 JSON produzido por FOR JSON, JSON_QUERYou JSON_MODIFY.

Valor de retorno

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

Remarks

A função JSON_MODIFY 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 lax e no modo estrito. Para obter mais informações sobre a especificação do modo de caminho opcional (lax ou strict), consulte Expressões de caminho JSON.

Novo valor	O caminho existe	Modo lax	Modo restrito
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 objetos $.user ou $.user.settings não existirem, ou se as configurações forem uma matriz ou um valor escalar.	Erro - INVALID_PROPERTY
NULL	Yes	Exclua a propriedade existente.	Defina o valor existente como null.
NULL	No	Nenhuma ação. O primeiro argumento é retornado como resultado.	Erro - INVALID_PROPERTY

No modo lax, JSON_MODIFY tenta criar um novo par 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.

Examples

A. Operações básicas

O exemplo a seguir mostra operações básicas que podem ser feitas com 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;

Aqui está 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_MODIFY, você pode atualizar apenas uma propriedade. Se tiver de fazer várias atualizações, pode utilizar 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;

Aqui está 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 função JSON_MODIFY. Primeiro, você pode pegar o valor de uma propriedade existente e inseri-lo como um novo par 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;

Aqui está o conjunto de resultados.

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

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

D. Incrementar um valor

O exemplo a seguir mostra como incrementar o valor de uma propriedade no texto JSON com a função JSON_MODIFY. Primeiro, você pode pegar o valor da propriedade existente e inseri-lo como um novo par 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;

Aqui está 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 contenha texto JSON formatado corretamente. Como resultado, a saída JSON da função é cercada com aspas duplas e todos os caracteres especiais são escapados, como 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;

Aqui está o conjunto de resultados.

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

Para evitar a fuga automática, forneça newValue usando a JSON_QUERY função. JSON_MODIFY sabe que o valor retornado por JSON_QUERY está formatado corretamente JSON, portanto, ele não escapa do 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;

Aqui está 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