how to create a partial index with $exists: false condition?

Question

I want to create this partial index:

db.mycollection.createIndex( 
  {"firstname": 1, "lastname": 1}, 
  { partialFilterExpression: { "status": {$exists: false}, "quantity": { $lt: -1 } } } ); 

but i receive this error:

unsupported expression in partial index: $not\n status exists\n"

How can I do ?

Pathy

Answer 1

If the status field doesn't have null values, you can use

{ partialFilterExpression: { "status": null, "quantity": { $lt: -1 } } } ); 

Willy

Answer 2

Unfortunately, you don't.

That feature request has been around for quite a while: https://jira.mongodb.org/browse/SERVER-17853

Hopefully they get to it soon, that would be quite useful.

Tubule

Answer 3

I was researching this issue recently and after a bit of trying out different things I have come here to drop an answer with 3 year old delay:

YES, YOU CAN.

{ partialFilterExpression: { "status": { $in: [null] }, "quantity": { $lt: -1 } } } ); 

I will be honest, no idea why this works, but it very much works. You'd think this $in query matches only existing fields that have null value but $in: [null] for some reason will match the same as $exists: false and you can create a partial index with it.

---

@Edit: for example in my project those two queries will match exact same results despite technically null values not being the same as $exists: false, but for whatever reason when used alongside $in, they are. For context the field someParticularQuantity can only have positive values above 0, if the quantity is zero then the field just doesn't exists in the database.

the non-indexable (my old way of querying):

db.collection.find({
  $or: [
    {
      "someParticularQuantity": {
        $lt: 4,
      },
    },
    {
      "someParticularQuantity": {
        $exists: false,
      },
    },
  ],
})

completely indexable (the new method I found out):

db.collection.find({
  "someParticularQuantity": {   
      $in: [ null, 1, 2, 3 ],
    }
})

Berny