JSON_MODIFY (Transact-SQL)

Si applica a: SQL Server 2016 (13.x) e alle sue 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_QUERYo 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).

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;

Il set di risultati è il seguente.

{
    "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;

Il set di risultati è il seguente.

{
    "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;

Il set di risultati è il seguente.

{
    "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;

Il set di risultati è il seguente.

{
    "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;

Il set di risultati è il seguente.

{
    "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;

Il set di risultati è il seguente.

{
    "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;

Contenuto correlato

• Espressioni di percorso JSON (SQL Server)
• Dati JSON (SQL Server)