CODEWITHSUNDEEP
mongodbaggregation-framework
Georges
Georges

Reputation: 81

Formulas in MongoDB views

I am trying to create a MongoDB view which aggregates data inserted in the last 365 days. In my $match stage, before data is massaged, I used the following formula:

{
   $match: _created: { $gte: new Date((new Date()).getTime() - (365 * 24 * 60 * 60 * 1000)) }
}

This seemed to work at first but then I noticed that the formula is evaluated prior to creation and the view ends up as

{
   $match: _created: { $gte: ISODate('2019-10-17T21:51:10.000Z') }
}

which, of course, will not be updated as time passes.

I am using MongoDB Community Server v4.4.1; is there a way to insert the formula itself in the view, not its static result?

Thanks in advance for any helpful comments.

Upvotes: 0

Views: 170

Answers (1)

prasad_
prasad_

Reputation: 14317

The following aggregation pipeline will work with date query to find data within the last year. This pipeline can be used with a view.

[ { 
  $match: { 
      $expr: { 
          $gte: [ "$created", { $subtract: [ "$$NOW", 31536000000 ] } ] 
      } 
  } 
} ]

The $$NOW is the agregation system variable and its value will be set to the date at the time of running the query on the view.

The value 31536000000 is the (365 * 24 * 60 * 60 * 1000).

[EDIT ADD]

The code I had tried from the mongo shell (assuming the collection name is coll with the date field created).

// The aggregation pipeline
> var pipeline = [ { 
    $match: { 
        $expr: { 
            $gte: [ "$created", { $subtract: [ "$$NOW", 31536000000 ] } ] 
        } 
    } 
  } ]

// Create view, using the above pipeline
> db.createView(
    "coll_year_view",
    "coll",
    pipeline
)

// Query the view
> db.coll_year_view.find()

Upvotes: 1

Related Questions