SQL/JSONファンクションJSON

21 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 ...)

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

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

例21-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.po_document
         COLUMNS ("Special Instructions",
                  NESTED LineItems[*]
                    COLUMNS (ItemNumber NUMBER,
                             Description PATH Part.Description))
       ) AS "JT";

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

21.1 JSON_TABLEの代替のSQL NESTED句

SELECT句で、SQL/JSONファンクションjson_tableのかわりにNESTED句を使用することがよくあります。これにより、問合せ式が単純化されることがあります。これには、JSON列がNULLの場合、NULL以外のリレーショナル列を含む行を含めるという利点もあります。

NESTED句は、ANSI左外部結合でjson_tableを使用するためのショートカットです。つまり、この2つの問合せは等価です。

SELECT ... 
  FROM mytable NESTED jcol COLUMNS (...);

SELECT ...
  FROM mytable t1 LEFT OUTER JOIN
       json_table(t1.jcol COLUMNS (...)
       ON 1=1;

json_tableで左外部結合を使用したり、NESTED句を使用すると、選択結果に対応するJSON列データがない(つまり、JSON列がNULLである)リレーショナル列を含む行を含めることができます。この2つの間の唯一のセマンティックの相違点は、NESTED句を使用する場合、JSON列自体が結果に含まれないことです。

NESTED句は、ネストした列の可能性を含め、json_tableと同じCOLUMNS句を指定します。NESTEDを使用するメリットは、次のとおりです。

単純なドット表記法を使用している場合でも、表別名を指定する必要はありません。
JSON列がJSON型でない場合でも、is jsonチェック制約を指定する必要はありません。(列がJSON型でないかぎり、単純なドット表記を使用したjson_tableには制約が必要です。)
LEFT OUTER JOINを指定する必要はありません。

NESTED句の構文はより単純で、COLUMNS句のすべての柔軟性が実現され、暗黙的な左外部結合が実行されます。例21-2に、これを示します。

例21-3に、単純なドット表記法によるNESTED句の使用を示します。

例21-2 等価: SQL NESTEDとLEFT OUTER JOINを指定したJSON_TABLE

次の2つの問合せは同じです。1つは、明示的なLEFT OUTER JOINを指定したでSQL/JSONファンクションjson_tableを使用します。もう1つは、SQLのNESTED句を使用します。

SELECT id, requestor, type, "number"
  FROM j_purchaseorder LEFT OUTER JOIN
       json_table(po_document
         COLUMNS (Requestor,
                  NESTED ShippingInstructions.Phone[*]
                    COLUMNS (type, "number")))
       ON 1=1);

