JSON用のSQLファンクションおよび条件で使用される句

15 JSON用のSQLファンクションおよび条件で使用される句

JSONデータを使用する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データの問合せ

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

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

json_valueでは、次の任意のSQLデータ型をRETURNING句に使用できます: VARCHAR2、NUMBER、BINARY_DOUBLE、BINARY_FLOAT、DATE (オプションのキーワードPRESERVE TIMEまたはTRUNCATE TIMEを含む)、TIMESTAMP、TIMESTAMP WITH TIME ZONE、INTERVAL YEAR TO MONTH、INTERVAL DAY TO SECOND、SDO_GEOMETRYおよびCLOB。ユーザー定義のオブジェクト型またはコレクション型を使用することもできます。

注意:

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

デフォルトで、RETURNING DATEを含むjson_valueは、時間コンポーネントがゼロ(ゼロ時間、分および秒)のSQL DATE値を返します。つまり、デフォルトでは、問い合せたJSONスカラー値の時間コンポーネントは、返されたSQL DATEインスタンスでは切り捨てられます。

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またはJSONを使用できます。^脚注1

BLOBの結果はAL32UTF8キャラクタ・セットです。json_serializeによって返されるデータ型に関係なく、返されるデータはテキストのJSONデータを表します。

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

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

json_query (のみ)の場合、データベース初期化パラメータcompatibleが20以上で、入力データがJSONデータ型のとき:

デフォルトの戻り型(RETURNING句なし)もJSONです。

それ以外の場合、デフォルトの戻り型はVARCHAR2(4000)です。
戻りデータ型に関係なく、デフォルトでは、返されるデータをスカラーJSON値にすることができます。

戻りデータ型の直後にキーワードDISALLOW SCALARSを含めることで、この動作をオーバーライドできます。json_queryの呼出しでは、スカラーではないJSON値のみが返されます(RFC 8259がサポートされていない場合と同じ動作になります)。

RETURNING句は、オプションの2つのキーワード(PRETTYおよびASCII)も受け取ります(戻りデータ型がJSONの場合を除く)。両方を使用する場合は、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がない場合、このケースはエラーとして扱われ、エラー句またはデフォルトのエラー処理動作によって通常どおり処理されます。

関連トピック

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

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

json_queryまたはjson_table列のパス式で対象とするJSONデータは、単一のJSON値(スカラー、オブジェクトまたは配列値)または複数のJSON値にすることができます。オプションのラッパー句を使用すると、対象データを配列にラップしてから返すことができます。

たとえば、対象となるデータが値"A50"および{"a": 42}のセットの場合、これらがラップされて配列["A50", {"a": 42}] (または[{"a": 42}, "A50"])が返されるように指定できます(要素の順序は制御できません)。または、唯一の対象値が42の場合、これをラップして配列[42]を返すことができます。

Oracle Database 20cより前では、RFC 4627のみがサポートされていましたが、RFC 8259はサポートされませんでした。このコンテキストでは、単一のスカラーJSON値を返すことができませんでした。エラーが発生しないように、配列にラップする必要がありました。これはデータベース初期化パラメータcompatibleが20より低い場合に引き続き当てはまります。RFC 8259がサポートされている場合でも、結果を配列にラップする場合があります。

ラッパー句(またはラッパー句がない場合(キーワードWITHOUT WRAPPERを使用する場合と同じ))の動作は、(1)対象となるJSONデータが単一のスカラー値であるかどうか、および(2)単一のスカラー値を返すことがSQL/JSONファンクションの特定の呼出しで許可されているかどうかに応じて異なります。

ラップを行わず、次のいずれかに該当する場合、単一のスカラー値または複数値(スカラーまたはそれ以外)が返されると、エラーが発生します。

データベース初期化パラメータcompatibleが20より低い。
RETURNING句にキーワードDISALLOW SCALARSが使用されている。

ON EMPTY句はラッパー句より優先されます。前者のデフォルトはNULL ON EMPTYです。これは、パス式と一致するJSON値がない場合にSQLのNULLが返されることを意味します。かわりに空のJSON配列([])が返されるようにするには、EMPTY ARRAY ON EMPTYを指定します。かわりにエラーを発生させる場合は、ERROR ON EMPTYを指定します。

空ではない一致のためのラッパー句は次のとおりです。

WITH WRAPPER - パス式と一致するすべてのJSON値を含むJSON配列を使用します。配列要素の順序は指定されません。
WITHOUT WRAPPER: パス式と一致するJSON値を使用します。

次のいずれかの条件に該当する場合にエラーを発生させます。
- パス式が複数の値と一致する。
- スカラー値を返すことができず、パス式が(オブジェクトまたは配列ではなく)単一のスカラー値と一致する。
WITH CONDITIONAL WRAPPER - パス式と一致するすべてのJSON値を表す値を使用します。

複数のJSON値と一致する場合、これはWITH WRAPPERと同じです。

一致するJSON値が1つのみの場合:
- スカラー値を返すことが許可される場合、または一致する単一の値がオブジェクトまたは配列の場合は、WITHOUT WRAPPERと同じです。
- それ以外の場合、これはWith WRAPPERと同じです。

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

キーワードUNCONDITIONALは、コードを明確にするために使用できます。WITH WRAPPERとWITH UNCONDITIONAL WRAPPERは同じ内容を意味します。

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

表15-1は、ラッパー句の各種の使用例を示しています。配列ラッパーは太字のイタリックで示されています。

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

パス式と一致するJSON値	WITH WRAPPER	WITHOUT WRAPPER	WITH CONDITIONAL WRAPPER
`{"id": 38327}` (単一のオブジェクト)	`[{"id": 38327}]`	`{"id": 38327}`	`{"id": 38327}` (`WITHOUT WRAPPER`と同じ)
`[42, "a", true]` (単一の配列)	`[[42, "a", true]]`	`[42, "a", true]`	`[42, "a", true]` (`WITHOUT WRAPPER`と同じ)
`42`	`[42]`	`42` (単一のスカラー値を返すことができる場合) エラー(単一のスカラー値を返すことができない場合)	`42` (単一のスカラー値を返すことができる場合(`WITHOUT WRAPPER`と同じ)) `[42]` (単一のスカラー値を返すことができない場合(`WITH WRAPPER`と同じ))
`42`、`"a"`、`true` (複数の値)	`[42, "a", true]`	エラー(複数の値)	`[42, "a", true]` (`WITH WRAPPER`と同じ)
なし	`ON EMPTY`句によって判別されます。デフォルトではSQLの`NULL` (`NULL ON EMPTY`) `EMPTY ARRAY ON EMPTY`句を使用した場合は`[]`	エラー(値なし)	`WITH WRAPPER`と同じ。

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

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

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

関連項目

RFC 8259のサポート: JSONスカラー

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

15.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句によっては処理されません。

関連項目

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

関連項目

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

15.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スカラー・データ型との互換性がありません。これは、表17-1で示されている非互換性、またはSQLデータ型の制約が厳しすぎる(VARCHAR(2)がJSON文字列"hello"には短すぎるなど)が原因である可能性があります。

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

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

関連項目

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

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

脚注の凡例

脚注1: JSONデータ型は、データベース初期化パラメータcompatibleが20以上の場合にのみ使用できます。