JSON_TRANSFORM

構文

図json_transform.epsの説明

JSON_TRANSFORM_returning_clause::=

図json_transform_returning_clause.epsの説明

JSON_passing_clause::=

JSON_passing_clauseの詳細は、「JSON_EXISTS条件」を参照してください。

operation ::=

図operation.epsの説明

remove_op ::=

図remove_op.epsの説明

insert_op ::=

図insert_op.epsの説明

replace_op ::=

図replace_op.epsの説明

rhs_expr ::=

図rhs_expr.epsの説明

append_op ::=

図append_op.epsの説明

set_op ::=

図set_op.epsの説明

rename_op ::=

図rename_op.epsの説明

keep_op ::=

図keep_op.epsの説明

sort_op::=

図sort_op.epsの説明

nested_path_op::=

図nested_path_op.epsの説明

目的

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操作

• REMOVE 指定したパス式で対象となる入力データを削除します。すべてのデータを削除しようとすると、エラーが発生します。つまり、REMOVE '$'は使用できません。デフォルトでは、ターゲット・データが存在しない場合、エラーは発生しません(IGNORE ON MISSING)。

• 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です。

• REPLACEは、指定されたパス式で対象となるデータを、等号(=)の後に続く指定されたSQL式の値に置き換えます。デフォルトでは、ターゲット・データが存在しない場合、エラーは発生しません(IGNORE ON MISSING)。

  REPLACEには、IGNORE ON MISSING句を指定したSETと同じ効果があります。

• APPEND RHSで指定された値をLHSのパス式で対象となる配列に付加します。

  APPENDは、last+1の配列位置へのINSERTの効果があります。

  LHSのパス式が、配列でない値の既存のフィールドを対象としている場合はエラーが発生します。

  RHSが配列を対象としている場合は、RHSの配列の要素を順番に追加することでLHSの配列が更新されます。

• SET LHSが指定する値を、等号(=)の後に続くもので指定する値に設定します。LHSは、SQL/JSON変数またはデータを対象とするパス式のいずれかになります。RHSがSQL式の場合は、その値がLHSの変数に割り当てられます。LHSでパス式が指定されている場合、デフォルトの動作では、既存のターゲット・データが新しい値に置換されるか、パス式がまったく一致しない場合は、ターゲットの場所に新しい値が挿入されます。(配列の末尾を超える配列要素の挿入については、演算子INSERTを参照してください。)

  LHSでSQL/JSONの変数が指定されると、その変数はRHSで指定されたものに動的に割り当てられます。(まだ存在していない場合は変数が作成されます)。変数は、後続のSET操作(同じJSON_TRANSFORM呼出し内)によって異なる値に設定されるまで、その値を保持し続けます。

  RHSがパス式の場合は、その式が対象とするデータが変数に割り当てられます。

  変数の設定は制御操作であり、後続の操作によるデータの変更方法に影響することがありますが、それ自体がデータを直接変更することはありません。

  LHSにパス式が指定されているときのデフォルトの動作は、SQLのUPSERTと同様であり、対象となる既存のデータが新しい値に置き換えられます。また、パス式に一致するものがない場合は、対象となる場所に新しい値が挿入されます。(配列の末尾を超える配列要素の挿入については、演算子INSERTを参照してください。)

• RENAME 指定されたパス式で対象となるフィールドの名前を、等号(=)の後に続くSQL式の値に変更します。デフォルトでは、対象となるフィールドが存在しない場合、エラーは発生しません(IGNORE ON MISSING)。

• KEEP 少なくとも1つの指定されたパス式で対象とされていない入力データのすべての部分を削除します。最上位のオブジェクトまたは配列は削除されません。これは空にされ、空のオブジェクト({})または配列([])になります。

• KEEPは、少なくとも1つの指定されたパス式で対象とされていない入力データのすべての部分を削除します。最上位のオブジェクトまたは配列は削除されません。これは空にされ、空のオブジェクト({})または配列([])になります。

• SORTは、指定されたパスで対象となる配列の要素をソートします。結果には、配列のすべての要素が含まれます(何も削除されません)。可能性のある唯一の変更は、順序が変更されることです。

• NESTED PATHを使用して、操作のシーケンスを適用する有効範囲(データの特定の部分)を定義します。

JSON_TRANSFORM_returning_clause

操作を指定したら、JSON_TRANSFORM_returning_clauseを使用して戻りデータ型を指定できます。

JSON_passing_clause

JSON_passing_clauseを使用して、JSON_EXISTS条件やSQL/JSON問合せファンクションと同様のSQL/JSON変数へのバインド変数のSQLバインディングを指定できます。

例

例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)です。