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

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

JSONデータを使用するSQLファンクションのためのPASSING、RETURNING、ラッパー、エラー、空白フィールドおよび不一致の句について説明します。それぞれの句は、SQLファンクションおよび条件のjson_value、json_query、json_table、json_serialize、json_transform、json_mergepatch、is json、is not json、json_existsおよびjson_equalのうちのいずれかまたは複数で使用されます。

SQLファンクションおよび条件のPASSING句
Oracle SQLファンクションのjson_transform、SQL/JSONファンクションのjson_valueおよびjson_query、SQL/JSON条件のjson_existsでは、オプションのPASSING句を受け入れます。この句では、パス式で使用するためにSQL値をSQL/JSON変数にバインドします。
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ファンクションおよび条件のエラー句
JSONデータに対する一部のSQL問合せファンクションおよび条件は、オプションでエラー句を受け入れます。この句によって、ファンクションまたは条件によって発生した実行時エラーの処理方法を指定します。この句についてと、デフォルト動作(エラー句なし)についてまとめます。
SQL/JSON問合せファンクションで使用される空白フィールド句
SQL/JSON問合せファンクションのjson_value、json_queryおよびjson_tableは、オプションでON EMPTY句を受け入れます。これは、対象となるJSONフィールドが問合せデータ内にない場合の処理を指定します。この句についてと、デフォルト動作(ON EMPTY句なし)について説明します。
SQL/JSON問合せファンクションで使用されるON MISMATCH句
ON MISMATCH句をSQL/JSONファンクションjson_value、json_queryおよびjson_tableで使用して、型照合の例外を処理できます。対象のJSON値が指定のSQL戻り値と一致しない場合に使用する処理を指定します。この句についてと、デフォルト動作(ON MISMATCH句なし)について説明します。
SQLファンクションおよび条件のTYPE句
Oracle SQLファンクションのjson_transform、SQL/JSONファンクションの json_query、json_valueとjson_tableおよびSQL/JSON条件のjson_existsでは、オプションのTYPE句を受け入れます。この句では、JSON値がJSON言語の型に関して厳密に(つまり、関連する"only"データ型変換項目メソッドが比較対象のデータに適用されたかのように)比較されるかどうかを指定します。

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

17.1 SQLファンクションおよび条件のPASSING句

Oracle SQLファンクションのjson_transform、SQL/JSONファンクションのjson_valueおよびjson_query、SQL/JSON条件のjson_existsでは、オプションのPASSING句を受け入れます。この句では、パス式で使用するためにSQL値をSQL/JSON変数にバインドします。

キーワードPASSINGの後に、カンマで区切られた1つ以上のSQL/JSON変数バインディング(42 AS "d"など)が続きます。

各バインディングは、(1)評価するSQL式、(2)キーワードAS、(3)SQL/JSON変数名で構成されます。^脚注1バインディング42 AS "d"は、式の値42をdという名前のSQL/JSON変数にバインドします。これは、$.PONumber?(@ > $d)などのパス式で使用できます。

PASSING句をTYPE (STRICT)句とともに使用すると、パス式のSQL/JSON変数と比較される各値が、関連する"only"データ型変換項目メソッドが値に適用されたかのように、JSON言語の型に関して厳密に比較されます。比較に使用される型は、SQL/JSON変数の型です。

たとえば、TYPE (STRICT)と指定した場合、変数$dの数値の値に対する$.PONumber?(@ > $d)などの比較は、$.PONumber?(@.numberOnly() > $d)であるかのように暗黙的に処理されます。そのため、これらの2つの問合せは同じ動作をします。$dの値は数値であるため、値が数値であるPONumberフィールドのみが考慮されます。

SELECT count(*) FROM j_purchaseorder
WHERE json_exists(po_document, '$.PONumber?(@.numberOnly() > $d)'
PASSING to_number(:1) AS "d");

SELECT count(*) FROM j_purchaseorder
WHERE json_exists(po_document, '$.PONumber?(@ > $d)'
PASSING to_number(:1) AS "d" TYPE(STRICT));

評価する式のデータ型は、JSON、VARCHAR2、NUMBER、BINARY_DOUBLE、DATE、TIMESTAMPまたはTIMESTAMP WITH TIME ZONEである必要があり、それ以外の場合は、エラーが発生します。

