8

The latest version of MongoDB (v3.2) adds support for partial (filtered) indexes. You supply a filter when you create the index and that filter determines which documents will be referenced in the index and which will not.

Can I use any filter expression (as long as it's a valid filter)? Or are there limitations to the filter being used? If so, what are those limitations?

i3arnon
  • 113,022
  • 33
  • 324
  • 344

1 Answers1

19

Can I use any filter expression?

No, partial indexes support only a subset of the operators in the filter used. The only supported operators are: $AND (only at the top level), $EQ, $LT, $LTE, $GT, $GTE, $EXISTS and the TYPE_OPERATOR.

That leaves out, for example $NOT, $REGEX, $OR, etc.

You can see that in the source for MongoDB here.

What are those limitations?

There are also some general limitations on partial indexes:

  • _id indexes can't be partial indexes.
  • Sparse indexes can't be partial indexes.
  • Shard key indexes can't be partial indexes.
  • Partial indexes aren't supported in versions earlier than 3.2.
i3arnon
  • 113,022
  • 33
  • 324
  • 344
  • It doesn't appear that this can be accomplished from the C# driver. At least I don't see PartialIndexFilter as a property of the CreateIndexOptions class – Ralph Shillington Oct 17 '16 at 16:15
  • @RalphShillington How about [`PartialFilterExpression`](http://api.mongodb.com/csharp/current/html/P_MongoDB_Driver_CreateIndexOptions_1_PartialFilterExpression.htm)? – i3arnon Oct 17 '16 at 16:58
  • 1
    @i3amon You're absolutely right! My error was in doing var options = new CreateIndexOptions(); without setting the generic type. – Ralph Shillington Oct 17 '16 at 19:07
  • Is it possible to make a second `_id` index that would be partial (with a different name) ? (I'm trying to make some sort of default filter view which would automatically exclude blacklisted content) – Cyril Duchon-Doris Feb 28 '19 at 14:06
  • It's also worth mentioning that only `{ $exists: true }` is supported, because `{ $exists: false }` is translated internally to an expression which uses the `$not` operator, which itself is not supported. – Valeriu Paloş Mar 06 '23 at 06:22
  • As a workaround, instead of `{ $exists: false }` I think one can use `{ $eq: null }` which evaluates true when the field has the value `null` *or* if the field does not exist. Not identical behaviour, but close enough to build a partial index correctly. – Valeriu Paloş Mar 06 '23 at 07:21