MongoDB partialFilterExpression with document field comparisson

user_367285 0
Viewed 1180

I want to define a MongoDB partialFilterExpression which is based on the comparison of two document fields. Specifically, it is about comparing two date fields. The index should only be effective if one date (here processingTime) is larger than the other date (here creationTime).

I tried it the following way but that does not seem to work (because the document are not removend via the TTL index condition).

db.
  getSiblingDB('foo').
  getCollection('bar').
  createIndex(
    { creationTime: 1 },
    {
      expireAfterSeconds: 60,
      partialFilterExpression: {
        processingTime: { $exists: true },
        processingTime: {$gt: "$creationTime"}
      }
    }
  );

I guess that $creationTime is not evaluated as a document field but only as simple text.

Is there a way to reach my goal?

mongodb
1 Answers

Try storing a field in each document that contains the difference between the two date fields (e.g. processing_creation_time_diff) and then setting the partialFilterExpression on that value. For example: 

db.getSiblingDB('foo').
  getCollection('bar').
  createIndex(
    { creationTime: 1 },
    {
      expireAfterSeconds: 60,
      partialFilterExpression: { 
        processing_creation_time_diff : { "$gt": 0 } // processing is greater than creation
      }
    }
  )

You are correct that the index specification as currently defined is evaluating $creationTime as a string. I threw together a quick example demonstrating that the filter expression is comparing processingTime against the string '$creationTime': 

db.bar.insert({ "x" : "$before", "y" : 1 })
db.bar.insert({ "x" : "$compare_field", "y" : 1 })
db.bar.insert({ "x" : "$this_returns", "y" : 1  })

db.getCollection('bar').createIndex(
  { y: 1 },
  {
    partialFilterExpression: {
      x: {$gt: "$compare_field"}
    }
  }
);

db.bar.find({ "x" : { "$gt" : "$compare_field" }, "y" : 1 }).explain("executionStats")

// Returns only one doc using the partial index:
{ "_id" : ObjectId("5c342c9f1ed401160b81011d"), "x" : "$this_returns", "y" : 1 }

The partial filter expression is evaluated as x is greater than the string "$compare_field", which matches the document with the value $this_returns.

Referencing a field using '$fieldname' is typically used in aggregations. Outside of aggregations you would need to use the $expr operator which is not an operator that can be used for a partialFilterExpression.

user_10927670
Related