SELECT id, requestor, type, "number"
  FROM j_purchaseorder NESTED
       po_document
         COLUMNS (Requestor,
                  NESTED ShippingInstructions.Phone[*]
                    COLUMNS (type, "number");

出力はどちらの場合でも同じです。

7C3A54B183056369E0536DE05A0A15E4 Alexis Bull Office 909-555-7307
7C3A54B183056369E0536DE05A0A15E4 Alexis Bull Mobile 415-555-1234
7C3A54B183066369E0536DE05A0A15E4 Sarah Bell

表j_purchaseorderに、列idおよびrequestorの値がNULL以外で、列po_documentの値がNULLである行がある場合、その行は両方のケースで表示されます。一方、LEFT OUTER JOINが存在しない場合、json_tableのケースには表示されません。

例21-3 SQL NESTEDを使用したネスト配列の拡張

この例では、表j_purchaseorderから列idおよびdate_loadedを、JSON列po_documentのフィールドShippingInstructionsの値にネストされているフィールドPhoneの配列要素とともに選択します。Phone配列値は列typeおよびnumberとして展開されます。

(列指定"number"には、numberがSQLの予約語であるため二重引用符が必要です。)

SELECT *
  FROM j_purchaseorder NESTED
       po_document.ShippingInstructions.Phone[*]
         COLUMNS (type, "number")

親トピック: SQL/JSONファンクションJSON_TABLE

21.2 SQL/JSONファンクションJSON_TABLEのCOLUMNS句

SQL/JSONファンクションjson_tableの必須のCOLUMNS句は、このファンクションによって作成される仮想表の列を定義します。

これは、キーワードCOLUMNSの後ろに次のエントリをカッコで囲んだ形式で構成されます。オプションのFOR ORDINALITYエントリ以外、COLUMNS句の各エントリは、標準列指定またはネストした列指定です。

COLUMNS句内の多くても1つのエントリは、生成される行数の列(SQLデータ型NUMBER)を指定するキーワードFOR ORDINALITYを列名の後ろに付けた形式にすることができます。これらの数は1から始まります。たとえば:
```
COLUMNS (linenum FOR ORDINALITY, ProductID)
```
行パス式の配列ステップは、パス式と一致する任意の行数になります。特に、配列ステップの索引と範囲の順序、配列索引の複数出現、および範囲の重複による指定された位置の重複によって、位置の一致ごとに1つの行が生成されます。順序行番号にはこれが反映されます。
通常の列の指定では、列名の後にオプションの列のデータ型が続きます。これには、json_valueのRETURNING句に使用できる任意のSQLデータ型とそれに続くオプションの値句およびオプションのPATH句を指定できます。デフォルトのデータ型はVARCHAR2(4000)です。

列のデータ型は、JSON、VARCHAR2、NUMBER、DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONEまたはSDO_GEOMETRYのいずれかにすることができます。

データ型SDO_GEOMETRYは、Oracle Spatial and Graphデータ向けに使用されます。特に、これは、json_tableをGeoJSONデータで使用できることを意味します。このデータは、JSONで地理データをエンコーディングするための形式です。

OracleではSQL/JSON標準を拡張し、列の戻りデータ型がVARCHAR2(N)の場合は、データ型の直後にオプションのキーワードTRUNCATEを指定できるようにしました。TRUNCATEが存在し、かつ戻り値がNよりも長い場合は、値が切り捨てられ、先頭N文字のみが戻されます。TRUNCATEがない場合、このケースはエラーとして扱われ、エラー句またはデフォルトのエラー処理動作によって通常どおり処理されます。
ネストした列の指定は、キーワードNESTEDの後ろにオプションのPATHキーワード、SQL/JSON行パス式、COLUMNS句を続けて構成します。このCOLUMNS句は、ネストしたデータを表す列を指定します。ここで使用される行パス式により、指定したネストした列のコンテキストが洗練されます。ネストした各列のパス式は行パス式を基準にしたものになります。同じ行の列に異なるレベルで配列に存在する投影値に列の句をネストできます。

COLUMNS句は(ネストしていてもネストしていなくても)どのレベルでも同じ特性を持ちます。つまり、COLUMNS句は再帰的に定義されます。ネストのレベルごとに(つまり、キーワードNESTEDが使用されるたびに)、ネストしたCOLUMNS句は、ネスト元のCOLUMNS句(その親)の子と呼ばれます。同じ親句を持つ複数のCOLUMNS句は兄弟です。

親子のCOLUMNS句によって定義される仮想表は、外部結合を使用して結合されますが、この場合、親が外部表になります。兄弟のCOLUMNS句によって定義される仮想列は、和結合を使用して結合されます。

例21-1および例21-9に、ネストされた列句の使用を示します。

標準列指定に必要なものは、列名のみです。スカラー・データ型、値の処理またはターゲット・パスを指定することによる列の投影の詳細な定義はオプションです。

オプションの値句は、列に投影されたデータを処理する方法、つまり、データをjson_value、json_existsまたはjson_queryと同じように処理するかどうかを指定します。この値処理には、戻りデータ型、戻り形式(prettyまたはASCII)、ラッパー、およびエラーの処理が含まれます。

キーワードEXISTSを使用すると、投影されるデータは、json_existsと同様に処理されます(列のデータ型に関係なく)。

そうでない場合は、次のようになります。
- JSONデータ型の列の場合、投影されるデータはjson_queryと同様に処理されます。
- JSON型以外の列(json_valueのRETURNING句に使用できる任意の型)の場合、デフォルトでは、投影されるデータはjson_valueによる処理と同様に処理されます。ただし、キーワードFORMAT JSONを使用すると、json_queryによって処理されたかのように処理されます。投影されたデータがJSONオブジェクトまたは配列の場合は通常、FORMAT JSONのみを使用します。(JSON型の列にFORMAT JSONを使用するとエラーが発生します。)
たとえば、ここで、列FirstNameの値はjson_valueセマンティクスを使用して直接投影されており、列Addressの値はjson_queryセマンティクスを使用してJSON文字列として投影されます。
```
COLUMNS (FirstName, Address FORMAT JSON)
```
json_queryのセマンティクスは、投影されるJSONデータが整形式であることを意味します。列がJSON型以外の場合、これには、文字列値の非ASCII文字が必要に応じてエスケープされていることも含まれます。たとえば、タブ文字(CHARACTER TABULATION、U+0009)は\tとしてエスケープされます。(JSON型のデータの場合、json_queryの使用時ではなく、JSONデータの作成時にそのようなエスケープが行われます。)

列にjson_queryのセマンティクスがある場合:
- データベース初期化パラメータcompatibleが少なくとも20の場合は、キーワードDISALLOW SCALARSを使用して、スカラーJSON値を除外することによって、json_queryの動作に影響を与えることができます。
- 明示的なラッパー句を追加することにより、デフォルトのラッパー動作をオーバーライドできます。
特定のハンドラ(json_exists、json_valueまたはjson_query)のデフォルトのエラー処理を、それに適した明示的なエラー句を追加することでオーバーライドできます。
オプションのPATH句は、列の内容として使用される行の部分を指定します。キーワードPATHに続く列パス式は、仮想行によって提供されるコンテキスト項目と照合されます。この列パス式は、行パス式によって指定されるパスに対して相対的であるため、相対パスの表記にする必要があります。

PATH句が存在しない場合、動作は'$.<column-name>'のパスで存在している場合と同様です。ここで、<column-name>は列名です。つまり、対象となるオブジェクト・フィールドの名前が列名として暗黙的に取得されます。

対象となるフィールドのみを指定するために、<column-name>に使用されるSQL識別子は、引用符で囲まれていない場合でも大/小文字を区別して解釈されます。列のSQL名自体は通常のルールに従います。二重引用符(")で囲まれている場合は、使用されている文字の大/小文字が区別されます。そうでない場合は、区別されません(大文字として処理されます)。

たとえば、これらの2つのCOLUMNS句は等価です。SQLの場合、大/小文字はComments列に対してのみ意味があります(引用符で囲まれているため)。その他の2つの列には、PATH句を使用するかどうかに関係なく、大/小文字を区別しない名前が付いています(つまり、名前の大/小文字は区別されないということです)。最初のCOLUMNS句では、暗黙的に対象とする大/小文字が混在したフィールド名と一致する最初の2つの列が書き込まれます。
```
COLUMNS(ProductId, Quantity NUMBER, "Comments")

COLUMNS(productid   VARCHAR2(4000) PATH '$.ProductId',
        quantity    NUMBER         PATH '$.Quantity',
        "Comments"  VARCHAR2(4000) PATH '$.Comments')
```
例21-1に、これを示す等価の問合せを示します。

SQL/JSONパス式のかわりに、PATH句でドット表記法を使用することもできます。例21-2および例21-9に、これを示します。

列パス式配列ステップでは、索引と範囲の順序、配列索引の複数出現、および範囲の重複による指定された位置の重複は、列に使用される特定のセマンティクス(json_exists、json_queryまたはjson_value)の場合と同様の効果があります。

json_exists — 考慮されるのは、指定された位置のセットであり、指定された順序、回数を含む指定方法ではありません。チェックされるのは、少なくとも1つの指定された位置に一致が存在することです。
json_query — 指定された各位置がデータと順番に照合されます。
json_value — 指定された位置が1つのみの場合、データと照合されます。それ以外の場合は一致しません。デフォルト(NULL ON ERROR)では、SQLのNULL値が返されます。

json_valueセマンティクスを使用する列句では、PATH句の後にオプションのキーワードの組合せTYPE (STRICT)を指定することもできます。これは、json_valueのRETURNING句に関連してそれが使用される場合と同じ意味があり同じ動作になります。たとえば、この2つの問合せは等価です。値が数値であるPONumberフィールドのみが考慮(投影)されます。

SELECT jt.ponumb
  FROM j_purchaseorder,
       json_table(po_document, '$'
         COLUMNS (ponumb NUMBER PATH '$.PONumber.numberOnly()')) jt

SELECT jt.ponumb
  FROM j_purchaseorder,
       json_table(po_document, '$'
         COLUMNS (ponumb NUMBER PATH '$.PONumber' TYPE (STRICT))) jt

関連項目

21.3 JSON_TABLEによるSQL/JSON問合せファンクションおよび条件の一般化

SQL/JSONファンクションjson_tableは、SQL/JSON条件json_existsと、SQL/JSONファンクションjson_valueおよびjson_queryを一般化します。これらの関数を使用して実行できる処理はすべて、json_tableを使用して実行できます。これらによって実行されるジョブについては、これらの関数の構文の方がjson_tableの構文よりも簡単に使用できます。

同じデータにアクセスするために、json_exists、json_valueまたはjson_queryのいずれかを複数回使用するか、それらの組合せを使用する場合は、そのかわりにjson_tableの単一の呼出しを使用できます。これにより、多くの場合、問合せが読みやすくなり、1回のみデータを読み取るように最適化されます。

このため、オプティマイザは一般にjson_exists、json_valueおよびjson_queryの複数の呼出し(任意の組合せ)を、それよりも少ないjson_tableの呼出しに自動的にリライトします。(実行計画を調べて、そのようなリライトが特定の問合せに対して発生したかどうかを確認できます)。

例21-4および例21-5に、この例を示します。これらではそれぞれ、列j_purchaseorder.po_document内の各オブジェクトによって使用される要求者および一連の電話を選択しています。ただし、json_tableを使用する例では、その列が4回ではなく1回のみ読み取られます。

これらの例では、ブールJSON値を表すためにBOOLEAN SQL値を使用しています。(Oracle Databaseリリース23cでは、データ型BOOLEANに対するOracle SQLサポートが導入されています)。

JSON値のnullは、SQLに関するかぎりは1つの値であり、SQLで値の欠如(存在しないデータ、不明なデータまたは適用できないデータ)を表すNULLとは異なります。これらの例では、オブジェクト属性zipCodeのJSON値がnullの場合に、SQL BOOLEAN値のTRUEが返されます。

例21-4 JSONデータに複数回アクセスすることによるデータの抽出

この例では、SQL列j_purchaseorder.po_documentにアクセスするSQLファンクションの呼出しを4回使用しているため、その列を4回読み取ります。

SELECT json_value(po_document, '$.Requestor' RETURNING VARCHAR2(32)),
       json_query(po_document, '$.ShippingInstructions.Phone'
                  RETURNING VARCHAR2(100))
  FROM j_purchaseorder
  WHERE json_exists(po_document, '$.ShippingInstructions.Address.zipCode')
    AND json_value(po_document,  '$.AllowPartialShipment'
                   RETURNING BOOLEAN) = TRUE;

例21-5 JSON_TABLEを使用した複数回読み取ることのないデータ抽出

この例では、SQL列j_purchaseorder.po_documentにアクセスする単一のjson_table呼出しを使用するめ、その列を1回のみ読み取ります。

この例では、どちらの仮想列にもBOOLEAN SQL値を使用します。

列partialは、データ(フィールドAllowPartialShipment)のJSONブール値に対応します。この列には、json_valueセマンティクスが使用されます。
列has_zipは、json_tableキーワードEXISTSの使用による結果です。このキーワードは、json_existsのセマンティクスを使用するように指定します。

ノート: JSONデータがJSONデータ型の場合は、キーワードFORMAT JSONを使用しないでください。使用するとエラーが発生します。

SELECT jt.requestor, jt.phones
  FROM j_purchaseorder,
       json_table(po_document, '$'
         COLUMNS (
           requestor VARCHAR2(32 CHAR) PATH '$.Requestor',
           phones    VARCHAR2(100 CHAR) FORMAT JSON
                     PATH '$.ShippingInstructions.Phone',
           partial   BOOLEAN PATH '$.AllowPartialShipment',
           has_zip   BOOLEAN EXISTS
                     PATH '$.ShippingInstructions.Address.zipCode')) jt
  WHERE jt.partial AND jt.has_zip;

WHERE句は、あるいは次のように記述することもできます。

WHERE jt.partial = TRUE AND jt.has_zip = TRUE

関連項目

SQL/JSONファンクションJSON_VALUEとブール型のJSON値の使用

親トピック: SQL/JSONファンクションJSON_TABLE

21.4 JSON_TABLEとJSON配列の使用

JSON値は、1つの配列にすることも、1つ以上の配列を含めることも可能であり、他のJSON配列またはオブジェクト内の任意の数のレベルにネストしてもかまいません。NESTED PATH句にjson_tableを使用すると、配列の特定の要素を投影できます。

例21-6では、JSONデータ内の要求者および関連する電話番号を列po_document内に投影します。JSON配列Phone全体がJSONデータph_arrの列として投影されています。このJSONデータをVARCHAR2列としてフォーマットするには、JSONデータがJSONデータ型ではない場合、キーワードFORMAT JSONが必要となります(型がJSONデータの場合、このキーワードはエラーになります)。

JSON配列Phone全体ではなく、配列の個別要素のみを投影する場合はどうすればよいでしょうか。例21-7は、これを行う方法の1つを示しています。この方法は、投影する必要があるデータが配列要素のみである場合に使用できます。

要求者および関連する電話データの両方を投影する場合、例21-7の行パス式($.Phone[*])は適切ではありません。この式は、配列Phoneの(電話オブジェクト)要素のみが対象になります。

例21-8は、両方を対象にする方法の1つを示しています。ここでは、名前と電話配列全体の両方を対象にする行パス式を使用するとともに、個別電話オブジェクトのフィールドtypeおよびnumberを対象にする列パス式を使用しています。

例21-8では例21-6のように、JSONデータがJSONデータ型でない場合にはキーワードFORMAT JSONが必要になります。これは、結果のVARCHAR2列にJSONデータ(つまり、電話ごとに1つの配列要素という形式の電話の種類または電話番号の配列)が含まれるためです。また、例21-6の事例とは異なり、列phone_typeおよびphone_numにはラッパー句が必要になりますが、これは、配列Phoneにフィールドtypeおよびnumberを持つオブジェクトが複数含まれるためです。

場合によっては、例21-8の効果が不要なことがあります。たとえば、電話番号のJSON配列が含まれる列(特定の発注書のすべての番号に対して1つの行)ではなく、単一の電話番号が含まれるリレーショナル列(番号当たり1つの行)が必要な場合があります。

この結果を得るために、配列に対してjson_tableのNESTEDパス句を使用することにより、配列要素を投影するようjson_tableに命令を出す必要があります。NESTEDパス句は実質的に、追加の行ソース(行パターン)として機能します。例21-9に、これを示します。

キーワードNESTEDは1回のjson_tableの呼出しで何回でも使用できます。

例21-9では、外部のCOLUMNS句は、ネストした(内部の) COLUMNS句の親になります。定義されている仮想表は外部結合を使用して結合されますが、この場合、親句によって定義される表が結合における外部表になります。

(同じ親の下で直接ネストされた2番目の列の句がある場合、これら2つのネストした句は兄弟のCOLUMNS句になります。)

例21-6 JSON配列全体のJSONデータとしての投影

SELECT jt.*
  FROM j_purchaseorder,
       json_table(po_document, '$'
         COLUMNS (requestor VARCHAR2(32 CHAR) PATH '$.Requestor',
                  ph_arr    VARCHAR2(100 CHAR) FORMAT JSON
                            PATH '$.ShippingInstructions.Phone')
                 ) AS "JT";

例21-7 JSON配列の要素の投影

SELECT jt.*
  FROM j_purchaseorder,
       json_table(po_document, '$.ShippingInstructions.Phone[*]'
         COLUMNS (phone_type VARCHAR2(10) PATH '$.type',
                  phone_num  VARCHAR2(20) PATH '$.number')) AS "JT";

PHONE_TYPE     PHONE_NUM
----------     ---------
Office         909-555-7307
Mobile         415-555-1234

例21-8 JSON配列の要素と他のデータの投影

SELECT jt.*
  FROM j_purchaseorder,
       json_table(po_document, '$'
         COLUMNS (
           requestor  VARCHAR2(32 CHAR) PATH '$.Requestor',
           phone_type VARCHAR2(50 CHAR) FORMAT JSON WITH WRAPPER
                      PATH '$.ShippingInstructions.Phone[*].type',
           phone_num  VARCHAR2(50 CHAR) FORMAT JSON WITH WRAPPER
                      PATH '$.ShippingInstructions.Phone[*].number')) AS "JT";

REQUESTOR    PHONE_TYPE            PHONE_NUM
---------    ----------            ---------
Alexis Bull  ["Office", "Mobile"]  ["909-555-7307", "415-555-1234"]

例21-9 JSON_TABLE: NESTEDを使用した配列要素の投影

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

SQL識別子が引用符で囲まれている列numberを除いて("number")、実際のSQL列名は大文字です。(列numberは小文字です。)

最初の問合せでは、列名の記述は、大/小文字の区別を含め、対象となるフィールド前と完全に同じです。引用符で囲まれるかどうかに関係なく、これらは、適切なパスを設定するために大/小文字を区別して解釈されます。

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

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

SELECT jt.*
  FROM j_purchaseorder po,
       json_table(po.po_document
         COLUMNS (Requestor,
                  NESTED ShippingInstructions.Phone[*]
                    COLUMNS (type, "number"))) AS "JT";

SELECT jt.*
  FROM j_purchaseorder po,
       json_table(po.po_document, '$'
         COLUMNS (Requestor VARCHAR2(4000) PATH '$.Requestor',
                  NESTED
                    PATH '$.ShippingInstructions.Phone[*]'
                    COLUMNS (type     VARCHAR2(4000) PATH '$.type',
                             "number" VARCHAR2(4000) PATH '$.number'))
       ) AS "JT";

関連項目

親トピック: SQL/JSONファンクションJSON_TABLE

21.5 JSON_TABLEを使用したJSONデータに対するビューの作成

問合せのパフォーマンスを向上させるために、SQL/JSONファンクションjson_tableを使用して、列に投影するJSONデータに対してビューを作成できます。問合せのパフォーマンスをさらに向上させるには、マテリアライズド・ビューを作成し、JSONデータをインメモリーに配置できます。

例21-10では、JSONデータに対してビューを定義しています。ここでは、NESTEDパス句を使用して配列LineItemsの要素を投影しています。

例21-11では、例21-10と同じデータおよび構造を持つマテリアライズド・ビューを定義しています。

一般に、ビューは直接更新できません(マテリアライズドかどうかは関係ありません)。マテリアライズド・ビューの作成にキーワードREFRESHおよびON STATEMENTが使用されている場合(例21-11を参照)、そのビューは元表が更新されたときに自動的に更新されます。

json_tableを使用すると、任意のフィールドをビュー列として投影でき、(マテリアライズドかどうかにかかわらず)ビューの作成に任意の表の結合および任意の数のjson_tableの起動を含めることができます。

次に、例21-10と例21-11の唯一の相違点を示します。

キーワードMATERIALIZEDの使用
BUILD IMMEDIATEの使用
REFRESH FAST ON STATEMENT WITH PRIMARY KEYの使用。

REFRESH FASTの使用は、マテリアライズド・ビューが増分的にリフレッシュされることを意味します。これが発生するようにするには、WITH PRIMARY KEYまたはWITH ROWID(主キーがない場合)を使用する必要があります。表に基づいてマテリアライズド・ビューを作成する際には、JSON列が含まれるベースとなる表に主キーを指定し、WITH PRIMARY KEYを使用することをお薦めします。REFRESH FASTは、複数表マテリアライズド結合ビューおよび(単一または複数表)マテリアライズド集計ビューで使用できます。

ビューの作成にON COMMIT (ON STATEMENTではなく)を使用できます。前者は、表の更新トランザクションがコミットされた場合にのみ、元表を使用してビューを同期します。それまで表の変更はビューに反映されません。ON STATEMENTを使用すると、ビューがDML文ごとにただちに同期されます。これは、ON STATEMENTを使用して作成したビューでは、実行される可能性のあるロールバックを反映することを意味します。(後続のCOMMIT文がトランザクションを終了し、ロールバックを回避します。)