SQL/JSONファンクションJSON

17 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句はありません。)

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

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

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

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

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

json_table(t.j ...)

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

必須のCOLUMNS句は、json_tableによって作成される仮想表の列を定義します。これは、キーワードCOLUMNSの後ろに次のエントリをカッコで囲んだ形式で構成されます。

COLUMNS句内の多くても1つのエントリは、生成される行数の列(SQLデータ型NUMBER)を指定するキーワードFOR ORDINALITYを列名の後ろに付けた形式にすることができます。これらの数は1から始まります。次に例を示します。
```
COLUMNS (linenum FOR ORDINALITY, ProductID)
```
オプションのFOR ORDINALITYエントリ以外、COLUMNS句の各エントリは、標準列指定またはネストした列指定です。
標準列指定は、オプションで列のスカラー・データ型が付いた列名で構成されます。これには、json_valueのRETURNING句と同様に、SQLデータ型VARCHAR2、NUMBER、DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONEまたはSDO_GEOMETRYを使用でき、後ろにオプションの値句およびオプションのPATH句が付けられます。デフォルトのデータ型はVARCHAR2(4000)です。

データ型SDO_GEOMETRYは、Oracle Spatial and Graphデータ向けに使用されます。特に、これは、json_tableをGeoJSONデータで使用できることを意味します。このデータは、JSONで地理データをエンコーディングするための形式です。
ネストした列指定は、キーワードNESTEDの後ろにオプションのPATHキーワード、SQL/JSON行パス式、およびCOLUMNS句が付いた形式で構成されます。このCOLUMNS句は、ネストしたデータを表す列を指定します。ここで使用される行パス式により、指定したネストした列のコンテキストが洗練されます。ネストした各列のパス式は行パス式を基準にしたものになります。同じ行の列に異なるレベルで配列に存在する投影値に列の句をネストできます。

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

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

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

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

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

デフォルトでは、投影されたデータはjson_valueによって処理されたかのように処理されます。キーワードEXISTSを使用すると、json_existsによって処理されたかのように処理されます。キーワードFORMAT JSONを使用すると、json_queryによって処理されたかのように処理されます。

FORMAT JSONの場合、明示的なラッパー句を追加することにより、デフォルトのラッパー動作をオーバーライドできます。

特定のハンドラ(json_value、json_existsまたはjson_query)のデフォルトのエラー処理を、それに適した明示的なエラー句を追加することでオーバーライドできます。
オプションのPATH句は、列の内容として使用される行の部分を指定します。キーワードPATHに続く列パス式は、仮想行によって提供されるコンテキスト項目と照合されます。この列パス式は、行パス式によって指定されるパスに対して相対的であるため、相対パスの表記にする必要があります。

