18.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で配列ラッパーを使用できません。使用すると、コンパイル時エラーが発生します。

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

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

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

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