22.4 JSON_TABLEとJSON配列の使用

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT jt.*
  FROM j_purchaseorder,
       json_table(data, '$.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         [phone]

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

SELECT jt.*
  FROM j_purchaseorder,
       json_table(data, '$'
         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"]

例22-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.data
         COLUMNS (Requestor,
                  NESTED ShippingInstructions.Phone[*]
                    COLUMNS (type, "number"))) AS "JT";

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