式がSQLのNULL値に評価される場合、その結果は、次のように、そのNULL値のSQL型に応じて異なります:

SQL型JSONのNULLを渡すと、エラーが発生します。
SQL型VARCHAR2のNULLを渡すと、その変数は空のJSON文字列""にバインドされます。
SQL型RAWのNULLを渡すと、その変数は長さゼロのJSONバイナリ値にバインドされます。
その他のSQL型のNULLを渡すと、その変数はJSONのnull値にバインドされます。

ノート:

SQL/JSON変数名にはSQL識別子の構文がありますが、次の制限があります。

SQL/JSON変数名には、その定義に使用されるSQL識別子に引用符文字が含まれていても、引用符文字は含まれません。

JSONファンクションおよび条件のPASSING句では、キーワードASに続くSQL識別子は、引用符で囲まれた識別子または引用符で囲まれていない識別子(AS "d"やAS dなど)にすることができます。これは、1つ目のケース(名前に引用符なし)ではdという名前の、2つ目のケース(暗黙的に大文字)ではDという名前のSQL/JSON変数を定義します。(1つ目のケースのSQL識別子はdではなく"d"で、2番目のケースではdではなくDです。)
SQL/JSON変数名に使用できるのは、ASCII英数字またはASCIIアンダースコア文字(10進コード95)のみです。さらに、名前は数字ではなく文字またはアンダースコア文字で始める必要があります。たとえば、42 AS "2d"、42 AS "d+"および42 AS "dã"ではそれぞれエラーが発生し、1つ目の理由は数字で始まるため、2つ目の理由は英数字ではないASCII文字(+)が含まれているため、3つ目の理由はASCII以外の文字(ã)が含まれているためです。

SQL/JSON変数は、$の後にSQL/JSON変数名が続きます。たとえば、$dはDという名前の変数です。

SQL/JSONパス式では、SQL識別子ではなくSQL/JSON変数が使用されます。特に、これは引用符が存在しないことを意味し、名前を直接使用します。たとえば、$.PONumber?(@ > $"d")ではエラーが発生し、$.PONumber?(@ > $d)が正しい構文です。

関連トピック

関連項目:

PASSING句の詳細は、『Oracle Database SQL言語リファレンス』のJSON_EXISTS条件に関する項を参照してください

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

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

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

json_valueの場合は、RETURNING句で次のSQLデータ型のいずれかを使用できます: VARCHAR2、NVARCHAR2、CHAR、NCHAR、BOOLEAN、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、RAW^脚注2、CLOBおよびNCLOB。ユーザー定義のオブジェクト型またはコレクション型を使用することもできます。

(JSONのブール値が対象である場合の戻り型の詳細は、SQL/JSONファンクションJSON_VALUEとブール型のJSON値の使用を参照してください。)

