Microsoft SQL Server
JSONテキストを変更する

指定したパスのJSONテキストの値を変更する

JSON_MODIFY関数はJSONテキストを入力パラメーターとして使用し、指定されたパスの値を第3引数を使用して変更します。

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はkey：valueのペアを挿入します。

key：valueのペアを削除するには、新しい値として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は、キーを削除するために使用できるように、値がない場合はデフォルトでdeleteキーを削除します。

JSON配列にスカラー値を追加する

JSON_MODIFYには、配列に値を追加する '追加'モードがあります。

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

3番目のパラメータはテキストなのでJSON_QUERY関数でラップしてJSONにテキストをキャストする必要があります。この "キャスト"がなければ、JSON_MODIFYは3番目のパラメータをプレーンテキストとエスケープ文字として扱い、文字列値として挿入します。 JSON_QUERYを指定しないと結果は次のようになります：

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

JSON_MODIFYはこのオブジェクトが存在しない場合は挿入し、3番目のパラメータの値がNULLの場合は削除します。

FOR JSONクエリで生成された新しいJSON配列を挿入する

FOR JSON句を使用した標準のSELECTクエリを使用してJSONオブジェクトを生成し、JSONテキストに第3パラメータとして挿入できます。

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は、FOR JSON句を使用した選択クエリが有効なJSON配列を生成し、それをJSONテキストに挿入することを認識します。

SELECTクエリでは、 WITHOUT_ARRAY_WRAPPERを除くすべてのFOR JSONオプションを使用できます。これは、JSON配列の代わりに単一のオブジェクトを生成します。単一のJSONオブジェクトの挿入方法については、このトピックの他の例を参照してください。

FOR JSON句で生成された単一のJSONオブジェクトを挿入する

FOR JSON句とWITHOUT_ARRAY_WRAPPERオプションを使用した標準SELECTクエリを使用してJSONオブジェクトを生成し、JSONテキストに第3パラメータとして挿入できます。

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

WITHOUT_ARRAY_WRAPPERオプションを指定したJSONでは、SELECTクエリで複数の結果が返された場合（この場合はTOP 1を使用するか、プライマリキーでフィルタリングする必要があります）、無効なJSONテキストが生成されることがあります。したがって、JSON_MODIFYは返された結果が単なるテキストであると想定し、JSON_QUERY関数でラップしないと他のテキストと同様にエスケープします。

結果をJSONにキャストするには、 JSON_QUERY関数を使用してFOR JSON、WITHOUT_ARRAY_WRAPPERクエリをラップする必要があります。