SQL問合せファンクションおよび条件で使用される句

16 SQL問合せファンクションおよび条件で使用される句

RETURNING、ラッパー、エラーおよび空白フィールドの句について説明します。これらは、SQLファンクションおよび条件のjson_value、json_query、json_table、json_serialize、json_mergepatch、is json、is not json、json_existsおよびjson_equalの1つ以上で使用されます。

SQL問合せファンクションで使用されるRETURNING句
SQLファンクションjson_value、json_query、json_serializeおよびjson_mergepatchは、オプションでRETURNING句を受け入れます。この句は、ファンクションから返される値のデータ型を指定します。この句についてと、デフォルト動作(RETURNING句なし)について説明します。
SQL/JSON問合せファンクションJSON_QUERYおよびJSON_TABLEで使用されるラッパー句
SQL/JSON問合せファンクションのjson_queryおよびjson_tableは、オプションのラッパー句を受け入れます。これには、json_queryによって返される値、またはjson_table列のデータに使用される値の形式を指定します。この句についてと、デフォルト動作(ラッパー句なし)について説明します。例を示します。
SQL問合せファンクションおよび条件で使用されるエラー句
一部のSQL問合せファンクションおよび条件は、オプションでエラー句を受け入れます。これにより、ファンクションまたは条件によって発生した実行時エラーの処理方法を指定します。この句についてと、デフォルト動作(エラー句なし)についてまとめます。
SQL/JSON問合せファンクションで使用される空白フィールド句
SQL/JSON問合せファンクションのjson_value、json_queryおよびjson_tableは、オプションでON EMPTY句を受け入れます。これは、対象となるJSONフィールドが問合せデータ内にない場合の処理を指定します。この句についてと、デフォルト動作(ON EMPTY句なし)について説明します。
JSON_VALUEのON MISMATCH句
RETURNING句でユーザー定義のオブジェクト型またはコレクション型インスタンスを指定する場合、ファンクションjson_valueには、オプションでON MISMATCH句を指定できます。この句では、対象のJSON値が指定したSQL戻り値と一致しない場合に使用する処理を指定します。この句についてと、デフォルト動作(ON MISMATCH句なし)について説明します。

親トピック: JSONデータの問合せ

16.1 SQL問合せファンクションで使用されるRETURNING句

SQLファンクションjson_value、json_query、json_serializeおよびjson_mergepatchは、オプションでRETURNING句を受け入れます。この句は、ファンクションから返される値のデータ型を指定します。この句についてと、デフォルト動作(RETURNING句なし)について説明します。

json_valueの場合、VARCHAR2、NUMBER、DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、SDO_GEOMETRYおよびCLOBの事前定義済SQLデータ型から任意のものをRETURNING句で使用できます。ユーザー定義のオブジェクト型またはコレクション型を使用することもできます。

ノート:

Oracle SQLデータ型DATEのインスタンスには、時間コンポーネントが含まれます。JSONデータでは、ISO 8601日時値を表す文字列を使用できます。つまり、時間コンポーネントを使用できます。

デフォルトで、RETURNING DATEを含むjson_valueは、時間コンポーネントがゼロ(ゼロ時間、分および秒)のSQL DATE値を返します。デフォルトでは、問い合せたJSONスカラー値の時間コンポーネントは、返されたSQL DATEインスタンスで切り捨てられます。ただし、時間の切捨てが行われる前に、ISO 8601の日時文字列で表される値にタイムゾーン・コンポーネントがある場合は、値は最初にUTCに変換され、タイムゾーン情報が考慮されます。

RETURNING DATE PRESERVE TIMEを使用すると、このデフォルトの切捨て動作をオーバーライドし、問い合せたJSONスカラー値の時間コンポーネント(存在する場合)を保持できます。(RETURNING DATE TRUNCATE TIMEの使用は、デフォルトの動作であるRETURNING DATEとまったく同じ効果があります。)

