mongodb - Mongo Date Query issue

Question

I am trying to retrieve some data from a collection on Mongo database for the past month and it is supposed to be run every month so I want to have a query that can run first day of the month to generate a report based on the query. So I was trying to use $lt and $gte to limit the date range. The date need to be filtered is one value inside an array. For example: Here is the collection data with the collection name Test:

{
    "_id" : ObjectId("1234528f4a224cdb39aaa"),
    "Project" : "12345",
    "Department" : "Sales",
    "Summary" : "TEST",
    "FirstName" : "Mary",
    "LastName" : "White",
    "activities" : [ 
        {
            "Type" : "Trip",
            "dateOfActivity" : ISODate("2020-12-20T06:00:00.000Z")
        }, 
        {
            "Type" : "Conference",
            "dateOfActivity" : ISODate("2021-01-05T06:00:00.000Z"),
            "Note" : "test"
        }
    ]

So I would like to run a query to retrieve all the information with the dateofActivity for last 15 days. So I was using the following query:

db.Test.find( { 'activities.dateOfActivity': { $lt: new Date(), $gte :new Date(new Date().setDate(new Date().getDate()-15)) }})

I then noticed the returning records doesn't limit to all the activities in the past 15 days. I then tried to run the query :

db.Test.find( { 'activities.dateOfActivity': { $gte : ISODate("2021-02-04T06:00:00.000Z")}})

which doesn't limit the query records as well. The returning records contains some data from 12/27/2020. Where did I do wrong on the query?

Also what is the best way to just retrieve the result for the past month based on this dateofActivity field? For example, if I want to run this report first day of every month to retrieve the records for the last month?

question from:https://stackoverflow.com/questions/66055305/mongo-date-query-issue

Answer 1

There are several options for filtering the array in the response.

If you want only the first element that matches (which would work for that example document), use $elemMatch in a projection:

db.Test.find( {'activities.dateOfActivity': { $gte: date2, $lte: date1 }}, {activities: {$elemMatch:{dateOfActivity:{$gte: date2, $lte: date1})

If you need to get every matching elements, you would need to use aggregation.

Using unwind:

• Match to limit the rest of the pipeline to documents with at least one matching date
• Unwind the activities array
• Match only those activities in the date range

db.Test.aggregate([
   {$match: { 'activities.dateOfActivity':  { $gte: date2, $lte: date1 }}},
   {$unwind: "$activities"},
   {$match: { 'activities.dateOfActivity':  { $gte: date2, $lte: date1 }}}
])

Using filter:

• Match to select documents with a matching date
• Iterate each activities array with filter to eliminate non-matching elements

db.Test.aggregate([
   {$match: { "activities.dateOfActivity":  { $gte: date2, $lte: date1 }}},
   {$addFields: { 
         activities: {$filter: {
              input: "$activities",
              cond: {$and: [
                       {$gte: [ "$$this.dateOfActivity", date2]},
                       {$lte: [ "$$this.dateOfActivity", date1]}
              ]}
         }}
    }}
])