JSON_MODIFY (Transact-SQL)

Aplica-se a: SQL Server 2016 (13.x) e posterior Banco de Dados SQL do AzureInstância Gerenciada SQL do Azureponto de extremidade do Azure Synapse AnalyticsSQL Analytics no Microsoft FabricWarehouse no Microsoft Fabric

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

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

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

caminho

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

caminho tem a seguinte sintaxe:

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

• acrescentar

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

• laxista

  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 estrito, lax é o modo padrão.

• rigoroso

  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 (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 caminho.

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

novoValor

O novo valor para a propriedade especificada por caminho.

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

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 de expressão como texto JSON formatado corretamente.

Comentários

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 JSON Path Expressions (SQL Server).

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, quer o documento JSON esteja armazenado em varchar, nvarcharou no tipo de dados nativo json.

Exemplos

Um. 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. Várias 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 função JSON_QUERY. 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 (SQL Server)
• dados JSON no SQL Server