MongoDB Aggregation Pipeline: how to compare string to date inside array

Question

I have a collection situations with documents in this structure:

{
    "_id" : “32AzuAkJ8PXEgSkcuFII0YSeVUl1",
    "situations" : [
        {
            "datetime" : "2018-11-28T21:21:49.013Z"
        },
        {
            "datetime" : "2018-11-29T15:17:50.913Z"
        },
        {
            "datetime" : "2018-11-30T22:50:01.684Z"
        },
    ],
    "user" : [
        {
            "email" : “some@email.com”,
            "userType" : “new”
        }
    ]
}

I need to filter out documents which have at least one situation between 14 days and 7 days ago AND at least one situation between 7 days ago and now. And it has to happen through an Aggregation Pipeline, because I will need to perform other operations later.

First I tried this pipeline:

{
    $match: {
        $and: [
            {
                $expr: {
                    $and: [
                        { "$situations.datetime": { $gt: new Date((new Date().getTime() - (7 * 24 * 60 * 60 * 1000))) } },
                        { "$situations.datetime": { $lte: new Date((new Date().getTime())) } }
                    ]
                }
            },
            {
                $expr: {
                    $and: [
                        { "$situations.datetime": { $gt: new Date((new Date().getTime() - (14 * 24 * 60 * 60 * 1000))) } },
                        { "$situations.datetime": { $lte: new Date((new Date().getTime() - (7 * 24 * 60 * 60 * 1000))) } }
                    ]
                }
            }
        ]
    }
}

But it didn't work. I think because my datetime is a String. Then I was thinking about using $dateFromString somehow, but I didn't know how to use it within the situations array.

score 0 · Accepted Answer · answered Dec 29 '19 at 16:36

As you've noticed you cannot compare dates against strings stored in your database - MongoDB will never return any results from such query. The easier way in your case is to convert in-memory dates to string using .toJSON() function (tested in Mongo shell - you can build same string from any programming language).

Other issue I see here is that you're trying to apply two pairs of conditions so you need to use $elemMatch. Otherwise you may have 4 different dates and each of them will be matching one of your conditions whereas you're trying to find a situation between two dates.

Your query might look like below:

db.col.aggregate([
    {
        $match: {
            $and: [
                { 
                    "situations": { 
                        $elemMatch: { 
                            datetime: { 
                                $gt: new Date((new Date().getTime() - (7 * 24 * 60 * 60 * 1000))).toJSON(),
                                $lte: new Date((new Date().getTime())).toJSON()
                            }
                        } 
                    } 
                },
                { 
                    "situations": { 
                        $elemMatch: { 
                            datetime: { 
                                $gt: new Date((new Date().getTime() - (14 * 24 * 60 * 60 * 1000))).toJSON(),
                                $lte: new Date((new Date().getTime() - (7 * 24 * 60 * 60 * 1000))).toJSON()
                            }
                        } 
                    } 
                }
            ]
        }
    }
])

MongoDB Aggregation Pipeline: how to compare string to date inside array

1 Answers1