30.9 複数値関数ベースの索引の使用

WHERE句のjson_exists問合せでは、対象とするデータが索引に指定されたスカラー型と一致する場合(かつその場合にかぎり)、複数値関数ベースの索引を選択できます。

SQL/JSON条件json_existsの複数値関数ベースの索引は、個別にまたはJSON配列の要素としてスカラーJSON値を対象とします。複数値索引は、JSONデータ型として格納されているJSONデータに対してのみ定義できます。

条件json_existsは、対象となるデータが問合せのSQL/JSONパス式(または同等の単純なドット表記法構文)と一致する場合にtrueを返します。それ以外の場合、falseを返します。通常は、パス式にフィルタ式を含めます。つまり、照合では対象データがフィルタを満たす必要があります。

名前に"only"を含むデータ型変換項目メソッド(numberOnly()など)を使用して定義される複数値索引は、同様に同じ項目メソッドを使用するjson_exists問合せによってのみ選択できます。つまり、問合せでは同じ項目メソッドを明示的に使用する必要があります。詳細は、JSON_EXISTSの複数値関数ベースの索引の作成を参照してください。

項目メソッドを使用せずに、または名前に"only"が含まれていないデータ型変換項目メソッド(number()など)を使用して定義された複数値索引は、項目メソッドで指定された型に変換できるスカラー値を(おそらく配列要素として)対象とする問合せで選択できます。データ型変換項目メソッドの詳細は、SQL/JSONパス式の項目メソッドを参照してください。

この例では、WHERE句でSQL/JSON条件json_existsを使用して、730に一致するsubpartsフィールド値がないかチェックします。これらは、複数値索引mvi、cmvi_1およびcmvi_2 (JSON_EXISTSの複数値関数ベースの索引の作成で定義)を選択できるどうかの観点で説明されています。JSONスカラー値のSQLスカラー値への変換は、表18-2を参照してください。

例30-17 項目メソッドnumberOnly()を使用したJSON_EXISTS問合せ

この例では、WHERE句で項目メソッドnumberOnly()を使用します。パス式が数値のsubparts値730 (たとえばsubparts : 730)または1つ以上の数値の要素730が含まれる配列のsubparts値(たとえばsubparts:[630, 730, 690, 730])を対象とする場合、問合せでは索引mviを選択できます。対象となる文字列値"730" (たとえばsubparts:"730"またはsubparts:["630", "730", 690, "730"])に対しては、索引mviを選択できません。

かわりに、項目メソッドnumber()を使用して索引mviが定義されている場合、この問合せでは、数値のsubparts値730、文字列のsubparts値"730"または数値要素730または文字列要素"730"が含まれる配列のsubparts値に対して索引を選択できます。

SELECT count(*) FROM parts_tab
  WHERE json_exists(jparts, '$.parts.subparts?(@.numberOnly() == 730)');

例30-18 項目メソッドnumberOnly()を使用しないJSON_EXISTS問合せ

これらの2つの問合せでは、項目メソッドnumberOnly()を使用しません。1つ目はメソッドnumber()を使用します。このメソッドは対象データを数値に変換します(可能な場合)。2つ目は対象データを型変換しません。

索引mviは、対象データが数値730であっても、どちらの問合せでも選択できません。索引はnumberOnly()を使用して定義されているため、索引を選択するには、問合せでnumberOnly()を使用する必要があります。

SELECT count(*) FROM parts_tab t
  WHERE json_exists(jparts, '$.parts.subparts?(@.number() == 730)');

SELECT count(*) FROM parts_tab t
  WHERE json_exists(jparts, '$.parts.subparts?(@ == 730)');

例30-19 複数フィールドをチェックするJSON_EXISTS問合せ

この問合せのフィルタ式では、SQL NUMBER値4と一致するpartnoフィールド(おそらくJSON文字列からの変換によるもの)および数値730と一致するフィールドsubpartsの有無を指定します。

この問合せでは、索引cmvi_1またはcmvi_2のいずれかを選択できます。各行に数値4と一致するparts.partno値と、数値730と一致するparts.subparts値があるため、データのどちらの行もこれらの索引と一致します。subparts一致の場合、1行目にはsubparts値730が、2行目には値730が含まれる配列であるsubparts値があります。

SELECT a FROM parts_tab
  WHERE json_exists(jparts,'$.parts[*]?(@.partno == 4 &&
                                        @.subparts == 730)');

例30-20 配列要素の位置をチェックするJSON_EXISTS問合せ

この例は例30-19に似ていますが、フィールドpartnoが数値4と一致する必要があることに加えて、ここでのフィルタ式では、フィールドsubpartsの値が少なくとも2つの要素の配列と一致し、配列の2つ目の要素が数値730と一致している必要があります。

この問合せでは、位置述語[1]に対しても含め、索引cmvi_2を選択できます。索引cmvi_2では、仮想列subpartNumを指定します。これは、最終列FOR ORDINALITYの直前、最後から2番目の列としてJSONフィールドsubpartsに対応します。

この問合せでは索引cmvi_1も選択できますが、その索引にFOR ORDINALITY列がないため、使用するには、配列の位置条件[1]を評価するために追加ステップが必要になります。索引cmvi_2の使用には、このような追加ステップは必要ないため、このような問合せのパフォーマンスは向上します。

SELECT a FROM parts_tab
  WHERE json_exists(jparts,'$.parts[*]?(@.partno == 4 &&
                                        @.subparts[1] == 730)');