JSON_MODIFY (Transact-SQL)

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

Met à jour la valeur d’une propriété dans une chaîne JSON et renvoie la chaîne JSON mise à jour.

Conventions de la syntaxe Transact-SQL

Syntaxe

JSON_MODIFY ( expression , path , newValue )

Arguments

expression

Expression. En règle générale, nom d’une variable ou d’une colonne qui contient du texte JSON.

JSON_MODIFY retourne une erreur si expression ne contient pas de code JSON valide.

path

Expression de chemin JSON qui spécifie la propriété à mettre à jour.

La syntaxe de path est la suivante :

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

• append

  Modificateur facultatif qui spécifie que la nouvelle valeur doit être ajoutée au tableau référencé par <json path>.

• lax

  Spécifie que la propriété référencée par <json path> n’a pas besoin d’exister. Si la propriété n’est pas présente, JSON_MODIFY tente d’insérer la nouvelle valeur sur le chemin spécifié. L’insertion peut échouer si la propriété ne peut pas être insérée dans le chemin. Si vous ne spécifiez pas lax ou strict, lax est le mode par défaut.

• strict

  Spécifie que la propriété référencée par <json path> doit être dans l’expression JSON. Si la propriété n’est pas présente, JSON_MODIFY retourne une erreur.

• <json path>

  Spécifie le chemin de la propriété à mettre à jour. Pour plus d’informations, consultez Expressions de chemin d’accès JSON (SQL Server).

  Dans SQL Server 2017 (14.x) et Azure SQL Database, vous pouvez fournir une variable comme valeur de path.

  JSON_MODIFY renvoie une erreur si le format de chemin d'accès n’est pas valide.

newValue

Nouvelle valeur de la propriété spécifiée par path.

La nouvelle valeur doit être varchar, nvarchar ou text.

En mode lax, JSON_MODIFY supprime la clé spécifiée si la nouvelle valeur est NULL.

JSON_MODIFY échappe tous les caractères spéciaux dans la nouvelle valeur si le type de la valeur est varchar ou nvarchar. Une valeur de texte n’est pas échappée si elle est correctement mise en forme JSON produite par FOR JSON, JSON_QUERYou JSON_MODIFY.

Valeur retournée

Retourne la valeur mise à jour de expression sous forme de texte JSON correctement formaté.

Notes

La JSON_MODIFY fonction vous permet de mettre à jour la valeur d’une propriété existante, d’insérer une nouvelle paire clé :valeur ou de supprimer une clé en fonction d’une combinaison de modes et de valeurs fournies.

Le tableau suivant compare le comportement de JSON_MODIFY en mode lax et en mode strict. Pour plus d’informations sur la spécification du mode de chemin d’accès facultatif (lax ou strict), consultez Expressions de chemin d’accès JSON (SQL Server).

En mode lax, JSON_MODIFY tente de créer une nouvelle paire clé-valeur, mais dans certains cas, cela peut échouer.

Les fonctions JSON fonctionnent de la même façon que le document JSON soit stocké dans varchar, nvarchar ou dans le type de données json natif.

Exemples

R. Opérations de base

L’exemple suivant montre des opérations de base réalisables avec du texte 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;

Voici le jeu de résultats obtenu.

{
    "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. Mises à jour multiples

Avec JSON_MODIFY, vous ne pouvez mettre à jour qu’une seule propriété. Si vous devez effectuer plusieurs mises à jour, vous pouvez utiliser plusieurs JSON_MODIFY appels.

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;

Voici le jeu de résultats obtenu.

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

C. Renommer une clé

L’exemple suivant montre comment renommer une propriété en texte JSON avec la JSON_MODIFY fonction. Tout d’abord, vous pouvez prendre la valeur d’une propriété existante et l’insérer en tant que nouvelle paire clé-valeur. Vous pouvez ensuite supprimer l’ancienne clé en définissant la valeur de l’ancienne propriété NULLsur .

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;

Voici le jeu de résultats obtenu.

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

Si vous ne castez pas la nouvelle valeur en type numérique, JSON_MODIFY la traite comme du texte et la met entre guillemets doubles.

D. Incrémenter une valeur

L’exemple suivant montre comment incrémenter la valeur d’une propriété dans du texte JSON avec la fonction JSON_MODIFY. Tout d’abord, vous pouvez prendre la valeur de la propriété existante et l’insérer en tant que nouvelle paire clé-valeur. Vous pouvez ensuite supprimer l’ancienne clé en définissant la valeur de l’ancienne propriété NULLsur .

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;

Voici le jeu de résultats obtenu.

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

E. Modifier un objet JSON

JSON_MODIFY traite l’argument newValue comme du texte brut même s’il contient du texte JSON correctement formaté. Par conséquent, la sortie JSON de la fonction est mise entre guillemets doubles et tous les caractères spéciaux sont échappés, comme le montre l’exemple suivant.

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;

Voici le jeu de résultats obtenu.

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

Pour éviter un échappement automatique, fournissez newValue en utilisant la fonction JSON_QUERY. JSON_MODIFY sait que la valeur retournée par JSON_QUERY est une valeur JSON au format approprié. La valeur ne fait donc pas l’objet d’une séquence d’échappement.

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;

Voici le jeu de résultats obtenu.

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

F. Mettre à jour une colonne JSON

L’exemple suivant met à jour la valeur d’une propriété dans une colonne de table contenant du texte JSON.

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

Contenu connexe

• Expressions de chemin JSON (SQL Server)
• Données JSON (SQL Server)