Microsoft SQL Server
Modifica il testo JSON

Modifica il valore nel testo JSON sul percorso specificato

La funzione JSON_MODIFY utilizza il testo JSON come parametro di input e modifica un valore sul percorso specificato utilizzando il terzo argomento:

declare @json nvarchar(4000) = N'{"Id":1,"Name":"Toy Car","Price":34.99}'
set @json = JSON_MODIFY(@json, '$.Price', 39.99)
print @json -- Output: {"Id":1,"Name":"Toy Car","Price":39.99}

Di conseguenza, avremo un nuovo testo JSON con "Prezzo": 39,99 e l'altro valore non verrà modificato. Se l'oggetto sul percorso specificato non esiste, JSON_MODIFY inserirà la chiave: coppia di valori.

Per eliminare la chiave: coppia di valori, inserisci NULL come nuovo valore:

declare @json nvarchar(4000) = N'{"Id":1,"Name":"Toy Car","Price":34.99}'
set @json = JSON_MODIFY(@json, '$.Price', NULL)
print @json -- Output: {"Id":1,"Name":"Toy Car"}

JSON_MODIFY per impostazione predefinita cancellerà la chiave se non ha valore, quindi puoi usarla per eliminare una chiave.

Aggiungi un valore scalare in un array JSON

JSON_MODIFY ha la modalità 'aggiungi' che aggiunge valore alla matrice.

declare @json nvarchar(4000) = N'{"Id":1,"Name":"Toy Car","Tags":["toy","game"]}'
set @json = JSON_MODIFY(@json, 'append $.Tags', 'sales')
print @json -- Output: {"Id":1,"Name":"Toy Car","Tags":["toy","game","sales"]}

Se la matrice sul percorso specificato non esiste, JSON_MODIFY (append) creerà una nuova matrice con un singolo elemento:

declare @json nvarchar(4000) = N'{"Id":1,"Name":"Toy Car","Price":34.99}'
set @json = JSON_MODIFY(@json, 'append $.Tags', 'sales')
print @json -- Output {"Id":1,"Name":"Toy Car","Tags":["sales"]}

Inserisci un nuovo oggetto JSON nel testo JSON

La funzione JSON_MODIFY consente di inserire oggetti JSON nel testo JSON:

declare @json nvarchar(4000) = N'{"Id":1,"Name":"Toy Car"}'
set @json = JSON_MODIFY(@json, '$.Price', 
                        JSON_QUERY('{"Min":34.99,"Recommended":45.49}'))
print @json
-- Output: {"Id":1,"Name":"Toy Car","Price":{"Min":34.99,"Recommended":45.49}}

Poiché il terzo parametro è testo, è necessario avvolgerlo con la funzione JSON_QUERY per "trasmettere" il testo a JSON. Senza questo "cast", JSON_MODIFY tratterà il terzo parametro come testo normale e sfuggirà ai caratteri prima di inserirlo come valore stringa. Senza JSON_QUERY i risultati saranno:

{"Id":1,"Name":"Toy Car","Price":'{\"Min\":34.99,\"Recommended\":45.49}'}

JSON_MODIFY inserirà questo oggetto se non esiste o lo eliminerà se il valore del terzo parametro è NULL.

Inserire un nuovo array JSON generato con la query FOR JSON

È possibile generare oggetti JSON utilizzando la query SELECT standard con la clausola FOR JSON e inserirla nel testo JSON come terzo parametro:

declare @json nvarchar(4000) = N'{"Id":17,"Name":"WWI"}'
set @json = JSON_MODIFY(@json, '$.tables', 
                        (select name from sys.tables FOR JSON PATH) )
print @json

(1 row(s) affected)
{"Id":1,"Name":"master","tables":[{"name":"Colors"},{"name":"Colors_Archive"},{"name":"OrderLines"},{"name":"PackageTypes"},{"name":"PackageTypes_Archive"},{"name":"StockGroups"},{"name":"StockItemStockGroups"},{"name":"StockGroups_Archive"},{"name":"StateProvinces"},{"name":"CustomerTransactions"},{"name":"StateProvinces_Archive"},{"name":"Cities"},{"name":"Cities_Archive"},{"name":"SystemParameters"},{"name":"InvoiceLines"},{"name":"Suppliers"},{"name":"StockItemTransactions"},{"name":"Suppliers_Archive"},{"name":"Customers"},{"name":"Customers_Archive"},{"name":"PurchaseOrders"},{"name":"Orders"},{"name":"People"},{"name":"StockItems"},{"name":"People_Archive"},{"name":"ColdRoomTemperatures"},{"name":"ColdRoomTemperatures_Archive"},{"name":"VehicleTemperatures"},{"name":"StockItems_Archive"},{"name":"Countries"},{"name":"StockItemHoldings"},{"name":"sysdiagrams"},{"name":"PurchaseOrderLines"},{"name":"Countries_Archive"},{"name":"DeliveryMethods"},{"name":"DeliveryMethods_Archive"},{"name":"PaymentMethods"},{"name":"SupplierTransactions"},{"name":"PaymentMethods_Archive"},{"name":"TransactionTypes"},{"name":"SpecialDeals"},{"name":"TransactionTypes_Archive"},{"name":"SupplierCategories"},{"name":"SupplierCategories_Archive"},{"name":"BuyingGroups"},{"name":"Invoices"},{"name":"BuyingGroups_Archive"},{"name":"CustomerCategories"},{"name":"CustomerCategories_Archive"}]}

JSON_MODIFY saprà che selezionare la query con la clausola FOR JSON genera un array JSON valido e lo inserirà semplicemente nel testo JSON.

È possibile utilizzare tutte le opzioni FOR JSON nella query SELECT, ad eccezione di WITHOUT_ARRAY_WRAPPER , che genererà un singolo oggetto anziché un array JSON. Vedi altro esempio in questo argomento per vedere come inserire un singolo oggetto JSON.

Inserisci un singolo oggetto JSON generato con la clausola FOR JSON

È possibile generare oggetti JSON utilizzando la query SELECT standard con la clausola FOR JSON e l'opzione WITHOUT_ARRAY_WRAPPER e inserirla nel testo JSON come terzo parametro:

declare @json nvarchar(4000) = N'{"Id":17,"Name":"WWI"}'
set @json = JSON_MODIFY(@json, '$.table', 
                        JSON_QUERY(
                         (select name, create_date, schema_id
                           from sys.tables
                           where name = 'Colors' 
                           FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)))
print @json

(1 row(s) affected)
{"Id":17,"Name":"WWI","table":{"name":"Colors","create_date":"2016-06-02T10:04:03.280","schema_id":13}}

FOR JSON con WITHOUT_ARRAY_WRAPPER l'opzione può generare testo JSON non valido se la query SELECT restituisce più di un risultato (in questo caso è necessario utilizzare TOP 1 o filtro per chiave primaria). Pertanto, JSON_MODIFY assumerà che il risultato restituito sia solo un testo semplice e lo sfugga come qualsiasi altro testo se non lo si avvolge con la funzione JSON_QUERY.

È necessario eseguire il wrapping della query FOR JSON, WITHOUT_ARRAY_WRAPPER con la funzione JSON_QUERY per poter eseguire il risultato su JSON.