JSON_MODIFY (Transact-SQL)
適用対象: SQL Server 2016 (13.x) 以降
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
JSON 文字列内のプロパティの値を更新し、更新された JSON 文字列を返します。
構文
JSON_MODIFY ( expression , path , newValue )
引数
式 (expression)
式。 通常、変数または JSON テキストを含む列の名前。
式に有効な JSON が含まれていない場合、JSON_MODIFY
はエラーを返します。
path
更新するプロパティを指定する JSON path 式。
path の構文は次のとおりです。
[append] [ lax | strict ] $.<json path>
append
<json path>
によって参照されるアレイに新しい値を追加する必要があることを指定する省略可能な修飾子。lax
<json path>
によって参照されるプロパティが存在する必要がないことを指定します。 プロパティが存在しない場合、JSON_MODIFY
は、指定したパスに新しい値を挿入しようとします。 パスにプロパティを挿入できない場合、挿入は失敗する可能性があります。 lax または strict の指定がない場合の既定のモードは lax です。strict
<json path>
によって参照されるプロパティが JSON 式内に存在する必要があることを指定します。 プロパティが存在しない場合、JSON_MODIFY
はエラーを返します。<json path>
更新するプロパティのパスを指定します。 詳細については、「JSON パス式 (SQL Server)」を参照してください。
SQL Server 2017 (14.x) と Azure SQL データベース では、path の値として変数を指定できます。
path の書式が有効でない場合、
JSON_MODIFY
はエラーを返します。
newValue
path によって指定されるプロパティの新しい値。
新しい値は、 varchar、 nvarchar、または text である必要があります。
lax モードでは、新しい値が JSON_MODIFY
の場合、NULL
は指定されたキーを削除します。
JSON_MODIFY
は、値の型が varchar または nvarchar の場合、新しい値のすべての特殊文字をエスケープします。 テキスト値は、 FOR JSON
、 JSON_QUERY
、または JSON_MODIFY
によって生成される適切な形式の JSON である場合、エスケープされません。
戻り値
expression の更新された値を、適切に書式設定された JSON テキストとして返します。
解説
JSON_MODIFY
関数を使用すると、既存のプロパティの値を更新したり、新しいキーと値のペアを挿入したり、モードと指定された値の組み合わせに基づいてキーを削除したりできます。
次の表は、厳密でないモードと厳格モードでの JSON_MODIFY
の動作を比較します。 省略可能なパス モード (厳密でない、または厳格) の指定について詳しくは、「JSON パス式 (SQL Server)」を参照してください。
新しい値 | パスが存在するか | 厳密でないモード | 厳格モード |
---|---|---|---|
NOT NULL |
はい | 既存の値を更新します。 | 既存の値を更新します。 |
NOT NULL |
いいえ | 指定したパスに新しいキーと値のペアを作成してみてください。 これは失敗する可能性があります。 たとえば、パス $.user.setting.theme を指定した場合、$.user または$.user.settings オブジェクトが存在しない場合、または設定が配列またはスカラー値の場合、JSON_MODIFY はキーtheme を挿入しません。 |
Error - INVALID_PROPERTY |
NULL |
はい | 既存のプロパティを削除します。 | 既存の値を null に設定します。 |
NULL |
いいえ | NO ACTION 最初の引数が結果として返されます。 | Error - INVALID_PROPERTY |
lax モードでは、JSON_MODIFY
は、新しいキーと値のペアを作成しようとしますが、その操作は、場合によっては失敗します。
JSON 関数は、JSON ドキュメントが varchar、nvarchar またはネイティブ json データ型のどちらに格納されているかに関係なく、同じように機能します。
例
A. 基本操作
次の例では、JSON テキストを使用して実行できる基本的な操作を示します。
DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;
-- Update name
SET @info = JSON_MODIFY(@info, '$.name', 'Mike');
PRINT @info;
-- Insert surname
SET @info = JSON_MODIFY(@info, '$.surname', 'Smith');
PRINT @info;
-- Set name NULL
SET @info = JSON_MODIFY(@info, 'strict $.name', NULL);
PRINT @info;
-- Delete name
SET @info = JSON_MODIFY(@info, '$.name', NULL);
PRINT @info;
-- Add skill
SET @info = JSON_MODIFY(@info, 'append $.skills', 'Azure');
PRINT @info;
結果セットは次のようになります。
{
"name": "John",
"skills": ["C#", "SQL"]
} {
"name": "Mike",
"skills": ["C#", "SQL"]
} {
"name": "Mike",
"skills": ["C#", "SQL"],
"surname": "Smith"
} {
"skills": ["C#", "SQL"],
"surname": "Smith"
} {
"skills": ["C#", "SQL", "Azure"],
"surname": "Smith"
}
B. 複数の更新プログラム
JSON_MODIFY
では、更新できるプロパティは 1 つだけです。 複数の更新を行う必要がある場合は、複数の JSON_MODIFY
呼び出しを使用できます。
DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;
-- Multiple updates
SET @info = JSON_MODIFY(JSON_MODIFY(JSON_MODIFY(@info, '$.name', 'Mike'), '$.surname', 'Smith'), 'append $.skills', 'Azure');
PRINT @info;
結果セットは次のようになります。
{
"name": "John",
"skills": ["C#", "SQL"]
} {
"name": "Mike",
"skills": ["C#", "SQL", "Azure"],
"surname": "Smith"
}
C: キーの名前を変更する
次の例は、 JSON_MODIFY
関数を使用して JSON テキスト内のプロパティの名前を変更する方法を示しています。 最初に、既存のプロパティの値を取得し、それを新しいキーと値のペアとして挿入できます。 その後、古いプロパティの値を NULL
に設定することで、古いキーを削除できます。
DECLARE @product NVARCHAR(100) = '{"price":49.99}';
PRINT @product;
-- Rename property
SET @product = JSON_MODIFY(JSON_MODIFY(@product, '$.Price', CAST(JSON_VALUE(@product, '$.price') AS NUMERIC(4, 2))), '$.price', NULL);
PRINT @product;
結果セットは次のようになります。
{
"price": 49.99
} {
"Price": 49.99
}
新しい値が数値型にキャストされない場合、JSON_MODIFY
は、それをテキストとして処理し、二重引用符で囲みます。
D. 値をインクリメントする
次の例では、JSON_MODIFY
関数を使用して、JSON テキスト内のプロパティの値を変更する方法を示します。 最初に、既存のプロパティの値を取得し、それを新しいキーと値のペアとして挿入できます。 その後、古いプロパティの値を NULL
に設定することで、古いキーを削除できます。
DECLARE @stats NVARCHAR(100) = '{"click_count": 173}';
PRINT @stats;
-- Increment value
SET @stats = JSON_MODIFY(@stats, '$.click_count', CAST(JSON_VALUE(@stats, '$.click_count') AS INT) + 1);
PRINT @stats;
結果セットは次のようになります。
{
"click_count": 173
} {
"click_count": 174
}
E. JSON オブジェクトを変更する
JSON_MODIFY
では、その中に適切に書式設定された JSON テキストが含まれている場合でも、newValue 引数をプレーン テキストとして処理します。 その結果、次の例に示すように、この関数の JSON の出力は二重引用符で囲まれ、すべての特殊文字がエスケープされます。
DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;
-- Update skills array
SET @info = JSON_MODIFY(@info, '$.skills', '["C#","T-SQL","Azure"]');
PRINT @info;
結果セットは次のようになります。
{
"name": "John",
"skills": ["C#", "SQL"]
} {
"name": "John",
"skills": "[\"C#\",\"T-SQL\",\"Azure\"]"
}
自動エスケープを回避するには、JSON_QUERY
関数を使用して newValue を指定します。 JSON_MODIFY
は、JSON_QUERY
によって返される値が適切に書式設定された JSON であることを認識するため、値をエスケープすることはありません。
DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;
-- Update skills array
SET @info = JSON_MODIFY(@info, '$.skills', JSON_QUERY('["C#","T-SQL","Azure"]'));
PRINT @info;
結果セットは次のようになります。
{
"name": "John",
"skills": ["C#", "SQL"]
} {
"name": "John",
"skills": ["C#", "T-SQL", "Azure"]
}
F. JSON 列を更新する
次の例では、JSON を含むテーブル列のプロパティの値を更新します。
UPDATE Employee
SET jsonCol = JSON_MODIFY(jsonCol, '$.info.address.town', 'London')
WHERE EmployeeID = 17;