JSON_MODIFY (Transact-SQL)

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics

JSON 文字列内のプロパティの値を更新し、更新された JSON 文字列を返します。

Transact-SQL 構文表記規則

構文

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;

関連するコンテンツ

• JSON パス式 (SQL Server)
• JSON データ (SQL Server)