PATH句が存在しない場合、動作は'$.<column-name>'のパスで存在している場合と同様です。ここで、<column-name>は列名です。つまり、対象となるオブジェクト・フィールドの名前が列名として暗黙的に取得されます。対象となるフィールドのみを指定するために、<column-name>に使用されるSQL識別子は、引用符で囲まれていない場合でも大/小文字を区別して解釈されます。列のSQL名は通常のルールに従います。二重引用符(")で囲まれている場合、使用される文字の大/小文字は区別されますが、そうでない場合、区別されません(大文字と同様に処理されます)。

たとえば、これらの2つのCOLUMNS句は等価です。SQLの場合、大/小文字が区別されるのは、列Commentsのみです。
```
COLUMNS(ProductId, quantity NUMBER, "Comments")

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

特定のjson_table列指定のFORMAT JSONを使用する場合、json_queryのセマンティクスは、JSONデータを列に投影する場合に使用されます。列に指定したデータ型は、json_queryが戻すことができるSQLデータ型(VARCHAR2、CLOBまたはBLOB)のいずれかになります。

json_queryセマンティクスは、次のことを示します。

投影されるJSONデータは常に整形式です。これには、文字列値の非ASCII文字が必要に応じてエスケープされていることも含まれます。たとえば、タブ文字(CHARACTER TABULATION、U+0009)は\tとしてエスケープされます。
json_queryエラー処理が適用されます。
ラッパー句を使用して複数のJSON値を配列内の要素として投影できます。

特定のjson_table列指定でFORMAT JSONを使用しない場合、JSONデータを投影すると、json_valueセマンティクスが使用されます。列に指定したデータ型は、json_valueが戻すことができるSQLデータ型(VARCHAR2、NUMBER、DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、SDO_GEOMETRYまたはCLOB)のいずれかになります。json_valueエラー処理が適用されます(ラッパー句は使用できません)。

たとえば、ここで、列FirstNameの値はjson_valueセマンティクスを使用して直接投影されており、列Addressの値はjson_queryセマンティクスを使用してJSON文字列として投影されます。

COLUMNS (FirstName, Address FORMAT JSON)

投影されたデータがJSONオブジェクトまたは配列の場合は通常、FORMAT JSONを使用します。投影されたデータがJSONスカラーの場合は通常、FORMAT JSONは使用しないでください。

17.1 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回呼び出す方が、データが解析されるのが1回のみであるという利点があります。

このため、オプティマイザがjson_exists、json_valueおよびjson_queryの複数の呼出し(任意の組合せ)を、データの解析が1回だけになるように、より少ないjson_tableの呼出しに自動的にリライトすることがよく起こります。

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

例17-3と関連して次の点に注意してください。

JSON値のnullは、SQLに関するかぎりは1つの値であり、SQLで値の欠如(存在しないデータ、不明なデータまたは適用できないデータ)を表すNULLとは異なります。例17-3で、オブジェクト属性zipCodeのJSON値がnullである場合、SQL文字列'true'が戻されます。
json_existsはSQL条件です。これをSQL WHERE句、CASE文、またはチェック制約で使用できます。例17-2では、これをWHERE句で使用しています。ファンクションjson_tableでは、キーワードEXISTSを指定したときに、暗黙的にjson_existsのセマンティクスが使用されます。これは仮想列にSQL値を戻す必要があります。Oracle SQLにはブール・データ型がないため、SQL文字列'true'または'false'を使用してブール値を表します。これは例17-3の事例です。VARCHAR2値は列jt.has_zipに格納されてから、リテラルのSQL文字列'true'と等価かどうかが明示的にテストされています。
JSONフィールドAllowPartialShipmentには、JSONのブール値があります。json_valueはその値に適用される場合、これは文字列として戻されます。例17-3では、データ型VARCHAR2は列のデータ型として使用されています。ファンクションjson_tableは、暗黙的にこの列に対してjson_valueを使用し、VARCHAR2値として値を戻します。これにより、リテラルのSQL文字列'true'と等価かどうかがテストされます。

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

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 VARCHAR2(5 CHAR))
        = 'true';

例17-3 JSON_TABLEの使用によって複数回の解析が不要なデータの抽出

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   VARCHAR2(5 CHAR) PATH '$.AllowPartialShipment',
                  has_zip   VARCHAR2(5 CHAR) EXISTS
                            PATH '$.ShippingInstructions.Address.zipCode')) jt
  WHERE jt.partial = 'true' AND jt.has_zip = 'true';

関連項目

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

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

17.2 JSON_TABLEとJSON配列の使用

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

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

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

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

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

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

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

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

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

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

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

例17-4 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";

例17-5 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

例17-6 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"]

例17-7 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

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

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

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

例17-9では、例17-8と同じデータおよび構造を持つマテリアライズド・ビューを定義します。このようなマテリアライズド・ビューを直接更新することはできません。読取り専用のビューとして扱い、元表を更新することにより間接的に更新する必要があります。ビューを直接変更しようとすると、エラーが発生します。マテリアライズド・ビューはキーワードREFRESHおよびON STATEMENTを使用して作成されている場合、元表を更新するたびにビューが自動的に更新されます。

例17-8と例17-9の違いは、次にあげるもののみです。

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

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

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