SQL/JSONファンクションJSON

22 SQL/JSONファンクションJSON_TABLE

SQL/JSONファンクションjson_tableは様々なSQLデータ型の列に特定のJSONデータを投影します。これを使用してJSON文書の一部を新規仮想表の行および列にマップしますが、これは、インラインのビューとみなすこともできます。

この仮想表は、既存のデータベース表に挿入でき、またSQLを使用して(join式など)問合せできます。

json_tableの一般的な使用目的は、JSONデータのビューを作成することです。このようなビューは、任意の表またはビューを使用する場合と同じように使用できます。このため、アプリケーション、ツールおよびプログラマは、JSONまたはJSONパス式の構文を考慮せずにJSONデータを操作できます。

JSONデータに対してビューを定義することにより、実質的にある種のスキーマがそのデータにマップされます。このマッピングは、事後に行われます。つまり、基礎となるJSONデータは、スキーマまたは特定の使用パターンを考慮せずに定義および作成できます。データが最初で、スキーマが後です。

このようなスキーマ(マッピング)により、データベースに格納できるJSON文書の種類に(整形式のJSONデータであること以外の)制約が課されることはありません。ビューでは、ビューを定義するマッピング(スキーマ)に準拠するデータのみが公開されます。ビューを再定義するだけでスキーマを変更でき、基礎となるJSONデータを再編成する必要はありません。

json_tableはSQLのFROM句で使用します。これは行ソースです。これにより、行パス式(行パターン)によって選択されたJSON値ごとに仮想表のデータの行が生成されます。生成された各行の列は、COLUMNS句の列パス式によって定義されます。

通常、json_table呼出しは、FROMリスト内のソース表と暗黙的に横方向に結合されます。その行にはそれぞれ、ファンクションへの入力として使用されるJSON文書が含まれています。json_tableでは、入力文書に対して行パス式を評価することにより決定される、0行以上の新規行を生成します。

json_tableの最初の引数はSQL式です。これは、適切にキャストされた表またはビューの列の値、PL/SQL変数、バインド変数のいずれかになります。式の評価の結果は、行パス式を評価するためのコンテキスト項目として使用されます。

json_tableの2番目の引数はSQL/JSON行パス式であり、行を処理するためのオプションのエラー句と(必須の) COLUMNS句が付加されます。この句により、作成する仮想表の列が定義されます。RETURNING句はありません。

json_tableのエラー処理には2つのレベルがありますが、これらは、パス式の2つのレベルである行と列に対応しています。存在する場合、列のエラー・ハンドラにより、行レベルのエラー処理がオーバーライドされます。両レベルのデフォルトのエラー・ハンドラはNULL ON ERRORです。

行パス式配列ステップでは、索引と範囲の順序、配列索引の複数出現、および範囲の重複による指定された位置の重複は、すべて通常の効果を持ちます。指定した位置はデータと順番に照合され、位置の一致ごとに1つの行が生成されます。

コンテキスト項目引数と行パス式を渡すかわりに、単純なドット表記法構文を使用できます。(引き続きエラー句を使用でき、COLUMNS句が必要です。)ドット表記法では、対象のJSONデータへの単純なパスとともに表またはビューの列を指定します。たとえば、この2つの問合せは等価です。

json_table(t.j, '$.ShippingInstructions.Phone[*]' ...)

json_table(t.j.ShippingInstructions.Phone[*] ...)

行パス式が'$'のみで、文書全体を対象とする場合、パスの部分を省略できます。次の問合せは等価です。

json_table(t.j, '$' ...)

json_table(t.j ...)

例22-1に、単純なドット表記法の使用と、より完全で明示的な表記法の使用の違いを示します。

SQL/JSONパス式を使用するかわりに、COLUMNS句のPATH句でドット表記を使用することもできます。たとえば、PATH '$.ShippingInstructions.name'のかわりにPATH 'ShippingInstructions.name'を使用できます。

例22-1 等価のJSON_TABLE問合せ: 単純な構文と完全な構文

この例では、2つの等価の問合せにjson_tableを使用します。最初の問合せでは、行および列データを対象とする式に単純なドット表記法構文を使用します。2番目の問合せでは、完全な構文を使用します。

SQL識別子が引用符で囲まれている列Special Instructionsを除いて、実際のSQL列名は大文字です。(識別子Special Instructionsには空白文字が含まれています。)

最初の問合せでは、列名の記述は、大/小文字の区別を含め、対象となるオブジェクト・フィールドの名前と完全に同じです。引用符で囲まれるかどうかに関係なく、これらは、デフォルト・パス(明示的なPATH句がない場合に使用されるパス)を設定するために大/小文字を区別して解釈されます。

2番目の問合せには次のものが含まれています。

JSON列式およびSQL/JSON行パス式の別々の引数
VARCHAR2(4000)の明示的な列データ型
投影されるオブジェクト・フィールドを対象とするための、明示的なPATH句およびSQL/JSON列パス式

SELECT jt.*
  FROM j_purchaseorder po,
       json_table(po.data
         COLUMNS ("Special Instructions",
                  NESTED LineItems[*]
                    COLUMNS (ItemNumber NUMBER,
                             Description PATH Part.Description))
       ) AS "JT";

SELECT jt.*
  FROM j_purchaseorder po,
       json_table(po.data, 
         '$'
         COLUMNS (
           "Special Instructions" VARCHAR2(4000)
                                  PATH '$."Special Instructions"',
           NESTED PATH '$.LineItems[*]'
             COLUMNS (
               ItemNumber  NUMBER        PATH '$.ItemNumber',
               Description VARCHAR(4000) PATH '$.Part.Description'))
       ) AS "JT";

_________________________________________________________