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クエリをラップする必要があります。