JSON_MODIFY(Transact-SQL)

適用対象:Sql Server 2016 (13.x) 以降のバージョンAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsMicrosoft Fabric の AzureSQL 分析エンドポイントMicrosoft Fabric のウェアハウスMicrosoft Fabric のSQL データベース

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

Transact-SQL 構文表記規則

Syntax

JSON_MODIFY ( expression , path , newValue )

Arguments

expression

式。 通常、変数または JSON テキストを含む列の名前。

JSON_MODIFY 式に有効な JSON が含まれていない場合、エラーが返されます。

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 2017 (14.x) と Azure SQL Database では、 パスの値として変数を指定できます。

  JSON_MODIFY は、パスの形式が無効な場合にエラーを返します。

newValue

path で指定されたプロパティの新しい値。

新しい値は、varchar、nvarchar、char、tinyint、smallint、int、bigint、bit、decimal/、numeric、または real/float である必要があります。 テキスト データ型はサポートされていません。

lax モードでは、新しい値が JSON_MODIFY の場合、NULL は指定されたキーを削除します。

JSON_MODIFY は、値の型が varchar または nvarchar の場合、新しい値のすべての特殊文字をエスケープします。 テキスト値は、 FOR JSON、 JSON_QUERY、または JSON_MODIFYによって生成される適切な形式の JSON である場合、エスケープされません。

戻り値

適切に書式設定された JSON テキストとして 、式 の更新された値を返します。

Remarks

JSON_MODIFY関数を使用すると、既存のプロパティの値を更新したり、新しいキーと値のペアを挿入したり、モードと指定された値の組み合わせに基づいてキーを削除したりできます。

次の表は、厳密でないモードと厳格モードでの JSON_MODIFY の動作を比較します。 省略可能なパス モード仕様 (lax または strict) の詳細については、「 JSON パス式」を参照してください。

lax モードでは、JSON_MODIFY は、新しいキーと値のペアを作成しようとしますが、その操作は、場合によっては失敗します。

JSON 関数は、JSON ドキュメントが varchar、 nvarchar、またはネイティブ json データ型のいずれに格納されているかにかかわらず、同じように機能します。

Examples

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 を指定します。 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 データ