22.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句のすべての柔軟性が実現され、暗黙的な左外部結合が実行されます。例22-2に、これを示します。

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

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

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

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

7C3A54B183056369E0536DE05A0A15E4 Alexis Bull Office 909-555-7307
7C3A54B183056369E0536DE05A0A15E4 Alexis Bull Mobile [phone]
7C3A54B183066369E0536DE05A0A15E4 Sarah Bell

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

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

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

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

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