SQL/JSONファンクションJSON

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

SQL/JSONファンクションjson_tableは、特定のJSONデータを各種SQLデータ・タイプの列に投影します。これを使用して、JSON式の評価結果を新規の仮想表の行および列に分解しますが、これは、インラインのビューであるとみなすこともできます。

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

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

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

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

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

json_tableの呼出しによって作成される行は、暗黙的にこれらの行の生成元の行に水平結合されます。つまり、json_tableによって生成される仮想表を、JSONデータが含まれる表に明示的に結合する必要はありません。

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

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

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

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

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

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

json_tableの2番目の引数はSQL/JSON行パス式であり、行を処理するためのオプションのエラー句と(必須の) COLUMNS句が付加されます。(RETURNING句はありません。)このパス式は、任意の数のJSON値を対象とすることができます。

行パス式は、生成される仮想表の行のパターンとして機能します。これが、SQLのFROM句によって提供されるコンテキスト項目と照合されることにより、COLUMNS句に指定する列に編成されるSQLデータの行が生成されます。これらの各行が0個以上の列パス式と照合され、仮想表の列が生成されます。

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

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

COLUMNS句内の多くても1つのエントリは、生成される行数の列(SQLデータ型NUMBER)を指定するキーワードFOR ORDINALITYを列名の後ろに付けた形式にすることができます。これらの数は1から始まります。
オプションの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-6は、ネストした列句の使用を示しています。
オプションの値句は、列に投影されたデータを処理する方法、つまり、データを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に続く列パス式は、仮想行によって提供されるコンテキスト項目と照合されます。この列パス式は、行パス式によって指定されるパスに対して相対的であるため、相対パスの表記にする必要があります。

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

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

JSON値のnullは、SQLに関するかぎりは1つの値であり、SQLで値の欠如(存在しないデータ、不明なデータまたは適用できないデータ)を表すNULLとは異なります。例17-2で、オブジェクト属性zipCodeのJSON値がnullである場合、SQL文字列'true'が戻されます。
json_existsによってブール値が戻されますが、これは、SQL値として、SQL文字列'true'または'false'によって表されます。json_existsがSQLのWHERE句またはCASE文の条件として直接使用される場合、この戻り値を明示的にテストする必要はありません。単にjson_exists(...)と入力してかまいません。ただし、json_existsが値を得るために他の場所で使用される場合、この値をテストする唯一の方法はこの値を明示的な文字列として使用する方法です。これが例17-2の事例です。値は列jt.has_zipに格納されてから、SQL文字列'true'と等価かどうかが明示的にテストされています。
JSONオブジェクト属性AllowPartialShipmentには、JSONのブール値があります。この値にjson_valueが適用される場合、これは文字列または数値として戻される場合があります。例17-2では、データ型NUMBERは列のデータ型として使用されます。関数json_tableは、暗黙的にこの列に対してjson_valueを使用し、値を数値として戻します。この値の数値1に対する等価性がテストされます。

例17-1 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 NUMBER(1))
        = 1;

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

17.2 JSON_TABLEとJSON配列の使用

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

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

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

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

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

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

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

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

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

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

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

例17-3 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-4 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-5 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-6 JSON_TABLE: NESTEDを使用した配列要素の投影

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

関連項目

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

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

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

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

例17-8では、例17-7と同じデータおよび構造を持つマテリアライズド・ビューを定義します。このようなマテリアライズド・ビューは更新用には使用できません。読取り専用のビューとして扱う必要があります。変更を試みると、エラーが発生します。

例17-7と例17-8の違いは、次にあげるものだけです。

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

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