(同じ考慮事項が、TRUNCATE TIMEに対応する項目メソッドdate()と、PRESERVE TIMEに対応するdateWithTime()に適用されます。)

json_query、json_serializeおよびjson_mergepatchの場合、VARCHAR2、CLOBまたはBLOBを使用できます。BLOBの結果はAL32UTF8文字セットです。json_serializeによって返されるデータ型に関係なく、返されるデータはテキストのJSONデータを表します。

必要に応じて、VARCHAR2の長さ(デフォルト: 4000)、およびNUMBERの精度とスケールを指定できます。

デフォルトの動作(RETURNING句なし)では、VARCHAR2(4000)を使用します。

データ型SDO_GEOMETRYは、Oracle Spatial and Graphデータ向けです。特に、これは、json_valueをGeoJSONデータで使用できることを意味します。このデータは、JSONで地理データをエンコーディングするための形式です。

また、RETURNING句は、PRETTYおよびASCIIの2つのオプションのキーワードも受け入れます。両方を使用する場合は、PRETTYがASCIIの前にくる必要があります。キーワードPRETTYはjson_valueには指定できません。

キーワードPRETTYを使用すると、改行文字の挿入およびインデントにより、戻されたデータがわかりやすいように出力されます。デフォルトの動作では、わかりやすいようには出力されません。

キーワードASCIIを使用すると、標準のASCII Unicodeエスケープ・シーケンスを使用することにより、戻されるデータ内の非ASCII Unicode文字がすべて自動的にエスケープされます。デフォルトの動作では、非ASCII Unicode文字はエスケープされません。

ヒント:

パス式として$のみを使用することにより、コンテキスト項目全体をわかりやすいように出力できます。

RETURNING句でVARCHAR2を指定した場合、値のスカラーは次のように表されます。

ブール値は、小文字の文字列"true"および"false"で表されます。
null値は、SQL NULLで表されます。
JSONの数値は、正準な形式で表されます。このため、JSONの数値では、出力の文字列がテキストの入力データの表現と異なる可能性があります。正準な形式で表された場合、数値は次のようになります。
- SQL NUMBERの精度と範囲の制限の対象となる場合があります。
- SQL NUMBERの制限事項の対象とならない場合には、次のようになります。
  - 精度は40桁に制限されます。
  - オプションの指数は、9桁と1つの符号(+または-)に制限されます。
  - 使用できる符号(-、+)、小数点(.)および指数のインジケータ(E)を含めて、テキスト全体が48文字に制限されます。
正準な形式のJSONの数値は、次のようなものです。
- JSONの数値です。(JSONデータで数値として解析されます。)
- 先行するプラス(+)符号はありません。
- 必要な場合にのみ小数点(.)が使用されます。
- その数値が小数(0と1の間)の場合は、小数点の前に単一のゼロ(0)が置かれます。
- 必要な場合にのみ指数表記法(E)が使用されます。これが当てはまるのは、特に、出力文字数が制限されすぎている場合です(VARCHAR2(N)のNが小さいことによる)。

OracleではSQL/JSON標準を拡張し、戻りデータ型がVARCHAR2(N)の場合は、データ型の直後にオプションのキーワードTRUNCATEを指定できるようにしました。TRUNCATEが存在し、かつ戻り値がNよりも長い場合は、値が切り捨てられ、先頭N文字のみが戻されます。TRUNCATEがない場合、このケースはエラーとして扱われ、エラー句またはデフォルトのエラー処理動作によって通常どおり処理されます。

関連トピック

16.2 SQL/JSON問合せファンクションJSON_QUERYおよびJSON_TABLEで使用されるラッパー句

SQL/JSON問合せファンクションのjson_queryおよびjson_tableは、オプションのラッパー句を受け入れます。これには、json_queryによって返される値、またはjson_table列のデータに使用される値の形式を指定します。この句についてと、デフォルト動作(ラッパー句なし)について説明します。例を示します。

ラッパー句は、次のいずれかの形式をとります。