ノート:

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

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句は、オプションのキーワード(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がない場合、このケースはエラーとして扱われ、エラー句またはデフォルトのエラー処理動作によって通常どおり処理されます。

ファンクションjson_valueもオプションのTYPE句を受け入れます。TYPE (STRICT)を使用した場合、投影された(戻された)値は、関連する"only"データ型変換項目メソッドが適用されたかのように、JSON言語の型に関して厳密に比較されます。

たとえば、この2つの問合せは等価です。値が数値であるPONumberフィールドのみが考慮(投影)されます。

SELECT json_value(po_document, '$.PONumber.numberOnly()') FROM j_purchaseorder
  RETURNING NUMBER;

SELECT json_value(po_document, '$.PONumber') FROM j_purchaseorder
  RETURNING NUMBER TYPE (STRICT);

LOBを返すことができるJSONの任意のSQLファンクションの場合、デフォルトではLOBは参照によって返されます。かわりに、キーワードVALUEを使用して戻り型(ファンクションに応じてCLOB、BLOBまたはNCLOB)に従うことで、値ベースのLOBを返すことができます。たとえば:

SELECT json_value(...) FROM ... RETURNING CLOB VALUE;

関連項目

関連項目:

SQLデータ型DATEおよびTIMESTAMPの詳細は、『Oracle Database SQL言語リファレンス』を参照してください
SQLデータ型NUMBERの詳細は、『Oracle Database SQL言語リファレンス』を参照してください
値ベースのLOBの詳細は、Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイドの値LOBを参照してください。
Oracle Spatial and Graphデータの使用の詳細は、『Oracle Spatial開発者ガイド』を参照してください
GeoJSON.org

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

17.3 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 21cより前では、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は同じことを意味します。

ノート:

OMIT QUOTES句を使用する場合は、json_queryで配列ラッパーを使用できません。使用すると、コンパイル時エラーが発生します。

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

表17-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つを選択し、さらに処理したい場合があります。配列ラッパーを使用すると、このような処理を行うことができます。

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

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

関連項目

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

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

17.4 SQLファンクションおよび条件のエラー句

JSONデータに対する一部の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_equal、json_serialize、json_scalar、json_mergepatch、またはjson_exists動作が含まれた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句によっては処理されません。

関連項目

関連項目:

JSON用のSQLファンクションのエラー句の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
JSON用のSQL条件のエラー句の詳細は、『Oracle Database SQL言語リファレンス』を参照してください

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

17.5 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のみが使用されます。

ノート:

SQL/JSONファンクションjson_valueが、record型または索引付き表型のRETURNING型が含まれているPL/SQLコードで使用されていると、NULLが返せなくなります。これらの型は不可分的にNULLにできないためです。

そのため、NULL ON MISMATCH句とNULL ON EMPTY句では、こうしたコレクション型に対してNULL値を返せません。NULLが返されることはなく、コンパイル時エラーが発生します。(SQLオブジェクト、VARRAYまたはネストされた表に対するRETURNING型が含まれたPL/SQLコードの場合、それらの型の値は不可分的にNULLにできるため、このような例外は存在しません)。

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

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

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

関連項目

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

17.6 SQL/JSON問合せファンクションで使用されるON MISMATCH句

ON MISMATCH句をSQL/JSONファンクションjson_value、json_queryおよびjson_tableで使用して、型照合の例外を処理できます。対象のJSON値が指定のSQL戻り値と一致しない場合に使用する処理を指定します。この句についてと、デフォルト動作(ON MISMATCH句なし)について説明します。

ノート:

ON MISMATCH句は、ON EMPTY句とON ERROR句のいずれも適用されない場合にのみ適用されます。これは一般に、対象となるJSONデータがパス式と一致するが、その対象となるデータの型が指定された戻り型と一致しない場合に適用されます。より正確には、対象となるデータが戻り型に変換できない場合にON MISMATCHが適用されます。たとえば、対象値"cat" (JSON文字列)は、SQL NUMBER値に変換できません。

ON EMPTY句は、パス式で対象とされたフィールドが問合せ対象データに存在しない場合に適用されます。

ON ERROR句は、問合せの処理中にエラーが発生した場合に適用されます。これには、問合せ構文が無効な場合と、配列ラッパーが指定されていないjson_value問合せまたはjson_query問合せで複数の値を対象とする場合が含まれます。

問合せで、ファンクションjson_value、json_queryまたはjson_tableによる対象となったJSONデータが反映されたSQL値が返される場合は、対象となったデータと返される値の型が一致する必要があります。そうでない場合は、エラーが発生します。

ON ERRORハンドラが指定されている場合は、その動作がON MISMATCHのデフォルト動作として適用されます: これは、ON MISMATCH句が指定されていない場合の型不一致での動作です。

型不一致の動作の指定には、次の方法で1つ以上のON MISMATCH句を使用できます。

IGNORE ON MISMATCH: デフォルトの動作(不一致を無視)を明示的に指定します。返されるオブジェクトまたはコレクションには、対象となるJSONデータに対する不一致のため、1つ以上のSQL NULL値が含まれることがあります。

この値は、問合せがユーザー定義オブジェクトまたはコレクション型のインスタンスを対象とする場合のみ使用できます。これは、json_value (またはjson_valueセマンティクスが含まれるjson_table列)が使用されている場合にのみ当てはまる可能性があります。別の型のデータが対象とされると、エラーが発生します。
NULL ON MISMATCH — 値としてSQL NULLを返します。

ノート:

SQL/JSONファンクションjson_valueが、record型または索引付き表型のRETURNING型が含まれているPL/SQLコードで使用されていると、NULLが返せなくなります。これらの型は不可分的にNULLにできないためです。

そのため、NULL ON MISMATCH句とNULL ON EMPTY句では、こうしたコレクション型に対してNULL値を返せません。NULLが返されることはなく、コンパイル時エラーが発生します。(SQLオブジェクト、VARRAYまたはネストされた表に対するRETURNING型が含まれたPL/SQLコードの場合、それらの型の値は不可分的にNULLにできるため、このような例外は存在しません)。
ERROR ON MISMATCH — 不一致の場合にエラーが発生します。

ファンクションjson_value (またはjson_valueセマンティクスが含まれるjson_table)がユーザー定義のオブジェクト型またはコレクション型のインスタンスを返す場合、ON MISMATCH句の各型の後に、それぞれが処理する不一致の種類を示す1つ以上の句をカンマ(,)で区切り、カッコで囲んで((…))続けることができます。不一致の種類は次のとおりです。

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

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

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

ノート:

"only"項目メソッド(名前に"only"が含まれている項目メソッド)を使用する場合は、指定したタイプの入力型の値のみ互換性があります。たとえば、項目メソッドbooleanOnly()を使用する場合は、JSON言語型booleanの入力値のみ、リストされた変換先SQL型(BOOLEAN、VARCHAR2およびCLOB)と互換性があります(変換可能)。

このコンテキストにおいては、項目メソッドidOnly()は例外です。これは、その入力が、JSON言語型ファミリのバイナリであるだけでなく識別子として適している必要もあるためです。「JSONデータ型の値の比較とソート」も参照してください。

表17-2 互換性のあるスカラー・データ型: JSONからSQLへの変換

JSON言語型(ソース)	SQL型(宛先)	ノート
`binary`	`RAW`	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`binary`	`BLOB`	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`binary`	`CLOB`	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`boolean`	`BOOLEAN`	インスタンス値は、SQLブール値`TRUE`または`FALSE`です。
`boolean`	`VARCHAR2`	インスタンス値は、SQL文字列`"true"`または`"false"`です。
`boolean`	`CLOB`	インスタンス値は、SQL文字列`"true"`または`"false"`です。
`date`	`DATE`。時間コンポーネント(場合によってはゼロ)が含まれます^脚注4	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`date`	`TIMESTAMP`	時間コンポーネントにはゼロが埋め込まれます。SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`daysecondInterval`	`INTERVALDAYTOSECOND`	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`double`	`BINARY_DOUBLE`	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`double`	`BINARY_FLOAT`	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`double`	`NUMBER`	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`double`	`VARCHAR2`	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`double`	`CLOB`	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`float`	`BINARY_FLOAT`	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`float`	`BINARY_DOUBLE`	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`float`	`NUMBER`	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`float`	`VARCHAR2`	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`float`	`CLOB`	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`null`	任意のSQLデータ型。	インスタンス値はSQL `NULL`です。
`number`	`NUMBER`	なし。
`number`	`BINARY_DOUBLE`	なし。
`number`	`BINARY_FLOAT`	なし。
`number`	`VARCHAR2`	なし。
`number`	`CLOB`	なし。
`string`	`VARCHAR2`	なし。
`string`	`CLOB`	なし。
`string`	`NUMBER`	JSON文字列は数値である必要があります。
`string`	`BINARY_DOUBLE`	JSON文字列は数値である必要があります。
`string`	`BINARY_FLOAT`	JSON文字列は数値である必要があります。
`string`	`DATE`。時間コンポーネント(場合によってはゼロ)が含まれます^脚注4	JSON文字列は、サポートされているISO8601形式である必要があります。
`string`	`TIMESTAMP`	JSON文字列は、サポートされているISO8601形式である必要があります。
`string`	`INTERVALYEARTOMONTH`	JSON文字列は、サポートされているISO 8601継続時間形式である必要があります。
`string`	`INTERVALDAYTOSECOND`	JSON文字列は、サポートされているISO 8601継続時間形式である必要があります。
`timestamp`	`TIMESTAMP`	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`timestamp`	`DATE`。時間コンポーネント(場合によってはゼロ)が含まれます^脚注4	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。
`yearmonthInterval`	`INTERVALYEARTOMONTH`	SQL型`JSON`として格納されているJSONデータに対してのみサポートされます。

^脚注4たとえば、ゼロの時間コンポーネントが含まれているDATEインスタンスは時間コンポーネントの保持が指定されていないjson_value RETURNING DATE句によって返されます。

例17-1 ON MISMATCH句の使用

この例では、次のオブジェクト・リレーショナル・データを様々な問合せで使用します。型不一致の動作を除き、問合せは同一です。各問合せは、存在しないJSONフィールドmiddleを対象とします。

CREATE TYPE person_T AS OBJECT (
  first     VARCHAR2(30),
  last      VARCHAR2(30),
  birthyear NUMBER);

この問合せでは、オブジェクトperson_t('Grace', 'Hopper', 1906)を返します。デフォルトのエラー・ハンドラはNULL ON ERRORであるため、フィールドmiddleは無視されます。

SELECT json_value('{"first":     "Grace",
                    "middle":    "Brewster",
                    "last":      "Hopper",
                    "birthyear": "1906"}',
                  '$'
                  RETURNING person_t)
