CODEWITHSUNDEEP
mongodbaggregation
Raphael
Raphael

Reputation: 119

Date range not working in aggregation pipeline, but works in find()

I am trying to filter data by a date range. Example return the data that was created no more than 14 days ago.

I can do this in find with the following:

{
    $match: {
        eventTime: { $gte: startTime.toDate(), $lte: endTime.toDate() }
    }
}

eventTime is an ISO date as well as startTime and endTime

I am using an aggregation and a lookup and trying to implement the same thing:

{
            $lookup:
            {
                from: "data",
                let: { dataId: "$dataId", patientId: "$patientId" },
                pipeline: [
                    {
                        $match:
                        {
                            $expr:
                            {
                                $and:
                                    [
                                        { $eq: ["$patientId", patientId] },
                                        { $eq: ["$dataId", "$$dataId"] },
                                        { $gte: ["$eventTime", startTime.toDate()] },
                                        { $lte: ["$eventTime", endTime.toDate()] },
                                    ]
                            }
                        }
                    }
                ],
                as: "data"
            }
        }

But no data results are returned. If I remove the dates I get all the correct data based on dataId and patient. so the join is working.. but somehow the date range is not.

Again both the eventTime and startTime and endTime are all ISO dates. example : 

let endTime = Moment(new Date());
let startTime = Moment().subtract(days, "days");

"eventTime": "2019-08-07T03:37:40.738Z"

 startTime  "2019-07-30T00:02:11.611Z"
 endTime  "2019-08-13T00:02:11.610Z"

End time is 'today' 
so in the example here the data time is between the two dates and should be returned.

I looked there : https://docs.mongodb.com/manual/reference/operator/aggregation/gte/ and it should work.. but not the case

I tried:

{eventTime: { '$gte': new Date(startTime), $lte: new Date(endTime)}}
and I get:
MongoError: An object representing an expression must have exactly one field: { $gte: new Date(1564495211043), $lte: new Date(1565704811042) }

also tried:

{ eventTime: {'$gte': new Date(startTime)}}

and get:

MongoError: Expression $gte takes exactly 2 arguments. 1 were passed in.

also tried:

{ $eventTime: {'$gte': new Date(startTime)}}, {$eventTime: {'$lte': new Date(endTime)}}

and get: MongoError: Unrecognized expression '$eventTime'

Any insight would certainly be appreciated

Upvotes: 2

Views: 1206

Answers (1)

Raphael
Raphael

Reputation: 119

I was able to get it working via toDate:

{
                        $match:
                        {
                            $expr:
                            {
                                $and:
                                    [
                                        { $eq: ["$patientId", patientId] },
                                        { $eq: ["$dataId", "iraeOverallAlert"] },
                                        { "$gte": [ {$toDate: "$eventTime"}, startTime.toDate()] },
                                        { "$lte": [ {$toDate: "$eventTime"}, endTime.toDate()] },

                                    ]
                            }
                        }
                    },

Note: This was not needed in the find, but somehow was needed using aggregation. Makes no sense but yah for trial and error.

Upvotes: 3

Related Questions