JSON Search Index Not Used Together with Vector Index When Both Exist

[TheKoguryo]

Using a filter like the following, you can perform a search while filtering on metadata:

filter_dict = {"category": search_category}
vector_store.similarity_search(user_question, 8, filter=filter_dict)

Internally, this results in a JSON_EXISTS predicate being added to the WHERE clause, as shown below:

SELECT text, metadata,
       vector_distance(embedding, :embedding, COSINE) AS distance
FROM "ORAVS_DOCUMENTS"
WHERE JSON_EXISTS(
        metadata,
        '$.category?(@ == $val)'
        PASSING :value0 AS "val"
      )
ORDER BY distance
FETCH APPROX FIRST 8 ROWS ONLY;

For performance reasons, a JSON Search Index is typically created using the CREATE SEARCH INDEX statement.

With this query:

• If no JSON Search Index exists, the optimizer uses the Vector Index.
• If a JSON Search Index exists, only the JSON Search Index is used, and the Vector Index is not applied.

Therefore, it is necessary to add a hint to the query, such as:

SELECT /*+ VECTOR_INDEX_TRANSFORM(ORAVS_DOCUMENTS) */
       text, metadata,
       ...

to ensure the desired execution plan.

[paxiaatucsdedu]

@sudarshan12s would you like to take a look at this?

[sudarshan12s]

Thanks @TheKoguryo. I see a PR is raised for the same. I will keep a note to add the same for JS too.