Creating Indexes for JSON Collections

You can create indexes for JSON collections in the JSON page.

See Also:

Indexes for JSON Data in Oracle Database JSON Developer's Guide

Open the Indexes Pane

In the JSON left pane, right-click the collection, and select Indexes.

The Indexes pane lists the existing indexes for the collection. Select the index row to display more information.

Description of the illustration index_main.png

The icons at the top are Add JSON Index, Edit JSON Index, and Delete JSON Index.

The properties of the selected index appear in JSON format below the listed indexes. Select JSON from the TABLE - JSON option to view all indexes in JSON presentation.

Create an Index

1. Click the + New JSON Index icon. The New Index pane appears.

2. Enter the following fields to create an index:

   
   Description of the illustration create_index.png

   • Name: Enter a name for the index.

   • Type: Select the index type from the drop-down list. The different options are Functional, Spatial and Search. Based on the index type selected, the corresponding options appear.

   • For a functional type index, the fields to enter are:

     • Unique: Select this option to make all indexed values unique.

     • Index Nulls: Select this option to use the index in Order By queries.

     • Path Required: Select this option if the path must select a scalar value, even a JSON null value.

     • Properties: Type the property that you want to index on, or Type * to display all available document properties in the collection. To select a property, select the checkbox in the respective row.

       Note:

       You cannot index properties in arrays.

     • Composite Index: Select this option if you want to use more than one property.

     • Advanced: Select this option to change the storage properties of the indexed property. For each property, you can change the type (varchar2, number, date or timestamp), maximum length for indexing (for character properties), and sort order.

   • For search index, the options are:

     • Dataguide off-on: Select on to create JSON data guide for collection.

     • Text Search off-on: Select on to index all properties in documents to support full-text search based on string equality (every property is treated as string)

     • Range Search off-on: Select on to support range search when string-range search or temporal search (equality or range) is required.

   • Spatial index is used to index GeoJSON geographic data. The selected property should be of GeoJSON type. See Using GeoJSON Geographic Data

     For spatial index, the options are:

     • Path Required: Select this option if the path must select a value, even if it is a JSON null value.

     • Lax: Select this option if the targeted field does not need to be present or does not have a GeoJSON geometry object as its value.

       Note:

       You cannot enable Path Required and Lax at the same time.

3. Click Create. A notification is displayed indicating that the index is created and the Indexes pane is populated.