CODEWITHSUNDEEP
mongodbindexing
Rodrigo Sasaki
Rodrigo Sasaki

Reputation: 7226

How do I create a Partial Index with an $in in the filter expression?

I'm basically trying to create a unique partial filter in my collection with a filter that I know works, but MongoDB doesn't accept it.

Here is my statement:

db.payments.createIndex(
  {
      "userId": 1,
      "accountId": 1,
      "status": 1
  },
  {
      unique: true,
      partialFilterExpression: {
          status: { 
              $in: ["new", "paid", "excluded"]
          }
      }
)

And I get this error message:

2021-04-20 18:53:42] com.mongodb.MongoCommandException: Command failed with error 67 (CannotCreateIndex): 'Error in specification { key: { userId: 1, accountId: 1, status: 1 }, name: "userId_1_accountId_1_status_1", unique: true, partialFilterExpression: { status: { $in: [ "new", "paid", "excluded" ] } }, v: 2 } :: caused by :: unsupported expression in partial index: status $in [ "new" "paid" "excluded" ]

If I try to filter it by a single status it works, e.g.

{
    partialFilterExpression: {
        status: { 
            $eq: "new" 
        }
    }
}

However, if I want to have multiple statuses allowed on the index it doesn't work. I couldn't find anything in the documentation stating that an $in expression is not supported. So how can I get this to work?

Upvotes: 2

Views: 4973

Answers (1)

Joe
Joe

Reputation: 28356

$in is not allowed in a partial filter expression.

From the docs:

The partialFilterExpression option accepts a document that specifies the filter condition using:

  • equality expressions (i.e. field: value or using the $eq operator),
  • $exists: true expression,
  • $gt, $gte, $lt, $lte expressions,
  • $type expressions,
  • $and operator at the top-level only

Upvotes: 3

Related Questions