13 SQL/JSON問合せファンクションおよび条件で使用される句
RETURNING
、ラッパー、エラーおよび空白フィールドの句について説明します。これらは、SQLファンクションおよび条件のjson_value
、json_query
、json_table
、is json
、is not json
、json_exists
およびjson_equal
の1つ以上で使用されます。
- SQL/JSON問合せファンクションで使用されるRETURNING句
SQL/JSON問合せファンクションjson_value
およびjson_query
は、オプションでRETURNING
句を受け入れます。この句は、関数から返される値のデータ型を指定します。この句についてと、デフォルト動作(RETURNING
句なし)について説明します。 - SQL/JSON問合せファンクションJSON_QUERYおよびJSON_TABLEで使用されるラッパー句
SQL/JSON問合せファンクションのjson_query
およびjson_table
は、オプションのラッパー句を受け入れます。これには、json_query
によって返される値、またはjson_table
列のデータに使用される値の形式を指定します。この句についてと、デフォルト動作(ラッパー句なし)について説明します。例を示します。 - SQL/JSON問合せファンクションおよび条件で使用されるエラー句
一部のSQL/JSON問合せファンクションおよび条件は、オプションでエラー句を受け入れます。これにより、関数または条件によって発生した実行時エラーの処理方法を指定します。この句についてと、デフォルト動作(エラー句なし)についてまとめます。 - SQL/JSON問合せファンクションで使用される空白フィールド句
SQL/JSON問合せファンクションのjson_value
、json_query
およびjson_table
は、オプションでON EMPTY
句を受け入れます。これは、対象となるJSONフィールドが問合せデータ内にない場合の処理を指定します。この句についてと、デフォルト動作(ON EMPTY
句なし)について説明します。
親トピック: JSONデータの問合せ
13.1 SQL/JSON問合せファンクションで使用されるRETURNING句
SQL/JSON問合せファンクションjson_value
およびjson_query
は、オプションでRETURNING
句を受け入れます。この句は、関数から返される値のデータ型を指定します。この句についてと、デフォルト動作(RETURNING
句なし)について説明します。
json_value
の場合、VARCHAR2
、NUMBER
、DATE
、TIMESTAMP
、TIMESTAMP WITH TIME ZONE
、SDO_GEOMETRY
およびCLOB
のSQLデータ型から任意のものをRETURNING
句で使用できます。 json_query
の場合、VARCHAR2
、CLOB
またはBLOB
を使用できます。BLOB
の結果はAL32UTF8キャラクタ・セットです。
必要に応じて、VARCHAR2
の長さ(デフォルト: 4000
)、およびNUMBER
の精度とスケールを指定できます。
デフォルトの動作(RETURNING
句なし)では、VARCHAR2(4000)
を使用します。
データ型SDO_GEOMETRY
は、Oracle Spatial and Graphデータ向けです。特に、これは、json_value
をGeoJSONデータで使用できることを意味します。このデータは、JSONで地理データをエンコーディングするための形式です。
また、RETURNING
句は、PRETTY
およびASCII
の2つのオプションのキーワードも受け入れます。両方を使用する場合は、PRETTY
がASCII
の前にくる必要があります。ASCII
は、SQL/JSONファンクションjson_value
およびjson_query
でのみ使用できます。PRETTY
は、json_query
でのみ使用できます。
キーワードPRETTY
を使用すると、改行文字の挿入およびインデントにより、戻されたデータがわかりやすいように出力されます。デフォルトの動作では、わかりやすいようには出力されません。
キーワードASCII
を使用すると、標準のASCII Unicodeエスケープ・シーケンスを使用することにより、戻されるデータ内の非ASCII Unicode文字がすべて自動的にエスケープされます。デフォルトの動作では、非ASCII Unicode文字はエスケープされません。
ヒント:
パス式として$
のみを使用することにより、コンテキスト項目全体をわかりやすいように出力できます。
RETURNING
句でVARCHAR2
を指定した場合、値のスカラーは次のように表されます。
-
ブール値は、小文字の文字列
"true"
および"false"
で表されます。 -
null
値は、SQLNULL
で表されます。 -
JSONの数値は、正準な形式で表されます。このため、JSONの数値では、出力の文字列がテキストの入力データの表現と異なる可能性があります。正準な形式で表された場合、数値は次のようになります。
-
SQL
NUMBER
の精度と範囲の制限の対象となる場合があります。 -
SQL
NUMBER
の制限事項の対象とならない場合には、次のようになります。-
精度は40桁に制限されます。
-
オプションの指数は、9桁と1つの符号(
+
または-
)に制限されます。 -
使用できる符号(
-
、+
)、小数点(.
)および指数のインジケータ(E
)を含めて、テキスト全体が48文字に制限されます。
-
正準な形式のJSONの数値は、次のようなものです。
-
JSONの数値です。(JSONデータで数値として解析されます。)
-
先行するプラス(
+
)符号はありません。 -
必要な場合にのみ小数点(
.
)が使用されます。 -
その数値が小数(0と1の間)の場合は、小数点の前に単一のゼロ(
0
)が置かれます。 -
必要な場合にのみ指数表記法(
E
)が使用されます。これが当てはまるのは、特に、出力文字数が制限されすぎている場合です(VARCHAR2(N)
のN
が小さいことによる)。
-
関連項目:
-
Oracle Spatial and Graphデータの使用の詳細は、『Oracle Spatial and Graph開発者ガイド』を参照してください。
13.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
は同じことを意味します。
表13-1は、ラッパー句の使用可能性を示しています。配列ラッパーは太字
で示されています。
表13-1 JSON_QUERYラッパー句の例
パス式と一致するJSON値 | WITH WRAPPER | WITHOUT WRAPPER | WITH CONDITIONAL WRAPPER |
---|---|---|---|
|
|
|
|
|
|
|
|
|
|
エラー(スカラー) |
|
|
|
エラー(複数の値) |
|
なし |
|
エラー(値なし) |
|
たとえば、JSONオブジェクトを取得するためのjson_query
問合せを検討してみます。パス式がオブジェクトではなくJSONスカラー値と一致した場合、または複数のJSON値(任意の種類)と一致した場合はどうなるでしょうか。エラーを発生させずに、一致した値を取得したい場合があります。たとえば、オブジェクトである値の1つを選択し、さらに処理したい場合があります。配列ラッパーを使用すると、このような処理を行うことができます。
ラッパーを使用する理由がエラーの発生を回避することのみであり、これらのエラー事例をエラーでない事例と区別する必要がない場合、条件付きラッパーが役立ちます。アプリケーションが探しているものが単一のオブジェクトまたは配列であり、パス式によって一致したデータがそのものである場合、予期した値を単一の配列にラップする必要はありません。
一方、無条件ラッパーの場合、結果として生成される配列は常にラッパーであることが判明しています。アプリケーションはこの事実に頼ることができます。条件付きラッパーを使用する場合、アプリケーションでは、戻された配列を解析するために追加の処理を行う必要がある場合があります。たとえば、表13-1では、同じ配列([42, "a", true]
)が、この配列と一致するパス式、およびその各要素と一致するパス式という、まったく異なる事例に対して戻されています。
13.3 SQL/JSON問合せファンクションおよび条件で使用されるエラー句
一部のSQL/JSON問合せファンクションおよび条件は、オプションでエラー句を受け入れます。これにより、関数または条件によって発生した実行時エラーの処理方法を指定します。この句についてと、デフォルト動作(エラー句なし)についてまとめます。
デフォルトでは、SQLファンクションおよび条件により、実行時エラーの発生が回避されます。たとえば、JSONデータが構文的に無効な場合、json_exists
およびjson_equal
はfalseを戻し、json_value
はNULL
を戻します。
ただし、場合によってはデフォルト動作をオーバーライドするエラー句を指定することもできます。指定できるエラー処理は異なりますが、エラー処理を指定可能な各SQL/JSONファンクションおよび条件では、少なくともエラー発生の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_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
句によっては処理されません。
関連項目:
-
SQL/JSONファンクションのエラー句の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
-
SQL/JSON条件のエラー句の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
13.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
を使用することによって、問合せパス式の結果が複数の値や複雑な値になったときはエラーが発生するようにします。しかし、パス式の対象フィールドが欠落しているだけはエラーを発生させずに、そのデータをインデックス付けする場合があります。例24-5は、json_value
式で索引を作成するときのNULL ON EMPTY
の使用方法を示しています。