Same-field `$or` to `$in` rewrite
Parent: Compound index vs index union for $or · Published reference · snapshot 2026-10-24 · skill mongodb-cohort-68aac318a142
↓ Facts as markdownall context files
Source-anchored research for Same-field `$or` to `$in` rewrite. Run status: COMPLETED.
These notes link each claim to its source. Some citations identify archived research reports that are no longer hosted on this site. A published reference means the content is available; it does not certify independent review or accuracy.Read the editorial policy and follow the sources before relying on a claim.
Structure and components
- Measure the actual query and index combination with explain before claiming a performance improvement. The 2022 community discussion reports little visible difference for small value lists and provides a benchmark for larger lists, but its two scripts use different data and indexes, and its $in list contains {_id:i} documents while the inserted _id is scalar; its timings are not a controlled comparison of equivalent predicates. (low confidence) [source]
How it works
- For an array-valued field, $in matches when at least one array element matches a listed value; requiring all listed values is a different predicate, expressed with $all. (high confidence) [source]
- Before asserting a workload-specific speedup, compare equivalent filters with `explain` in `executionStats` verbosity. Inspect the winning plan, `totalKeysExamined`, and `totalDocsExamined`; treat `executionTimeMillis` cautiously because MongoDB says this value is not necessarily representative of actual query time. (medium confidence) [source]
Problems, failure modes and limitations
- The rewrite requires equality alternatives. A same-field disjunction of ranges, such as (a>10 AND a<20) OR (a>30 AND a<50), cannot be represented by putting those range predicates into an $in values list. (high confidence) [source]
- The 2022 community benchmark is not a controlled equivalence test: its $in code builds object-valued terms {_id:i} and queries scalar _id using those objects, while its $or code uses scalar equality clauses; the $in setup also creates an extra compound index and adds a and b predicates. Its timing ratios cannot isolate the rewrite's effect. (low confidence) [source]
- A historical mixed equality/regex $or in an aggregation $match could fail during automatic conversion with Location3401204, 'Incorrect number of non-equivalent expressions'. SERVER-66379 records fixes in 5.0.10, 6.0.0-rc9, and 6.1.0-rc0; this scopes an old failure and does not establish a current defect. (low confidence) (verified as of 2023-10-29) [source]
Comparisons and alternatives
- Query $in accepts regex literals, but it does not accept $regex operator expressions as array entries; regex rewrites therefore need attention to syntax rather than mechanically moving complete operator documents into the list. (low confidence) [source]
- An index on the queried field lets MongoDB construct bounds for individual $in elements. This can express multiple equality alternatives as bounds within one index scan rather than separately planned OR branches; an appropriate compound index can also support shared predicates. (medium confidence) [source]
- Use a single $in predicate instead of repeated JavaScript object keys such as {status:"paid",status:"shipped"}; the later key replaces the earlier key, so that object does not express an OR. (medium confidence) [source]
Facts and statements
- For equality alternatives on exactly the same field, rewrite {$or:[{a:1},{a:2}]} as {a:{$in:[1,2]}}. MongoDB explicitly recommends this form; the selected documents are the same. (high confidence) [source]
- An index on the queried field lets MongoDB build bounds for the $in values; the rewrite does not itself create an index or guarantee an indexed winning plan. (medium confidence) [source]
- Shared predicates can remain outside the rewritten membership test: {createdAt:{$gte:t},$or:[{type:'purchase'},{type:'refund'}]} becomes {createdAt:{$gte:t},type:{$in:['purchase','refund']}}. This preserves both the date filter and the type alternatives. (medium confidence) [source]
- Do not promise a fixed speedup or a distinct scan for every written $or branch. SERVER-62353 documents existing pipeline optimization from same-path equality $or to $in, including partial grouping of same-path alternatives. Its description concerns particular optimizer phases and does not establish identical plans for every release and query context. (medium confidence) [source]
- Identical predicates shared by every branch can remain outside the rewritten disjunction: {$or:[{a:1,b:7},{a:2,b:7}]} is equivalent to {a:{$in:[1,2]},b:7}. This is a logical factoring rule; preserve any predicates that differ between branches. (medium confidence) [source]
Children
- No children recorded.