Microsoft SQL Server
Изменить текст JSON

Изменить значение в тексте JSON по указанному пути

Функция JSON_MODIFY использует JSON-текст в качестве входного параметра и изменяет значение по указанному пути с использованием третьего аргумента:

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}

В результате у нас будет новый текст JSON с «Price»: 39.99, а другое значение не будет изменено. Если объект по указанному пути не существует, JSON_MODIFY вставляет пару ключ: значение.

Чтобы удалить пару «ключ: значение», поместите NULL в качестве нового значения:

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 по умолчанию удаляет ключ, если он не имеет значения, поэтому вы можете использовать его для удаления ключа.

Добавить скалярное значение в массив JSON

JSON_MODIFY имеет режим «append», который добавляет значение в массив.

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

Если массив по указанному пути не существует, JSON_MODIFY (append) создаст новый массив с одним элементом:

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

Вставить новый JSON-объект в текст JSON

Функция JSON_MODIFY позволяет вставлять объекты JSON в текст 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}}

Поскольку третьим параметром является текст, вам необходимо обернуть его с помощью функции JSON_QUERY для «трансляции» текста в JSON. Без этого «приведения» JSON_MODIFY будет обрабатывать третий параметр как обычный текст и escape-символы, прежде чем вставлять его как строковое значение. Без результатов JSON_QUERY будут:

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

JSON_MODIFY вставляет этот объект, если он не существует, или удаляет его, если значение третьего параметра равно NULL.

Вставить новый массив JSON, сгенерированный с помощью запроса FOR JSON

Вы можете сгенерировать объект JSON с помощью стандартного запроса SELECT с предложением FOR JSON и вставить его в текст JSON в качестве третьего параметра:

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 будет знать, что запрос select с предложением FOR JSON генерирует действительный массив JSON, и он просто вставляет его в текст JSON.

Вы можете использовать все опции FOR JSON в запросе SELECT, кроме WITHOUT_ARRAY_WRAPPER , который будет генерировать один объект вместо массива JSON. См. Другой пример в этом разделе, чтобы увидеть, как вставить один объект JSON.

Вставить один объект JSON, сгенерированный с помощью предложения FOR JSON

Вы можете сгенерировать объект JSON с помощью стандартного запроса SELECT с предложением FOR JSON и WITHOUT_ARRAY_WRAPPER и вставить его в текст JSON в качестве третьего параметра:

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

Для JSON с параметром WITHOUT_ARRAY_WRAPPER может генерироваться недопустимый текст JSON, если запрос SELECT возвращает более одного результата (в этом случае вы должны использовать TOP 1 или фильтр по первичному ключу). Поэтому JSON_MODIFY предположит, что возвращаемый результат является просто обычным текстом и избегает его, как и любой другой текст, если вы не обертываете его функцией JSON_QUERY.

Вы должны перенести FOR JSON, WITHOUT_ARRAY_WRAPPER запрос с помощью функции JSON_QUERY , чтобы придать результат JSON.