WITH WRAPPER - パス式と一致するすべてのJSON値が含まれるJSON配列を表す文字列値を使用します。配列要素の順序は指定されません。
WITHOUT WRAPPER - パス式と一致する単一のJSONオブジェクトまたは配列を表す文字列値を使用します。パス式がスカラー値(オブジェクトまたは配列ではありません)または複数の値と一致する場合、エラーが発生します。
WITH CONDITIONAL WRAPPER - パス式と一致するすべてのJSON値を表す文字列値を使用します。0個の値、単一のスカラー値、または複数の値の場合、WITH CONDITIONAL WRAPPERはWITH WRAPPERと同じです。単一のJSONオブジェクトまたは配列値の場合は、WITHOUT WRAPPERと同じです。

デフォルトの動作はWITHOUT WRAPPERです。

オプションのキーワードUNCONDITIONALは、これを使用する方が明確になる場合はキーワードWITHの直後に追加できます。WITH WRAPPERとWITH UNCONDITIONAL WRAPPERは同じことを意味します。

オプションのキーワードARRAYは、これを使用する方が明確になる場合はキーワードWRAPPERの直前に追加できます。WRAPPERとARRAY WRAPPERは同じことを意味します。

表16-1は、ラッパー句の使用可能性を示しています。配列ラッパーはboldで示されています。

表16-1 JSON_QUERYラッパー句の例

パス式と一致するJSON値	WITH WRAPPER	WITHOUT WRAPPER	WITH CONDITIONAL WRAPPER
`{"id": 38327}` (単一のオブジェクト)	`[{"id": 38327}]`	`{"id": 38327}`	`{"id": 38327}`
`[42, "a", true]` (単一の配列)	`[[42, "a", true]]`	`[42, "a", true]`	`[42, "a", true]`
`42`	`[42]`	エラー(スカラー)	`[42]`
`42`, `"a"`, `true`	`[42, "a", true]`	エラー(複数の値)	`[42, "a", true]`
なし	`[]`	エラー(値なし)	`[]`

たとえば、JSONオブジェクトを取得するためのjson_query問合せを検討してみます。パス式がオブジェクトではなくJSONスカラー値と一致した場合、または複数のJSON値(任意の種類)と一致した場合はどうなるでしょうか。エラーを発生させずに、一致した値を取得したい場合があります。たとえば、オブジェクトである値の1つを選択し、さらに処理したい場合があります。配列ラッパーを使用すると、このような処理を行うことができます。

ラッパーを使用する理由がエラーの発生を回避することのみであり、これらのエラー事例をエラーでない事例と区別する必要がない場合、条件付きラッパーが役立ちます。アプリケーションが探しているものが単一のオブジェクトまたは配列であり、パス式によって一致したデータがそのものである場合、予期した値を単一の配列にラップする必要はありません。

一方、無条件ラッパーの場合、結果として生成される配列は常にラッパーであることが判明しています。アプリケーションはこの事実に頼ることができます。条件付きラッパーを使用する場合、アプリケーションでは、戻された配列を解析するために追加の処理を行う必要がある場合があります。たとえば、表16-1では、同じ配列([42, "a", true])が、この配列と一致するパス式、およびその各要素と一致するパス式という、まったく異なる事例に対して戻されています。

親トピック: SQL問合せファンクションおよび条件で使用される句

16.3 SQL問合せファンクションおよび条件で使用されるエラー句

一部のSQL問合せファンクションおよび条件は、オプションでエラー句を受け入れます。これにより、ファンクションまたは条件によって発生した実行時エラーの処理方法を指定します。この句についてと、デフォルト動作(エラー句なし)についてまとめます。

デフォルトでは、JSON用のSQLファンクションおよび条件により、実行時エラーの発生が回避されます。たとえば、JSONデータが構文的に無効な場合、json_existsおよびjson_equalはfalseを戻し、json_valueはNULLを戻します。

ただし、場合によってはデフォルト動作をオーバーライドするエラー句を指定することもできます。指定できるエラー処理は異なりますが、エラー処理を指定可能なJSON用の各SQLファンクションおよび条件では、少なくともエラー発生のERROR ON ERROR動作がサポートされます。