FROM DUAL;

この問合せでは、余計なデータの不一致のためにエラーが発生します。フィールドmiddleは余計です。

SELECT json_value('{"first":     "Grace",
                    "middle":    "Brewster",
                    "last":      "Hopper",
                    "birthyear": "1906"}',
                  '$'
                  RETURNING person_t
                  ERROR ON MISMATCH (EXTRA DATA))
FROM DUAL;
ORA-40602: extra data for object type conversion

この問合せでは、3つのON MISMATCH句を使用します。オブジェクトperson_t('Grace', 'Hopper', NULL)を返します。ERROR ON MISMATCH (EXTRA DATA)句は、それ自体でエラーが発生しますが、IGNORE ON MISMATCH (TYPE ERROR)によってそのエラーは無視されます。

SELECT json_value('{"first":     "Grace",
                    "middle":    "Brewster",
                    "last":      "Hopper",
                    "birthyear": "1906"}',
                  '$'
                  RETURNING person_t
                  ERROR ON MISMATCH (EXTRA DATA)
                  ERROR ON MISMATCH (MISSING DATA)
                  IGNORE ON MISMATCH (TYPE ERROR))
FROM DUAL;

関連トピック

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

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

17.7 SQLファンクションおよび条件のTYPE句

Oracle SQLファンクションのjson_transform、SQL/JSONファンクションの json_query、json_valueとjson_tableおよびSQL/JSON条件のjson_existsでは、オプションのTYPE句を受け入れます。この句では、JSON値がJSON言語の型に関して厳密に(つまり、関連する"only"データ型変換項目メソッドが比較対象のデータに適用されたかのように)比較されるかどうかを指定します。

