JSON_EXISTSの複数値関数ベースの索引の作成

30.8 JSON_EXISTSの複数値関数ベースの索引の作成

JSONデータ型として格納されるJSONデータの場合、SQL/JSON条件json_existsに複数値関数ベースの索引を使用できます。このような索引は、スカラーJSON値を個別に、またはJSON配列内で対象とします。

複数値索引の主な用途は、配列内のスカラー値の索引付けです。これにはスカラー配列要素が含まれますが、オブジェクト配列要素のスカラー・フィールド値も含まれます。

複数値索引では単一のスカラー値に索引付けすることもできますが、単一の値を対象とする問合せでは、一般に、Bツリー索引またはビットマップ索引を使用する方がパフォーマンスが高くなります。

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

複数値索引を作成するには、キーワードMULTIVALUEを指定したCREATE INDEXを使用し、SQL/JSONファンクションjson_tableの構文または索引付きデータへのパスを指定する問合せで使用するのと同じ単純なドット表記法構文を使用します。(ただし、SQL NESTED句をjson_tableのかわりに使用することはできません。使用するとコンパイル時にエラーが発生します。)

コンポジット関数ベースの索引を作成して、複数の仮想列(複数のJSONフィールド)に索引付けできます。コンポジット索引は、関数ベースの索引のセットのように動作します。問合せに使用する場合、ファンクションjson_tableを使用して、指定したJSONフィールド値をSQLスカラー値の仮想列として投影します。同様に、索引の定義に使用する場合、json_tableで指定したフィールド値はコンポジット関数ベースの索引として索引付けされます。

json_table構文を使用して複数値索引を作成する場合は、エラー処理句ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCHを使用する必要があります。そうしないと、問合せのコンパイル時エラーが発生します。json_tableを使用せずに単純なドット表記法構文を使用する場合、これらの句の動作は暗黙的に指定されます。

json_table構文を使用する場合、FOR ORDINALITY句を使用すると、特定の配列位置を対象とする問合せに対して索引を使用できるようになります。(SQL/JSONファンクションJSON_TABLEのCOLUMNS句を参照。)

複数値索引を問合せによって選択するには、索引付けするデータのSQL型を索引で指定する必要があり、問合せ結果のSQL型は索引で指定された型と一致する必要があります。

非コンポジット複数地索引を作成する場合、つまりjson_table構文を使用しない場合、索引指定には(binary()およびdateWithTime()以外の)データ型変換項目メソッドを含めてSQLデータ型を示す必要があります。データ型変換項目メソッドの詳細は、SQL/JSONパス式の項目メソッドを参照してください。

索引の名前に"only"を含む項目メソッドが使用されている場合は、その同じ項目メソッドを使用している問合せのみが索引を選択できます。それ以外の("only"が含まれていないメソッドを使用しているか、メソッドを使用していない)場合、項目メソッドで指定された型に変換できるスカラー値を(おそらく配列要素として)対象とする問合せで索引を選択できます。

たとえば、項目メソッドnumberOnly()を使用する複数値索引は、numberOnly()も使用する問合せに対してのみ選択できます。ただし、number()を使用する索引または項目メソッドを使用していない索引は、数値に変換可能な任意のスカラー(文字列"3.14"など)に一致する問合せに対して選択できます。

コンポジット複数値索引を作成する場合、json_tableの仮想列型では使用するSQL型を指定します。つまり、指定したSQL型に変換できるデータの問合せでは、索引を選択できます。

ただし、非コンポジット索引の場合と同様に、名前に"only"を含むデータ型変換項目メソッドを使用して、指定された列型をオーバーライド(さらに制約)できます。項目メソッドは列パス式で使用します。

たとえば、列型がNUMBERと指定されている場合、数値に変換可能なデータ(文字列"3.14"など)に一致する問合せでは、索引を選択できます。ただし、列パス式で項目メソッドnumberOnly()を使用している場合は、同様にnumberOnly()を使用している問合せのみが索引を選択できます。

特定のターゲットに複数の複数値索引を作成できます。たとえば、フィールドmonthに対して項目メソッドnumber()を使用する索引を作成し、同じフィールドに対して項目メソッドstring()を使用する別の索引を作成できます。

複数値索引を作成する方法として、次のことはできません。

コンポジット複数値索引の作成に使用されるjson_table式で、兄弟のネストされた配列を指定できません。しようとすると、エラーが発生します。複数の配列に索引付けできますが、兄弟にすることはできません。つまり、同じ親フィールドを持つことはできません。
SQL NESTED句の使用(JSON_TABLEの代替のSQL NESTED句を参照)。

json_table仮想列のスカラーJSON値の型とそれに対応するスカラーSQLデータ型の間の型エラー不一致は、表18-2で示されている型の非互換性、またはSQLデータ型の制約が厳しすぎること(データを格納するには小さすぎる)が原因である可能性があります。

エラー処理ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCHでは、1つ目の種類の不一致に対してSQL NULLを返しますが、2つ目の種類に対してエラーが発生します。たとえば、JSON文字列データに対してSQL型NUMBERの索引を作成する際に型の非互換性が許容されますが、JSON文字列値が"hello"のデータに対してSQL型VARCHAR(2)を使用して索引を作成しようとするとエラーが発生します。これは、データが2文字を超えるためです。

