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

RETURNING、ラッパー、エラーおよび空白フィールドの句について説明します。これらは、SQL/JSONファンクションおよび条件のjson_value、json_query、json_table、is json、is not jsonおよびjson_existsの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句なし)について説明します。

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のSQLデータ型から任意のものをRETURNING句で使用できます。 json_queryの場合、使用できるのはVARCHAR2のみです。

必要に応じて、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値は、SQL NULLで表されます。

• JSONの数値は、正準な形式で表されます。このため、JSONの数値では、出力の文字列がテキストの入力データの表現と異なる可能性があります。正準な形式で表された場合、数値は次のようになります。

  • SQL NUMBERの精度と範囲の制限の対象となる場合があります。

  • SQL NUMBERの制限事項の対象とならない場合には、次のようになります。

    • 精度は40桁に制限されます。

    • オプションの指数は、9桁と1つの符号(+または-)に制限されます。

    • 使用できる符号(-、+)、小数点(.)および指数のインジケータ(E)を含めて、テキスト全体が48文字に制限されます。

  正準な形式のJSONの数値は、次のようなものです。

  • JSONの数値です。(JSONデータで数値として解析されます。)

  • 先行するプラス(+)符号はありません。

  • 必要な場合にのみ小数点(.)が使用されます。

  • その数値が小数(0と1の間)の場合は、小数点の前に単一のゼロ(0)が置かれます。

  • 必要な場合にのみ指数表記法(E)が使用されます。これが当てはまるのは、特に、出力文字数が制限されすぎている場合です(VARCHAR2(N)のNが小さいことによる)。

関連項目:

• Oracle Database SQL言語リファレンス

• Oracle Spatial and Graphデータの使用の詳細は、『Oracle Spatial and Graph開発者ガイド』を参照してください。

• GeoJSON.org

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

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

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

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

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

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

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

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

• ERROR ON ERROR - エラーを発生させます(特別な処理なし)。

• NULL ON ERROR - エラーを発生させるかわりにNULLを戻します。

  json_existsに使用できません。

• FALSE ON ERROR - エラーを発生させるかわりにfalseを戻します。

  デフォルトであるjson_existsにのみ使用できます。

• TRUE ON ERROR - エラーを発生させるかわりにtrueを戻します。

  json_existsにのみ使用できます。

• 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_table列値句

  • json_query、またはjson_query動作を含むjson_table列値句

  • json_tableの低レベル・エラー処理

  • SDO_GEOMETRYがjson_valueのRETURNING句データ・タイプとして、またはjson_table列データ・タイプとして指定されている場合

デフォルト動作はNULL ON ERROR(条件JSON_EXISTSを除く)です。

注意:

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の使用方法を示しています。