キーワードTYPEの後に、カッコで囲んだキーワードSTRICTまたはLAXが続きます。

TYPE (LAX)は、デフォルトの動作(TYPE句なしと同じ)を指定します。つまり、JSON値は、比較のためにSQLデータ型の値として暗黙的に解釈(実質的にキャスト)できます。この型キャストについては、「比較における型」を参照してください。

たとえば、'$.PONumber?(@ > 20)などの比較では、PONumber値"314"が数値314として暗黙的に解釈されます(数値20と比較されるため)。この比較は、式が'$.PONumber?(@.number() > 20)の場合と同様にtrueです
TYPE (STRICT)は、"only"項目メソッドを適用するのと同じ効果があります。

たとえば、'$.PONumber?(@ > 20)は'$.PONumber?(@.numberOnly() > 20)であるかのように動作します。PONumber値"314"の場合、式が'$.PONumber?(@.numberOnly() > 20)の場合と同様に比較はfalseです。

関連項目:

Oracle Database SQL言語リファレンスのJSON_QUERY

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

脚注の凡例

脚注1: PASSING句で二重引用符(")文字でSQL/JSON変数名をラップする必要があるのは、名前で大文字と小文字を区別する必要がある場合のみです。
脚注2: 入力データがJSONデータ型である場合のみ、RAWを戻り型として使用できます。
脚注3: JSONデータ型は、データベース初期化パラメータcompatibleが20以上である場合にのみ使用できます。