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.

As always, function-based indexing is appropriate for queries that target particular functions, which in the context of SQL/JSON functions means particular SQL/JSON path expressions. This indexing is not very helpful for queries that are ad hoc, that is, arbitrary. Define a function-based index if you know that you will often query a particular path expression.

Regardless of the SQL data type you use to store JSON data, you can use a B-tree or bitmap function-based index for SQL/JSON function json_value queries. Such an index targets a single scalar JSON value. A bitmap index can be appropriate wherever the number of possible values for the function is small. For example, you can use a bitmap index for json_value if the values targeted are expected to be few.

For JSON data that is stored as JSON 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 (especially) as elements of a JSON array.

Although a multivalue index can index a single scalar value, if you expect a path expression to target such a value then it is more performant to use a B-tree or bitmap index. Use a multivalue index especially to index a path expression that you expect to target an array of scalar values.

SQL/JSON path expressions that contain filter expressions can be used in queries that pick up a function-based index. But a path expression that you use to define a function-based index cannot contain filter expressions.

If you query in an ad hoc manner then define a JSON search index. This is a general index, not targeted to any specific path expression. It is appropriate for structural queries, such as looking for a JSON field with a particular value, and for full-text queries using Oracle SQL condition json_textcontains, such as looking for a particular word among various string values.

You can of course define both function-based indexes and a JSON search index for the same JSON column.

A JSON search index is an Oracle Text (full-text) index designed specifically for use with JSON data.

Static dictionary views DBA_JSON_INDEXES, ALL_JSON_INDEXES, and USER_JSON_INDEXES describe all indexes on JSON data in the database, all of them that are accessible by the current user, and all of them that are owned by the current user, respectively.

For composite indexes, static dictionary views DBA_TABLE_VIRTUAL_COLUMNS, ALL_TABLE_VIRTUAL_COLUMNS, and USER_TABLE_VIRTUAL_COLUMNS provide information about virtual columns that are created automatically for indexing. They supplement the *_JSON_INDEXES views.