JSONデータの索引付け
索引がJSON索引であるのは、JSONデータ内に含まれるフィールドを1つ以上索引付けしている場合です。
JSONにはスキーマがないため、表の行によってJSONデータの型が異なる場合があります。ただし、JSONデータを索引付けするときには、表のすべての行でデータ型が同じである必要があります。そうでない場合、索引の作成は失敗します。さらに、1つ以上のJSON索引が作成された後で、誤った型のデータを書き込もうとすると失敗します。
前述の制限を除いて、JSONデータの索引付けとJSON索引の操作は、JSON以外のデータの索引付けとほぼ同様に行われます。索引を作成するには、ドット表記法を使用してJSONフィールドへのパスを指定します。ASキーワードを使用して、データの型を指定する必要もあります。
次の例は、JSONの操作で示した例に基づいています。
sql-> create index idx_json_income on JSONPersons (person.income
as integer);
Statement completed successfully
sql-> create index idx_json_age on JSONPersons (person.age as integer);
Statement completed successfully
sql-> その後、通常の方法で問合せを実行できます。このとき、索引idx_json_incomeが自動的に使用されます。ただし、この章の冒頭(基本的な索引付け)で示したように、問合せプロセッサでは使用する索引が認識されません。特定の索引の使用を必須にするには、通常どおりに索引ヒントを指定します。
sql-> SELECT /*+ FORCE_INDEX(JSONPersons idx_json_income) */ *
from JSONPersons j WHERE j.person.income > 10000000 and
j.person.age < 40;
> Row 0
+-----------+---------------------------------------------+
| id | 3 |
+-----------+---------------------------------------------+
| person | address |
| | city | Middleburg |
| | phones |
| | areacode | 305 |
| | number | 1234079 |
| | type | work |
| | |
| | areacode | 305 |
| | number | 2066401 |
| | type | home |
| | state | FL |
| | street | 187 Aspen Drive |
| | age | 38 |
| | connections |
| | 1 |
| | 4 |
| | 2 |
| | expenses |
| | food | 2000 |
| | gas | 10 |
| | travel | 700 |
| | firstname | John |
| | income | 100000000 |
| | lastLogin | 2016-11-29T08:21:35.4971 |
| | lastname | Morgan |
+-----------+---------------------------------------------+
1 row returned
sql-> 最後に、JSONマップで複数キー索引を作成する場合は、.keys()式に型を指定しないでください。これは、型は常にStringであるためです。ただし、.values()式には型宣言が必要です。
sql-> create index idx_json_expenses on JSONPersons
(person.expenses.keys(), person.expenses.values() as integer);
Statement completed successfully
sql-> SELECT * FROM JSONPersons j WHERE j.person.expenses.food > 1000;
> Row 0
+-----------+---------------------------------------------+
| id | 2 |
+-----------+---------------------------------------------+
| person | address |
| | city | Beloit |
| | phones |
| | areacode | 339 |
| | number | 1684972 |
| | type | home |
| | state | WI |
| | street | 187 Hill Street |
| | zipcode | 53511 |
| | age | 35 |
| | connections |
| | 1 |
| | 3 |
| | expenses |
| | books | 100 |
| | food | 1700 |
| | travel | 2100 |
| | firstname | John |
| | income | 100000 |
| | lastLogin | 2016-11-28T13:01:11.2088 |
| | lastname | Anderson |
+-----------+---------------------------------------------+
> Row 1
+-----------+---------------------------------------------+
| id | 3 |
+-----------+---------------------------------------------+
| person | address |
| | city | Middleburg |
| | phones |
| | areacode | 305 |
| | number | 1234079 |
| | type | work |
| | |
| | areacode | 305 |
| | number | 2066401 |
| | type | home |
| | state | FL |
| | street | 187 Aspen Drive |
| | age | 38 |
| | connections |
| | 1 |
| | 4 |
| | 2 |
| | expenses |
| | food | 2000 |
| | gas | 10 |
| | travel | 700 |
| | firstname | John |
| | income | 100000000 |
| | lastLogin | 2016-11-29T08:21:35.4971 |
| | lastname | Morgan |
+-----------+---------------------------------------------+
> Row 2
+-----------+---------------------------------------------+
| id | 4 |
+-----------+---------------------------------------------+
| person | address |
| | city | Leominster |
| | phones |
| | areacode | 339 |
| | number | 4120211 |
| | type | work |
| | |
| | areacode | 339 |
| | number | 8694021 |
| | type | work |
| | |
| | areacode | 339 |
| | number | 1205678 |
| | type | home |
| | null |
| | |
| | areacode | 305 |
| | number | 8064321 |
| | type | home |
| | state | MA |
| | street | 364 Mulberry Street |
| | age | 38 |
| | connections |
| | 3 |
| | 5 |
| | 1 |
| | 2 |
| | expenses |
| | books | 240 |
| | clothes | 2000 |
| | food | 6000 |
| | shoes | 1200 |
| | firstname | Peter |
| | income | 80000 |
| | lastLogin | 2016-10-19T09:18:05.5555 |
| | lastname | Smith |
+-----------+---------------------------------------------+
3 rows returned
sql-> 非JSONの複数キー索引に適用されるその他すべての制約は、JSONの複数キー索引にも適用されることに注意してください。