JSON_TRANSFORMの演算子SET

13.18 JSON_TRANSFORMの演算子SET

JSON_TRANSFORMの演算子SET (1)は、SQL/JSON変数の値を設定するか、(2)指定された場所でデータを置換または挿入します。

これは、操作のLHS (左辺)で指定されている内容を、RHS (右辺)で指定されている内容に設定します。LHSは、SQL/JSON変数またはデータを対象とするパス式のいずれかになります。

LHSでSQL/JSON変数^脚注1が指定されている場合、その変数には、RHSで指定されている内容が動的に代入されます。(まだ存在していない場合は変数が作成されます)。変数は、後続のSET操作によって(同じjson_tranform呼出し内で)異なる値に設定されるまで、その値を保持し続けます。

RHSがSQL式である場合は、その値が、LHSの変数に代入されます。RHSがパス式の場合は、その式が対象とするデータが変数に割り当てられます。

変数の設定は制御操作であり、後続の操作によるデータの変更方法に影響することがありますが、それ自体がデータを直接変更することはありません。
LHSにパス式が指定されているときのデフォルトの動作は、SQLのUPSERTと同様であり、対象となる既存のデータが新しい値に置き換えられます。また、パス式に一致するものがない場合は、対象となる場所に新しい値が挿入されます。(配列の末尾を超える配列要素の挿入については、演算子INSERTを参照してください。)

ノート:

json_transform代入の右辺(RHS)の式が、JSONデータであると認識されないSQL値(または一連の値)に評価される場合は、次のどちらかの方法でそれをJSONデータに変換できます。(1)その式の後にキーワードFORMAT JSONを続けるか、(2)JSONコンストラクタを使用してそれをラップします。^脚注2 例13-37ではこれを示しています。ここでは、JSONオブジェクトへの入力として、リテラルSQL文字列{...}を変換しています。

例13-33 JSON_TRANSFORM: SETの使用によるオブジェクトへのフィールドの追加

このコードでは、入力オブジェクト{"a":1} (コピー)にメンバー"b":2を追加しています。

SELECT json_transform('{"a":1}',
                      SET '$.b' = 2);

結果:

{"a":1, "b":2}

例13-34 JSON_TRANSFORM: SETの使用によるフィールドの値の変更

このコードでは、入力オブジェクト{"a":1} (コピー)にあるフィールドaの値を1から2に変更しています。

SELECT json_transform('{"a":1}',
                      SET '$.a' = 2);

結果:

{"a":2}

例13-35 JSON_TRANSFORM: SETの使用による配列要素の変更

このコードでは、入力オブジェクト{"a":[ 1,2,3 ]} (コピー)にある、配列aの2番目の要素(位置1)の値を、2から5に変更しています。

SELECT json_transform('{"a":[1,2,3]}',
                      SET '$.a[1]' = 5);

結果:

{"a":[ 1,5,3 ]}

例13-36 JSON_TRANSFORM: SETの使用による配列への値の追加(null埋込みあり)

このコードでは、入力配列[1,2,3] (コピー)の7番目の要素(位置6)を値5に設定しています。この入力配列には3つの要素しかないため、位置4、5および6にはnull要素が埋め込まれます。

SELECT json_transform('{"a":[1,2,3]}',
                      SET '$.a[6]' = 5);

結果:

{"a":[ 1,2,3,null,null,null,5 ]}

例13-37 JSON_TRANSFORM: SETの使用によるオブジェクトを含むフィールド値の作成または置換

この例では、Addressフィールドの値にJSONオブジェクト{"street":"8 Timbly Lane", "city":"Penobsky", "state":"Utah"}が設定されます。フィールドが存在しない場合はフィールドが作成されます。フィールドの既存の値が置き換えられます。

このフィールド値の入力は、JSONオブジェクトではなくリテラルSQL文字列です。しかしながら、この文字列はコンストラクタJSONでラップされているため、更新後のフィールド値はJSONオブジェクトです。

json_transform(data,
               SET '$.Address' = 
                   JSON('{"street":"8 Timbly Rd.",
                          "city":"Penobsky",
                          "state":"UT"}'))

入力文字列をJSONデータに変換するための別の方法は、SET操作でのキーワードFORMAT JSONの使用です。

json_transform(data,
               SET '$.Address' =
                   '{"street":"8 Timbly Rd.",
                     "city":"Penobsky",
                     "state":"UT"}'
                   FORMAT JSON)

コンストラクタJSONまたはFORMAT JSONを使用しなかった場合、Addressフィールドの値は、SQL入力文字列に対応するJSON文字列になります。入力の各二重引用符(")文字は、JSON文字列ではエスケープされます。

"{\"street\":\"8 Timbly Rd.\","city\":\"Penobsky\",\"state\":\"UT\"}"

json_transform-right-hand-side-rhs-path-expressions.html#GUID-1F575752-DB1F-42B3-9F23-E109E538F3C6__GUID-41EAF5AF-C23F-425D-B223-C00E655A7F31も参照してください。

例13-38 JSON_TRANSFORM: SETの使用による配列要素の変更

この例では、配列Phoneの先頭の要素にJSON文字列"909-555-1212"が設定されます。

json_transform(data,
               SET '$.ShippingInstructions.Phone[0]' = '909-555-1212')

操作の前の配列Phoneの値が次の値の場合:

[ {"type":"Office","number":"909-555-7307"},
  {"type":"Mobile","number":"415-555-1234"} ]

変更後のその値は次のようになります:

[ "909-555-1212",
  {"type":"Mobile","number":415-555-1234"} ]

演算子SETで使用できるハンドラは次のとおりです:

REPLACE ON EXISTING (デフォルト)、IGNORE ON EXISTING、ERROR ON EXISTING
CREATE ON MISSING (デフォルト)、IGNORE ON MISSING、ERROR ON MISSING
NULL ON NULL (デフォルト)、IGNORE ON NULL、ERROR ON NULL、REMOVE ON NULL
NULL ON EMPTY (デフォルト)、IGNORE ON EMPTY、ERROR ON EMPTY。
ERROR ON ERROR (デフォルト)、IGNORE ON ERROR。

関連トピック

親トピック: Oracle SQLファンクションJSON_TRANSFORM

脚注の凡例

脚注1: SQL/JSON変数は、ドル記号($)の後に変数名(SQL識別子)が続いたものです。SQL/JSON変数名の必要な構文については、「SQLファンクションおよび条件のPASSING句」を参照してください。
脚注2: コンストラクタJSONを使用するには、データベース初期化パラメータcompatibleが20以上である必要があります。