Indexing JSON Collection Tables

You can index the fields in a JSON collection table by specifying the name of the indexed element and ANYATOMIC for the type definition. For strongly typed indexes, you can specify the JSON type of the fields being indexed. Strongly typed indexes are useful when you want to ensure that a JSON attribute is of the expected type during inserts and updates of JSON data. In contrast, the ANYATOMIC option is appropriate when you are uncertain about the exact atomic type that may be present in the attribute.

create index myindex on PersonsJsonColl(age as ANYATOMIC);

The statement above creates an untyped index on the age field. This index, for example, would allow the database to perform high-speed filtering for queries like 'WHERE age > 20' or 'WHERE age = 30' without having to scan every JSON document in the table.

If the element you want to index is deeply nested in a JSON object, you must specify the complete path expression to the field as follows:

create index idx_income_cty on storeAcct (income as ANYATOMIC, address.city as ANYATOMIC);

The statement above creates a composite index using top-level income field and a nested city field. This index, for example, would optimize queries looking for high-income earners in a specific location.

For more details, see Working with JSON Collection Tables.