オプションのエラー句は次の形式を使用できます。

ERROR ON ERROR - エラーを発生させます(特別な処理なし)。
NULL ON ERROR - エラーを発生させるかわりにNULLを戻します。

json_existsに使用できません。
FALSE ON ERROR - エラーを発生させるかわりにfalseを戻します。

デフォルトであるjson_existsおよびjson_equalにのみ使用できます。
TRUE ON ERROR - エラーを発生させるかわりにtrueを戻します。

json_existsおよびjson_equalにのみ使用できます。
EMPTY OBJECT ON ERROR - エラーを発生させるかわりに空のオブジェクト({})を戻します。

json_queryにのみ使用できます。
EMPTY ARRAY ON ERROR - エラーを発生させるかわりに空の配列([])を戻します。

json_queryにのみ使用できます。
EMPTY ON ERROR – EMPTY ARRAY ON ERRORと同じ。
DEFAULT 'literal_return_value' ON ERROR - エラーを発生させるかわりに指定した値を戻します。値は、問合せのコンパイル時に定数である必要があります。

使用不可:
- json_exists動作を含むjson_exists、json_equal、json_serialize、json_mergepatchまたはjson_table列値句の場合
- json_query、またはjson_query動作を含むjson_table列値句
- json_tableの低レベル・エラー処理
- SDO_GEOMETRYがjson_valueのRETURNING句データ・タイプとして、またはjson_table列データ・タイプとして指定されている場合

デフォルト動作はNULL ON ERROR(条件json_existsおよびjson_equalを除く)です。

ノート:

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

ノート:

欠落フィールドの照合を試行したことによるエラーの場合、ON EMPTY句はON ERRORで指定された動作より優先されます。

ノート:

ON ERROR句は、構文的に正しいSQL/JSONパス式がJSONデータに対して照合されたときに発生する実行時エラーに対してのみ有効です。構文的に正しくないパス式の場合、コンパイル時エラーが発生します。これは、ON ERROR句によっては処理されません。

関連項目

16.4 SQL/JSON問合せファンクションで使用される空白フィールド句

SQL/JSON問合せファンクションのjson_value、json_queryおよびjson_tableは、オプションでON EMPTY句を受け入れます。これは、対象となるJSONフィールドが問合せデータ内にない場合の処理を指定します。この句についてと、デフォルト動作(ON EMPTY句なし)について説明します。

通常、SQL/JSONファンクションおよび条件のエラーへの対処は、エラー句(ON ERROR)を使用して行います。しかし、データ内に見つからない特定のJSONフィールドと照合するために問合せるときのように、この通常のエラー処理とは異なる対処が必要な特別なケースがあります。照合するフィールドがないという理由だけでエラーを発生させたくない場合があります。(欠落フィールドは、通常エラーとして扱われます。)

通常は、ON ERROR句と一緒にNULL ON EMPTY句を使用します。このように組み合せることにより、通常のエラーはON ERROR句に従って処理されますが、欠落フィールドの照合を試行した場合のエラーにはNULLが戻されるだけになります。このときに、ON EMPTY句がない場合は、ON ERROR句で欠落フィールドのケースも処理されます。

NULL ON EMPTYの他に、ERROR ON EMPTYとDEFAULT ... ON EMPTYも使用できます。これらは、同様の名前を持つON ERROR句と類似しています。

ON EMPTY句のみがある(ON ERROR句がない)場合、欠落フィールドの動作はON EMPTY句によって指定され、その他のエラーは、NULL ON ERROR句がある場合と同じ方法で処理されます(これがON ERRORのデフォルト)。どちらの句もない場合には、NULL ON ERRORのみが使用されます。

JSON_VALUEで作成された索引に対するNULL ON EMPTYの使用

