[SERVER-17853] Allow more complex expressions to be specified in partial index document filter Created: 01/Apr/15 Updated: 06/Apr/23 |
|
| Status: | Backlog |
| Project: | Core Server |
| Component/s: | Index Maintenance, Querying |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | J Rassi | Assignee: | Backlog - Query Optimization |
| Resolution: | Unresolved | Votes: | 39 |
| Labels: | storch | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||||||||||
| Assigned Teams: |
Query Optimization
|
||||||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||||||
| Case: | (copied to CRM) | ||||||||||||||||||||||||||||
| Description |
|
The 3.1.1 version of partial indexes supports document filters composed using the following query operators:
Users should be able to create partial indexes with more complex expressions for the document filter. We should consider allowing the following:
Support for $in and $regex is being tracked in |
| Comments |
| Comment by Asya Kamsky [ 22/Jun/21 ] | |||||||||||||||
|
As I mentioned in the forums, the partialFilterExpression applies to the document (to decide whether the document will be indexed). It does not apply to which array elements will be indexed, so I don't believe that having more complex partial filter expression would help you, at least with current semantics. | |||||||||||||||
| Comment by Vishal Turi [ 22/Jun/21 ] | |||||||||||||||
|
I have a similar issue with Unique index + Partial filter expression $type: string in an array of object property, and also Added in MongoDB Developer Forum as well, Inserted Record:
Created Index:
Insert New document with a null value:
It Throws Error: >> E11000 duplicate key error collection: sample.collection index: members.userId_1 dup key: { members.userId: null } | |||||||||||||||
| Comment by Asya Kamsky [ 03/Dec/20 ] | |||||||||||||||
|
fowiflowi@gmail.com That would only be possible if all non-deleted documents have the field set to true, but if when creating documents you set isDeleted:false then you can use a partial index with isDeleted:false right now. | |||||||||||||||
| Comment by Nikolay Symotiyk [ 23/Nov/20 ] | |||||||||||||||
|
Here is another case with soft delete: I would like to create a unique index only for documents that are not deleted (e. g. isDeleted: { $ne: true }) | |||||||||||||||
| Comment by Marc Knaup [ 15/Sep/20 ] | |||||||||||||||
|
We'd also love to use $ne because $exists: true includes null values which we can exclude by using $ne: null. | |||||||||||||||
| Comment by Asya Kamsky [ 26/Jun/20 ] | |||||||||||||||
|
srperetz have you considered two indexes, one on a and b with filter b exists true and the second on a and c with filter c exists true? Without knowing your full use case it’s hard to be sure but it seems like that should work, no? If the queries are always on three fields then the two indexes would be one on a, b, c and the other on a, c, b. | |||||||||||||||
| Comment by Anas Mansouri [ 16/Jun/20 ] | |||||||||||||||
|
Any update on this ? | |||||||||||||||
| Comment by Mike Zraly [ 05/Sep/18 ] | |||||||||||||||
|
FYI the use case mentioned by Sam Peretz comes up naturally when we are sharding a collection and add the shard key to a sparse compound index to keep covered queries covered. Since the shard key is always present the index will no longer be sparse. Since the original index was compound and $or is not supported in partialFilterExpressions there seems to be no way to recover sparseness using a partial index. | |||||||||||||||
| Comment by Sam Peretz [ 05/Sep/18 ] | |||||||||||||||
|
Our use case for $or is where we have a large percentage of documents that are missing certain fields, and we want an index with a filter expression like:
where a is a field that is always present in all documents, but b and c are fields that are frequently missing. Does anyone have any suggested workaround for this use case? | |||||||||||||||
| Comment by Jeffrey Johnson [ 28/Mar/16 ] | |||||||||||||||
|
I would agree that $ne and $not operators can be useful. My use case is where over 99% of the documents in a collection have the same value for a certain field. We want to query on any value that is not the common value for that field.
Adding $in support would probably work for the use case as long as the non-common values are limited, but it would entail the downside of dropping and rebuilding the index whenever a new value is stored. Edit: added last example statement with $ne comparison | |||||||||||||||
| Comment by João França [ 02/Mar/16 ] | |||||||||||||||
|
The $ne and $not operators can also be useful. | |||||||||||||||
| Comment by J Rassi [ 09/Jun/15 ] | |||||||||||||||
|
I split out $in and $regex support into |