SQL/JSONファンクションJSON

22.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)です。

列のデータ型は、BINARY_DOUBLE、BINARY_FLOAT、BOOLEAN、CHAR、CLOB、DATE (オプションのキーワードPRESERVE TIMEまたはTRUNCATE TIMEを指定)、DOUBLE PRECISION、FLOAT、INTEGER、NUMBER、INTERVAL YEAR TO MONTH、INTERVAL DAY TO SECOND、NCHAR、NCLOB、NVARCHAR2、RAW^脚注1、REAL、SDO_GEOMETRY、TIMESTAMP、TIMESTAMP WITH TIME ZONEおよびVARCHAR2のいずれかにすることができます。ユーザー定義のオブジェクト型またはコレクション型を使用することもできます。

データ型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句によって定義される仮想列は、和結合を使用して結合されます。

例22-1および例22-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_valueセマンティクスが使用される場合、RETURNINGの型がVECTORであれば、対象となるデータはJSONスカラー値ではなく、数値のJSON配列になることもあります。戻り型がVECTORでない場合、または配列に数値以外の要素がある場合は、配列入力でエラーが発生します。

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')
```
例22-1に、これを示す等価の問合せを示します。

SQL/JSONパス式のかわりに、PATH句でドット表記法を使用することもできます。例22-2および例22-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(data, '$'
         COLUMNS (ponumb NUMBER PATH '$.PONumber.numberOnly()')) jt

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