When querying a field that appears in all the JSON files in my database, the query is faster. When quering field that does not necessarily exist in every JSON file, the query is slower. Why is this happening?
Please check EXPLAIN. It might be using covering vs non-covering index.
Also depends on what type of predicate you are using.
Assume this related to Why OR logical operator affects the execution time in a diffirent way than AND operator? - #5 by iwanna_ster.
This query uses many ANY clauses, WITHIN and document structure is complex.
ANY or SOME is TRUE if the collection is non-empty and at least one element matches. WITHIN evaluates to TRUE if the right-hand-side value contains the left-hand-side value (or name and value) as a child or descendant (i.e. directly or indirectly).
Based on above information if the document field is present condition matched early it will end due to ANY. If no field is not present, not matched it need to continue till end with all childs.
due to recursive nature of WITHIN it may be few loops vs many more loops.