NULL ON EMPTYは、json_value式で作成される関数索引の場合に特に便利です。この句は、索引の選択を行うかどうか、またはいつ行うかへの影響は持ちませんが、json_value式の対象であるフィールドが欠落しているために索引付けが行われない一部のデータでインデックス付けが行われるようにすることを許可する際に有効です。

索引を移入する問合せには、通常ERROR ON ERRORを使用することによって、問合せパス式の結果が複数の値や複雑な値になったときはエラーが発生するようにします。しかし、パス式の対象フィールドが欠落しているだけはエラーを発生させずに、そのデータをインデックス付けする場合があります。例28-5は、json_value式で索引を作成するときのNULL ON EMPTYの使用方法を示しています。

関連項目

親トピック: SQL問合せファンクションおよび条件で使用される句

16.5 JSON_VALUEのON MISMATCH句

RETURNING句でユーザー定義のオブジェクト型またはコレクション型インスタンスを指定する場合、ファンクションjson_valueには、オプションでON MISMATCH句を指定できます。この句では、対象のJSON値が指定したSQL戻り値と一致しない場合に使用する処理を指定します。この句についてと、デフォルト動作(ON MISMATCH句なし)について説明します。

ノート:

ON ERROR句およびON EMPTY句は、パス式に一致するJSONデータがない場合にのみ適用されます。パス式に一致するJSONデータが、指定されたオブジェクトまたはコレクションの戻り型と一致しない場合は、ON MISMATCHが句が適用されます。戻り型がユーザー定義のオブジェクト型またはコレクション型のインスタンスである場合にのみ、ON MISMATCH句を使用できます。別の戻り型で使用する場合は、問合せのコンパイル時エラーが発生します。

json_valueファンクションが対象とするJSONデータを反映するSQLオブジェクト型またはコレクション型のインスタンスを返す場合、その対象とするデータと、返されるオブジェクトまたはコレクションの定義が一致する必要があります。一致しない場合、問合せのコンパイル時エラーが適用されます。

このようなエラーのデフォルト処理は、無視するだけです。ただし、次のように1つ以上のON MISMATCH句を指定して、このようなエラーを様々な方法で処理できます。

IGNORE ON MISMATCH: デフォルトの動作(不一致を無視)を明示的に指定します。返されるオブジェクトまたはコレクションのインスタンスには、対象のJSONデータに対する不一致のため、1つ以上のSQL NULL値を含めることができます。
NULL ON MISMATCH—オブジェクト型またはコレクション型の値としてSQL NULLを返します。
ERROR ON MISMATCH—不一致の場合に問合せのコンパイル時エラーを発生させます。

また、各タイプのON MISMATCH句の後には、それぞれが処理する不一致の種類を示す1つ以上の句をカンマ(,)で区切り、カッコ((…))で囲んで指定できます。不一致の種類は次のとおりです。

MISSING DATA—オブジェクト型またはコレクション型のデータに一致するために必要だったJSONデータが欠落していました。
EXTRA DATA—1つ以上のJSONフィールドに、対応するオブジェクト型またはコレクション型のデータがありません。たとえば、JSONフィールドaddressについて、同じ名前のオブジェクト型属性がありません(デフォルトでは大文字と小文字を区別しません)。
TYPE ERROR—JSONスカラー値のデータ型に、対応する戻りSQLスカラー・データ型との互換性がありません。これは、表18-1で示されている非互換性、またはSQLデータ型の制約が厳しすぎる(VARCHAR(2)がJSON文字列"hello"には短すぎるなど)が原因である可能性があります。

指定されたハンドラ(NULL ON MISMATCHなど)に対してこのような種類の不一致句(EXTRA DATAなど)が存在しない場合、そのハンドラはすべての種類の不一致に適用されます。

様々な種類のON MISMATCH句を必要な数だけ使用できますが、2つ以上が互いに矛盾する場合は、問合せのコンパイル時エラーが発生します。

関連項目

JSON_VALUEを使用したユーザー定義オブジェクト型インスタンスのインスタンス化

親トピック: SQL問合せファンクションおよび条件で使用される句