Indexing JSON Data
An index is a JSON index if it indexes at least one field that is contained inside JSON data.
Because JSON is schema-less, it is possible for JSON data to differ in type across table rows. However, when indexing JSON data, the data type must be consistent across table rows or the index creation will fail. Further, once one or more JSON indexes have been created, any attempt to write data of an incorrect type will fail.
With the exception of the previous restriction, indexing JSON data and working with JSON indexes behaves in much the same way as indexing non-JSON data. To create the index, specify a path to the JSON field using dot notation. You must also specify the data's type, using the AS keyword.
The following examples are built on the examples shown in Working with 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-> You can then run a query in the normal way, and the index idx_json_income will be automatically used. But as shown at the beginning of this chapter (Basic Indexing), the query processor will not know which index to use. To require the use of a particular index provide an index hint as normal:
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-> Finally, when creating a multi-key index on a JSON map, a type must not be given for the .keys() expression. This is because the type will always be String. However, a type declaration is required for the .values() expression:
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-> Be aware that all the other constraints that apply to a non-JSON multi-keyed index also apply to a JSON multi-keyed index.