SQL/JSONファンクションJSON

21 SQL/JSONファンクションJSON_QUERY

SQL/JSONファンクションjson_queryは、JSONデータから1つ以上の値を選択し、それらの値を返します。したがって、json_queryを使用してJSON文書のフラグメントを取得できます。

ユーザーが問い合せるJSONデータは、json_queryの最初の引数です。より正確には、JSONデータ(JSON^脚注1、VARCHAR2、CLOBまたはBLOB型)を含むSQLデータ型のインスタンスを返すSQL式です。これは、適切にキャストされた表またはビューの列の値、PL/SQL変数、バインド変数のいずれかになります。式の評価の結果は、パス式を評価するためのコンテキスト項目として使用されます(次に説明されています)。

json_queryの2番目の引数は、SQL/JSONパス式であり、オプションのRETURNING句、PASSING句、WRAPPER句、QUOTES句、ON ERROR句およびON EMPTY句が続きます。このパス式は、任意の数のJSON値を対象とすることができます。

パス式配列ステップでは、指定された各位置は、指定方法に関係なく順番にデータと照合されます。配列の索引と範囲の順序、索引の複数出現、および範囲の重複による指定された位置の重複が、すべて考慮されます。

RETURNING句で指定できるのは、データ型JSON、VARCHAR2、CLOBまたはBLOBです。BLOBの結果はAL32UTF8文字セットです。

ノート:

デフォルトでは、SQL/JSONファンクションjson_queryのデフォルトの戻り型(RETURNING句なし)は、入力データ型によって異なります:

入力の型がJSONの場合は、デフォルトの戻り型もJSONです。
それ以外の場合、デフォルトの戻り型はVARCHAR2です。

ただし、初期化パラメータJSON_BEHAVIORをオプションjson_query_ret_varcharにtrue値を指定して使用すると、現在のセッションのデフォルトの戻り型が必ずVARCHAR2になるように変更できます。(デフォルトでは、このオプションの値はfalseです。)

ALTER SESSION SET JSON_BEHAVIOR = "json_query_ret_varchar:true";

戻り値には、常に整形式のJSONデータが含まれています。これにより、文字列値の非ASCII文字が必要に応じてエスケープされていることが確認されます。たとえば、ASCIIタブ文字(Unicode文字CHARACTER TABULATION、U+0009)は\tとしてエスケープされます。キーワードFORMAT JSONは、json_queryに必要(または使用可能)ではありません。JSON形式は戻り値に対して暗黙的です。

ラッパー句により、戻される文字列値の形式が決まります。

(1) json_queryによって返されるJSON値が文字列で、(2)戻りデータ型がJSON型ではなくテキストの場合、JSON文字列区切り二重引用符文字は戻り値に含まれています。このコンテキストでは、二重引用符の区切り文字を省略する場合は、キーワードOMIT QUOTESを使用する必要があります。

たとえば、戻り型がVARCHAR2 (入力データがJSON型ではない場合のデフォルト)の場合、5文字のhelloのみを含むJSON文字列"hello"は、7文字のVARCHAR2値"hello"として返されます。

ノート:

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

json_queryのエラー句ではEMPTY ON ERRORを指定でき、これは、エラーの場合は空の配列([])が戻されることを意味します(エラーは発生しない)。

初期化パラメータcompatibleが20以上の場合、Oracle DatabaseではIETF RFC 8259がサポートされ、最上位レベルのJSONスカラー値のみをJSON文書に含めることができます。

compatibleパラメータが20より低い場合は、RFC 4627のみがサポートされます。JSON文書の最上位レベルに、スカラーではなくJSONオブジェクトまたは配列のみを含めることができます。RFC 8259には、RFC 4627 (およびRFC 7159)のサポートが含まれています。

RFC 8259がサポートされておらず、json_queryパス式の引数によって対象となった値が複数の値または単一のスカラー値である場合は、キーワードWITH WRAPPERを使用して、配列でラップされた値を返す必要があります。そうでない場合は、エラーが発生します。

RFC 8259がサポートされている場合、json_queryはデフォルトでスカラーJSON値を返すことができます。json_queryでスカラー以外のJSON値のみが返されるようにするには、RETURNING句にキーワードDISALLOW SCALARSを使用します。この場合の動作は、RFC 8259がサポートされていない場合と同じであるため、WITH WRAPPERを使用する必要があります。

例21-1は、SQL/JSONファンクションjson_queryとともに配列ラッパーを使用する例を示しています。文書ごとにVARCHAR2値が戻され、この内容は、電話のタイプの要素が不特定の順序で含まれるJSON配列を表します。例4-3の文書では、電話のタイプは"Office"および"Mobile"であり、戻される配列は[ "Mobile", "Office" ]または[ "Office", "Mobile" ]です。

例21-1ではパス式$.ShippingInstructions.Phone.typeが使用されていたとしても、同じ結果が得られることになる点に注目してください。SQL/JSONのパス式構文の緩和のため、[*].typeは.typeに相当します。