Microsoft SQL Server
Modificar texto JSON

Modificar valor en texto JSON en la ruta especificada

La función JSON_MODIFY utiliza el texto JSON como parámetro de entrada y modifica un valor en la ruta especificada utilizando el tercer argumento:

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}

Como resultado, tendremos un nuevo texto JSON con "Precio": 39.99 y el otro valor no se cambiará. Si el objeto en la ruta especificada no existe, JSON_MODIFY insertará el par clave: valor.

Para eliminar el par clave: valor, ponga NULL como nuevo valor:

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 eliminará la clave por defecto si no tiene valor, por lo que puede usarla para eliminar una clave.

Agregar un valor escalar en una matriz JSON

JSON_MODIFY tiene el modo 'agregar' que agrega valor a la matriz.

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"]}

Si la matriz en la ruta especificada no existe, JSON_MODIFY (anexar) creará una nueva matriz con un solo 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"]}

Insertar nuevo objeto JSON en texto JSON

La función JSON_MODIFY le permite insertar objetos JSON en el texto 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}}

Como el tercer parámetro es texto, debe envolverlo con la función JSON_QUERY para "convertir" el texto a JSON. Sin este "cast", JSON_MODIFY tratará el tercer parámetro como texto sin formato y caracteres de escape antes de insertarlo como valor de cadena. Sin JSON_QUERY los resultados serán:

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

JSON_MODIFY insertará este objeto si no existe, o lo eliminará si el valor del tercer parámetro es NULL.

Insertar nueva matriz JSON generada con la consulta FOR JSON

Puede generar el objeto JSON utilizando la consulta SELECT estándar con la cláusula FOR JSON e insertarlo en el texto JSON como tercer parámetro:

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 sabrá que la consulta de selección con la cláusula FOR JSON genera una matriz JSON válida y solo la insertará en el texto JSON.

Puede usar todas las opciones de FOR JSON en la consulta SELECT, excepto WITHOUT_ARRAY_WRAPPER , que generará un solo objeto en lugar de la matriz JSON. Vea otro ejemplo en este tema para ver cómo insertar un solo objeto JSON.

Insertar un solo objeto JSON generado con la cláusula FOR JSON

Puede generar el objeto JSON utilizando la consulta SELECT estándar con la cláusula FOR JSON y la opción WITHOUT_ARRAY_WRAPPER, e insertarlo en el texto JSON como tercer parámetro:

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}}

La opción FOR JSON with WITHOUT_ARRAY_WRAPPER puede generar texto JSON no válido si la consulta SELECT devuelve más de un resultado (en este caso, debe usar TOP 1 o filtrar por clave principal). Por lo tanto, JSON_MODIFY asumirá que el resultado devuelto es solo un texto sin formato y lo eliminará como cualquier otro texto si no lo ajusta con la función JSON_QUERY.

Debe ajustar la consulta FOR JSON, WITHOUT_ARRAY_WRAPPER con la función JSON_QUERY para convertir el resultado a JSON.