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_QUERY
ou 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).
Nouvelle valeur | Chemin existant | Mode lax | Mode strict |
---|---|---|---|
NOT NULL |
Oui | Mettre à jour la valeur existante. | Mettre à jour la valeur existante. |
NOT NULL |
Non | Essayez de créer une paire clé-valeur sur le chemin d’accès spécifié. Cela peut échouer. Par exemple, si vous spécifiez le chemin d’accès $.user.setting.theme , JSON_MODIFY n’insère pas la clé theme si le ou $.user.settings les $.user objets n’existent pas, ou si les paramètres sont un tableau ou une valeur scalaire. |
Erreur : INVALID_PROPERTY |
NULL |
Oui | Supprimer la propriété existante. | Affecter à la valeur existante la valeur Null. |
NULL |
Non | Aucune action. Le premier argument est retourné en tant que résultat. | Erreur : INVALID_PROPERTY |
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é NULL
sur .
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é NULL
sur .
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;