JSON_MODIFY (Transact-SQL)
Si applica a: SQL Server 2016 (13.x) e versioni successive
database SQL di Azure Istanza gestita di SQL di Azure
Azure Synapse Analytics
Aggiorna il valore di una proprietà in una stringa JSON e restituisce la stringa JSON aggiornata.
Convenzioni relative alla sintassi Transact-SQL
Sintassi
JSON_MODIFY ( expression , path , newValue )
Argomenti
expression
Espressione. In genere il nome di una variabile o di una colonna che contiene testo JSON.
JSON_MODIFY
restituisce un errore se expression non contiene codice JSON valido.
path
Espressione corrispondente a un percorso JSON che specifica la proprietà da aggiornare.
La sintassi di path è la seguente:
[append] [ lax | strict ] $.<json path>
append
Modificatore facoltativo che specifica che il nuovo valore deve essere aggiunto alla matrice a cui fa riferimento
<json path>
.lax
Specifica che la proprietà a cui fa
<json path>
riferimento non deve esistere. Se la proprietà non è presente,JSON_MODIFY
tenta di inserire il nuovo valore nel percorso specificato. L'inserimento può non riuscire se la proprietà non può essere inserita nel percorso. Se non si specifica né lax né strict, lax è la modalità predefinita.strict
Specifica che la proprietà a cui fa riferimento
<json path>
deve essere presente nell'espressione JSON. Se la proprietà non è presente,JSON_MODIFY
restituisce un errore.<json path>
Specifica il percorso della proprietà da aggiornare. Per altre informazioni, vedere Espressioni di percorso JSON (SQL Server).
In SQL Server 2017 (14.x) e in Azure SQL Database è possibile fornire una variabile come valore del percorso.
Se il formato di path non è valido,
JSON_MODIFY
restituisce un errore.
newValue
Nuovo valore della proprietà specificata da path.
Il nuovo valore deve essere varchar, nvarchar o text.
In modalità lax, se il nuovo valore è JSON_MODIFY
, NULL
elimina la chiave specificata.
JSON_MODIFY
escape di tutti i caratteri speciali nel nuovo valore se il tipo del valore è varchar o nvarchar. Un valore di testo non viene preceduto da un carattere di escape se il codice JSON è formattato correttamente prodotto da FOR JSON
, JSON_QUERY
o JSON_MODIFY
.
Valore restituito
Restituisce il valore aggiornato di expression come testo JSON correttamente formattato.
Osservazioni:
La JSON_MODIFY
funzione consente di aggiornare il valore di una proprietà esistente, inserire una nuova coppia key:value o eliminare una chiave in base a una combinazione di modalità e valori forniti.
Nella tabella seguente viene confrontato il comportamento di JSON_MODIFY
in modalità lax e in modalità strict. Per altre informazioni sulla specifica facoltativa della modalità del percorso (lax o strict), vedere Espressioni di percorso JSON (SQL Server).
Nuovo valore | Percorso esistente | Modalità lax | Modalità strict |
---|---|---|---|
NOT NULL |
Sì | Aggiorna il valore esistente. | Aggiorna il valore esistente. |
NOT NULL |
No | Provare a creare una nuova coppia chiave-valore nel percorso specificato. Questo potrebbe non riuscire. Ad esempio, se si specifica il percorso $.user.setting.theme , JSON_MODIFY non inserisce la chiave theme se gli $.user oggetti o $.user.settings non esistono o se le impostazioni sono una matrice o un valore scalare. |
Errore: INVALID_PROPERTY |
NULL |
Sì | Elimina la proprietà esistente. | Imposta il valore esistente su Null. |
NULL |
No | Nessuna azione. Il primo argomento viene restituito come risultato. | Errore: INVALID_PROPERTY |
In modalità lax, JSON_MODIFY
tenta di creare una nuova coppia chiave:valore, ma in alcuni casi l'operazione non riesce.
Le funzioni JSON funzionano allo stesso modo se il documento JSON viene archiviato in varchar, nvarchar o nel tipo di dati json nativo.
Esempi
R. Operazioni di base
L'esempio seguente illustra le operazioni di base che è possibile eseguire con testo 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;
Questo è il set di risultati.
{
"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. Più aggiornamenti
Con JSON_MODIFY
è possibile aggiornare una sola proprietà. Se è necessario eseguire più aggiornamenti, è possibile usare più JSON_MODIFY
chiamate.
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;
Questo è il set di risultati.
{
"name": "John",
"skills": ["C#", "SQL"]
} {
"name": "Mike",
"skills": ["C#", "SQL", "Azure"],
"surname": "Smith"
}
C. Rinominare una chiave
L'esempio seguente illustra come rinominare una proprietà nel testo JSON con la JSON_MODIFY
funzione . Prima è possibile inserire il valore di una proprietà esistente come nuova coppia chiave-valore. È quindi possibile eliminare la chiave precedente impostando il valore della proprietà precedente su 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;
Questo è il set di risultati.
{
"price": 49.99
} {
"Price": 49.99
}
Se si non esegue il cast del nuovo valore a un tipo numerico, JSON_MODIFY
lo considera come testo e lo racchiude tra virgolette doppie.
D. Incrementare un valore
L'esempio seguente illustra come incrementare il valore di una proprietà all'interno di testo JSON con la funzione JSON_MODIFY
. Prima è possibile inserire il valore della proprietà esistente come nuova coppia chiave-valore. È quindi possibile eliminare la chiave precedente impostando il valore della proprietà precedente su 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;
Questo è il set di risultati.
{
"click_count": 173
} {
"click_count": 174
}
E. Modificare un oggetto JSON
JSON_MODIFY
considera l'argomento newValue come testo normale anche se contiene testo JSON correttamente formattato. Di conseguenza, l'output JSON della funzione è racchiuso tra virgolette doppie e tutti i caratteri speciali sono sottoposti a escape, come illustrato nell'esempio seguente.
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;
Questo è il set di risultati.
{
"name": "John",
"skills": ["C#", "SQL"]
} {
"name": "John",
"skills": "[\"C#\",\"T-SQL\",\"Azure\"]"
}
Per evitare l'escape automatico, specificare newValue tramite la funzione JSON_QUERY
. JSON_MODIFY
sa che il valore restituito da JSON_QUERY
è codice JSON correttamente formattato e quindi non lo sottopone a escape.
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;
Questo è il set di risultati.
{
"name": "John",
"skills": ["C#", "SQL"]
} {
"name": "John",
"skills": ["C#", "T-SQL", "Azure"]
}
F. Aggiornare una colonna JSON
L'esempio seguente aggiorna il valore di una proprietà in una colonna di tabella contenente codice JSON.
UPDATE Employee
SET jsonCol = JSON_MODIFY(jsonCol, '$.info.address.town', 'London')
WHERE EmployeeID = 17;