MongoDb - add index on 'calculated' fields

Vegar_11956 0
Viewed 683

I have a query that includes an $expr-operator with a $cond in it. Basically, I want to have objects with a timestamp from a certain year. If the timestamp is not set, I'll use the creation date instead. 

{
  $expr: {
    $eq: [
      {
        $cond: {
          'if': {
            TimeStamp: {
              $type: 'null'
            }
          },
          then: {
            $year: '$Created'
          },
          'else': {
            $year: '$TimeStamp'
          }
        }
      },
      <wanted-year>
    ]
  }
}

It would be nice to have this query using a index. But is it possible to do so? Should I just add index to both TimeStamp and Created-fields? Or is it possible to create an index for a Year-field that doesn't really exist on the document itself...?

mongodb
2 Answers

Not possible

Indexes are stored on disk before executing the query.

Workaround: On-Demand Materialized Views

You store in separate collection your calculated data (with indexes)

user_37104900

This can't be done today without precomputing that information and storing it in a field on the document. The closest alternative would probably be to use MongoDB 4.2's aggregation pipeline-powered updates to precompute and store a createdOrTimestamp field whenever your documents are updated. You could then create an index on createdOrTimestamp that would be used when querying for documents that match a certain year.

What this would look like when updating or after inserting your document:

db.collection.update({ _id: ObjectId("5e8523e7ea740b14fb16b5c3") }, [
    {
        $set: {
            createdOrTimestamp: {
                $cond: {
                    if: {$gt: ['$TimeStamp', null]},
                    then: '$TimeStamp',
                    else: '$Created'
                }
            }
        }
    }
])

If documents already exist, you could also send off an updateMany operation with that aggregation to get that computed field into all your existing documents.

It would be really nice to be able to define computed fields declaratively on a collection just like indexes, so that they take care of keeping themselves up to date!

user_41556860
Related