30 Indexes for JSON Data

You can index scalar values in your JSON data using function-based indexes. In addition, you can define a JSON search index, which is useful for both ad hoc structural queries and full-text queries.

• Overview of Indexing JSON Data
  You can index particular scalar values within your JSON data using function-based indexes. You can index JSON data in a general way using a JSON search index, for ad hoc structural queries and full-text queries.
• How To Tell Whether a Function-Based Index for JSON Data Is Picked Up
  Whether or not a particular index is picked up for a given query is determined by the optimizer. To determine whether a given query picks up a given function-based index, look for the index name in the execution plan for the query.
• Creating Bitmap Indexes for JSON_VALUE
  You can create a bitmap index for SQL/JSON function json_value. A bitmap index can be appropriate whenever your queries target only a small set of JSON values.
• Creating B-Tree Indexes for JSON_VALUE
  You can create a B-tree function-based index for SQL/JSON function json_value. You can use the standard syntax for this, explicitly specifying json_value, or you can use dot-notation syntax with an item method. Indexes created in either of these ways can be used with both dot-notation queries and json_value queries.
• Using a JSON_VALUE Function-Based Index with JSON_TABLE Queries
  An index created using json_value with ERROR ON ERROR can be used for a query involving json_table. In this case the index acts as a constraint on the indexed path, to ensure that only one (non-null) scalar JSON value is projected for each item in the JSON data.
• Using a JSON_VALUE Function-Based Index with JSON_EXISTS Queries
  An index created using SQL/JSON function json_value with ERROR ON ERROR can be used for a query involving SQL/JSON condition json_exists.
• Data Type Considerations for JSON_VALUE Indexing and Querying
  For a function-based index created using SQL/JSON function json_value to be picked up for a given query, the data type returned by json_value in the query must match the type specified in the index.
• Creating Multivalue Function-Based Indexes for JSON_EXISTS
  For JSON data that is stored as JSON data type you can use a multivalue function-based index for SQL/JSON condition json_exists. Such an index targets scalar JSON values, either individually or within a JSON array.
• Using a Multivalue Function-Based Index
  A json_exists query in a WHERE clause can pick up a multivalue function-based index if (and only if) the data that it targets matches the scalar types specified in the index.
• Indexing Multiple JSON Fields Using a Composite B-Tree Index
  To index multiple fields of a JSON object you can create a composite B-tree index using multiple path expressions with SQL/JSON function json_value or dot-notation syntax.
• JSON Search Index for Ad Hoc Queries and Full-Text Search
  A JSON search index is a general index. It can improve the performance of both (1) ad hoc structural queries, that is, queries that you might not anticipate or use regularly, and (2) full-text search. It is an Oracle Text index that is designed specifically for use with JSON data.