JSON_TRANSFORM
JSON_TRANSFORM_returning_clause::=
JSON_passing_clause::=
JSON_passing_clauseの詳細は、「JSON_EXISTS条件」を参照してください。
operation ::=
(add_set_op::=,append_op::=, case_op::=,copy_op::=,insert_op::=,intersect_op::=,keep_op::=,merge_op::=,minus_op::=,nested_path_op::=,prepend_op::=,remove_op::=,rename_op::=,remove_set_op::=,replace_op::=,set_op,sort_op,union_op,)
add_set_op::=
case_op::=
keep_op ::=
nested_path_op::=
remove_op ::=
remove_set_op::=
rename_op ::=
sort_op::=
rhs_expr ::=
目的
JSON_TRANSFORMはJSONドキュメントを変更します。実行する操作と、変更する場所を示すSQL/JSONパス式を指定します。操作は、指定した順序で入力データに適用されます。それぞれの操作は、その前のすべての操作が適用された結果に作用します。
JSON_TRANSFORMは完全に成功するか、まったく成功しないかのいずれかです。指定されたいずれかの操作でエラーが発生した場合、どの操作も有効になりません。JSON_TRANSFORMは、指定された操作に従って変更された元のデータを返します。
UPDATE文内でJSON_TRANSFORMを使用すると、JSON列のドキュメントを変更できます。
SELECTのリストでそれを使用すると、選択した文書を変更できます。変更したドキュメントは、後で返したり、処理できます。
JSON_TRANSFORMは、JSONデータをサポートするSQLデータ型(JSON、VARCHAR2、CLOBまたはBLOB)を入力として受け取り、出力として返すことができます。JSONデータ型は、データベース初期化パラメータcompatibleが20以上の場合にのみ使用できます。
デフォルトの戻り(出力)データ型は、入力データ型と同じです。
関連項目:
例の詳細は、『JSON開発者ガイド』のOracle SQLファンクションJSON_TRANSFORMを参照してください。
JSON_TRANSFORM操作
-
ADD_SETを使用して、要素を集合に追加する場合と同様に、欠落している値を配列に追加します。 -
APPENDを使用して、RHSで指定された値をLHSのパス式で対象となる配列に追加します。APPENDは、last+1の配列位置へのINSERTの効果があります。LHSのパス式が、配列でない値の既存のフィールドを対象としている場合はエラーが発生します。
RHSが配列を対象としている場合は、RHSの配列の要素を順番に追加することでLHSの配列が更新されます。
-
CASEを使用して、一連のJSON_TRANSFORM操作を実行する条件を設定します。これは制御操作であり、条件付きで別の操作を適用し、それによってデータを変更できます。
構文は、キーワード
CASEの後に1つ以上のWHEN句を続け、その後にオプションでELSE句を続け、その後にENDを続けます。WHEN句は、キーワードWHENの後にパス式を続け、その後にTHEN句を続けます。パス式にはフィルタ条件を含めて、それによってデータが存在するかどうかをチェックします。
THEN句またはELSE句は、それぞれキーワードTHENまたはELSEの後に、0個以上のJSON_TRANSFORM操作を含むカッコ(())を続けます。THEN句の操作は、そのWHEN句の条件が満たされた場合に実行されます。オプションのELSE句の操作は、WHEN句なしの条件が満たされた場合に実行されます。したがって、
JSON_TRANSFORMCASE操作の構文は、基本的にOracle SQLの検索CASE式と同じですが、これは、テストされる述語であり、各THEN/ELSE分岐の結果の効果であることを除きます。SQLの場合、テストされる述語はSQL比較です。
JSON_TRANSFORMの場合、述語は、なんらかのデータの存在をチェックするパス式です。(基本的に、このチェックにはJSON_EXISTSが使用されます)。SQLの場合、各
THEN/ELSE分岐は評価対象のSQL式を保持し、その値がCASE式の結果として返されます。json_transformの場合、各THEN/ELSE分岐は、順次実行されるJSON_TRANSFORM操作の(カッコで囲まれた)順序を保持します。WHEN句の条件付きパス式は、成功するまで順次テストされます(成功後のパス式はテストされません)。その後で、成功したWHENテストのTHEN操作が順次実行されます。 -
COPYを使用して、LHSのパス式で対象とする配列の要素を、RHSで指定された値に置き換えます。LHSのパス式が配列を対象としていない場合は、エラーが発生します。この操作は、RHSパス式で一致する複数の値のシーケンスを受け入れることができます。 -
INSERT等号(=)の後に指定されたパス式で対象となる場所に、指定したSQL式の値を挿入します。この場所は、オブジェクトのフィールドまたは配列の位置のいずれかである必要があります(それ以外の場合は、エラーが発生します)。デフォルトでは、対象となるオブジェクトのフィールドがすでに存在する場合、エラーが発生します。オブジェクトのフィールドの
INSERTは、CREATE ON MISSING句(SETのデフォルト)を指定したSETと同じ効果があります。ただし、ON EXISTINGのデフォルトの動作は、REPLACEではなくERRORです。)配列の現在の末尾より後の配列の位置を指定できます。その場合、配列は指定された位置への値の挿入に対応するように長さが調整され、間にある位置にはJSONのnull値が設定されます。
たとえば、入力のJSONデータが
{"a":["b"]}の場合、INSERT '$.a[3]'=42は変更されたデータとして{"a":["b", null, null 42]}を返します。配列位置1および2の要素は、nullです。 -
INTERSECTを使用して、LHSのパス式で対象とする配列要素のうち、RHSで指定された値と等しくない要素をすべて削除します。重複している要素を削除します。これは集合演算であることに注意してください。操作後のすべての配列要素の順序は定義されていません。 -
MERGEを使用して、RHSパス式で一致する指定されたフィールド(名前と値)をLHSパス式で対象とするオブジェクトに追加します。対象となるLHSのオブジェクトにすでに含まれている、RHSで指定されたフィールドを無視します。RHSで同じフィールドが複数回指定されている場合は、一致するフィールドのシーケンスの最後のフィールドのみを使用します。 -
MINUSを使用して、LHSのパス式で対象とする配列要素のうち、RHSで指定された値と等しいすべての要素を削除します。重複している要素を削除します。これは集合演算であることに注意してください。操作後のすべての配列要素の順序は定義されていません。 -
KEEPは、少なくとも1つの指定されたパス式で対象とされていない入力データのすべての部分を削除します。最上位のオブジェクトまたは配列は削除されません。これは空にされて、空のオブジェクト({})または配列([])になります。 -
NESTED PATHを使用して、操作のシーケンスを適用する有効範囲(データの特定の部分)を定義します。 -
PREPENDを使用して、RHSで指定された値をLHSパス式で対象とする配列に追加します。この操作は、RHSパス式で一致する複数の値のシーケンスを受け入れることができます。LHSのパス式が、配列でない値の既存のフィールドを対象としている場合はエラーが発生します。
単一の値を先頭に付加する場合、
PREPENDには、配列位置0に対するINSERTの効果があります。RHSが配列を対象としている場合は、RHSの配列の要素を先頭に順番に追加することでLHSの配列が更新されます。
-
REMOVE指定したパス式で対象となる入力データを削除します。REMOVE '$'を使用できないなど、すべてのデータを削除しようとすると、エラーが発生します。デフォルトでは、ターゲット・データが存在しない場合、エラーは発生しません(IGNORE ON MISSING)。 -
REMOVE_SETを使用して、集合から要素を削除する場合と同様に、値のすべての出現を配列から削除します。 -
RENAMEは、指定されたパス式で対象となるフィールドの名前を、等号(=)に続くSQL式の値に変更します。デフォルトでは、対象となるフィールドが存在しない場合、エラーは発生しません(IGNORE ON MISSING)。 -
REPLACEは、指定されたパス式で対象となるデータを、等号(=)の後に続く指定されたSQL式の値に置き換えます。デフォルトでは、ターゲット・データが存在しない場合、エラーは発生しません(IGNORE ON MISSING)。REPLACEには、IGNORE ON MISSING句を指定したSETと同じ効果があります。 -
SETLHSが指定する値を、等号(=)の後に続くもので指定する値に設定します。LHSは、SQL/JSON変数またはデータを対象とするパス式のいずれかになります。RHSがSQL式の場合は、その値がLHSの変数に割り当てられます。LHSでパス式が指定されている場合、デフォルトの動作では、既存のターゲット・データが新しい値に置換されるか、パス式がまったく一致しない場合は、ターゲットの場所に新しい値が挿入されます。(配列の末尾を超える配列要素の挿入については、演算子INSERTを参照してください。) -
LHSでSQL/JSONの変数が指定されると、その変数はRHSで指定されたものに動的に割り当てられます。(まだ存在していない場合は変数が作成されます)。変数は、後続の
SET操作(同じJSON_TRANSFORM呼出し内)によって異なる値に設定されるまで、その値を保持し続けます。RHSがパス式の場合は、その式が対象とするデータが変数に割り当てられます。
変数の設定は制御操作であり、後続の操作によるデータの変更方法に影響することがありますが、それ自体がデータを直接変更することはありません。
LHSにパス式が指定されているときのデフォルトの動作は、SQLの
UPSERTと同様であり、対象となる既存のデータが新しい値に置き換えられます。また、パス式に一致するものがない場合は、対象となる場所に新しい値が挿入されます。(配列の末尾を超える配列要素の挿入については、演算子INSERTを参照してください。) -
SORTは、指定されたパスで対象となる配列の要素をソートします。結果には、配列のすべての要素が含まれます(何も削除されません)。可能性のある唯一の変更は、順序が変更されることです。 -
UNIONを使用して、RHSで指定された値をLHSのパス式で対象とする配列に追加します。重複している要素を削除します。この操作は、RHSパス式で一致する複数の値のシーケンスを受け入れることができます。これは集合演算であることに注意してください。操作後のすべての配列要素の順序は定義されていません。
TYPE句
STRICTおよびLAX構文の詳細は、「厳密なJSON構文と緩慢なJSON構文について」および「SQLファンクションおよび条件のTYPE句」を参照してください
JSON_passing_clause
JSON_passing_clauseを使用して、JSON_EXISTS条件やSQL/JSON問合せファンクションと同様のSQL/JSON変数へのバインド変数のSQLバインディングを指定できます。
JSON_TRANSFORM_returning_clause
操作を指定したら、JSON_TRANSFORM_returning_clauseを使用して戻りデータ型を指定できます。
例
例1: タイムスタンプを使用してJSON列を更新する
UPDATE t SET jcol = JSON_TRANSFORM(jcol, SET '$.lastUpdated' = SYSTIMESTAMP)
例2: クライアントにJSONを送信する前に、社会保障番号を削除する
SELECT JSON_TRANSFORM (jcol, REMOVE '$.ssn') FROM t WHERE …
JSON_TRANSFORM_returning_clause
入力データがJSONの場合、出力データ型もJSONです。他のすべての入力型については、デフォルトの出力データ型はVARCHAR2(4000)です。