例30-13 表PARTS_TAB (複数値索引の例)

JSONデータ型列jpartsが含まれる表parts_tabが、この複数値索引の例で使用されています。JSONデータには、値がスカラー要素を含む配列であるフィールドsubpartsが含まれます。

CREATE TABLE parts_tab (id NUMBER, jparts JSON);

INSERT INTO parts_tab VALUES
  (1, '{"parts" : [{"partno"   : 3, "subparts" : [510, 580, 520]},
                   {"partno"   : 4, "subparts" : 730}]}');

INSERT INTO parts_tab VALUES
  (2, '{"parts" : [{"partno"   : 7, "subparts" : [410, 420, 410]},
                   {"partno"   : 4, "subparts" : [710, 730, 730]}]}');

例30-14 JSON_EXISTSの複数値索引の作成

ここで作成された複数値索引は、フィールドsubpartsの値を索引付けします。単純なドット表記法構文を使用する際は、表の別名(この場合はt)が必要です。

問合せの対象となるsubparts値が配列の場合、数値である配列要素の索引を選択できます。値がスカラーの場合、スカラーが数値であれば索引を選択できます。

表parts_tabのデータを考えた場合、最初の行(id 1)の配列partsの各オブジェクトのsubpartsフィールドが索引付けされます。1つ目のオブジェクトのフィールドは、配列値に数値(510、580および520)の要素があるため、2つ目のオブジェクトのフィールドは、値が数値(730)であるためです。

項目メソッドnumber()がnumberOnly()のかわりに索引定義で使用された場合、数値に変換可能な数値以外のスカラー値(文字列"730"など)も索引付けされます。

CREATE MULTIVALUE INDEX mvi ON parts_tab t
  (t.jparts.parts.subparts.numberOnly());

例30-15 JSON_EXISTSのコンポジット複数値索引の作成

この例では、フィールドpartnoとフィールドsubpartsの両方を対象とするコンポジット複数値索引を作成します。コンポジット索引は、これらの2つのフィールドを対象とする2つの関数ベースの索引のセットのように動作します。

この問合せでは、json_table構文をSQL/JSONパス式とともに行パターン$.parts[*]に使用します。json_tableを使用した複数値索引の作成では常にそうであるように、エラー処理はERROR ON ERROR NULL ON EMPTY NULL ON MISMATCHと指定します。

ここでは、列PARTNUMにSQLデータ型NUMBER(10)が指定されています。つまり、フィールドpartnoを対象とする問合せに索引を使用するには、そのフィールドの値がそのデータ型に変換できる必要があります。

表18-2で示されているように、型が一般的に互換性がないために型変換できない場合は、NULL ON MISMATCHエラー・ハンドラによってSQL NULLが返されます。この例として、型NUMBER(10)のSQL partNum列に対するpartno文字列値"hello"があります。
一方、SQLデータ型の記憶域の制約が厳しすぎる場合は、エラーが発生します。つまり、索引は作成されません。この例として、"1234567890123"など、10文字を超えるpartno文字列があります。

CREATE MULTIVALUE INDEX cmvi_1 ON parts_tab
  (json_table(jparts, '$.parts[*]'
     ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH
     COLUMNS (partNum NUMBER(10) PATH '$.partno',
       NESTED PATH '$.subparts[*]'
         COLUMNS (subpartNum NUMBER(20) PATH '$'))));

例30-16 配列位置を対象にできるコンポジット複数値索引の作成

この例のコードは例30-15に似ていますが、このコードでは、さらに順序性のための仮想列SEQを指定しています。つまり、その直前の列(SUBPARTNUM)の値には、配列subparts内の(1ベースの)位置を使用してアクセスできます。(FOR ORDINALITY列のSQLデータ型は、常にNUMBERです。)

常に、COLUMNS句内の多くても1つのエントリでは、列名の後に、生成された行番号の列(SQLデータ型NUMBER)を指定する、1から始まるFOR ORDINALITYを続けることができます。そうしないと、索引の作成時にエラーが発生します。

json_table構文のこの一般ルールに加えて、

json_tableを使用して複数値索引を作成する場合、FOR ORDINALITY列はjson_tableの最後の列である必要があります。(json_tableが問合せで使用される場合、これは必須ではありません。索引の作成にのみ適用されます。)
json_tableを使用して作成した複数値索引を特定の問合せに対して選択するには、その問合せではjson_table式の最初の仮想列に対応するJSONフィールドにフィルタ式を適用する必要があります。

位置で配列要素を対象とする問合せで配列位置に対して複数値索引を選択するには、それらの配列要素の索引列がFOR ORDINALITY列の直前の列である必要があります

(ここでのコードでは、単純なドット表記法を行パターンに使用します。かわりにSQL/JSONパス式を行パターンに使用した場合、残りのコードは同じになります。)

CREATE MULTIVALUE INDEX cmvi_2 ON parts_tab t
  (t.jparts.parts[*]
  ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH
  COLUMNS (partNum NUMBER(10) PATH '$.partno',
    NESTED PATH subparts[*]
      COLUMNS (subpartNum NUMBER(20) PATH '$',
               seq FOR ORDINALITY))));

関連トピック

親トピック: JSONデータの索引