CODEWITHSUNDEEP
mongodbisodate
Sameer
Sameer

Reputation: 3183

How is regex filter applied to date (isodate) type field in mongodb

I was trying to apply a regex filter(year) to a date type field but the query returned no results. So was trying to understand the behavior of this query?

db.collection.find({"ReportedDate":/2016/i})

Note: I can filter it by date type without regex. But I am just trying to understand the behavior of the regex filter against date type field?

Upvotes: 4

Views: 8480

Answers (3)

Dheemanth Bhat
Dheemanth Bhat

Reputation: 4452

From MongoDB version >= 4.4 we can write custom filters using $function operator so try this:

let yearRegex = /2016/i;

db.collection.find({
    $expr: {
        $function: {
            body: function(reportedDate, yearRegex) {
                return yearRegex.test(reportedDate);
            },
            args: [{ $toString: "$ReportedDate" }, yearRegex],
            lang: "js"
        }
    }
});

Note: Instead of $toString we can also use $dateToString with timezone to cover edge cases.

Upvotes: 2

VIKAS KOHLI
VIKAS KOHLI

Reputation: 8440

I did the same thing but instead of find method, I use aggregation pipeline

db.collection.aggregate(
    [
        {
            $project: {
                "d":  { $dateToString: {format: "%Y-%m-%d", date: "$mydate"}},key:1
            }
        }  ,
        {
            $match: {
                d: /2012-/i
            }
        }
    ]
    )

By this I am able to apply the regex pattern in ISO Date too.

Upvotes: 0

Wan B.
Wan B.

Reputation: 18835

As mentioned, the $regex query operator provides regular expression capabilities for pattern matching strings in queries.

An alternative to using regex filter to query the year value, you can utilise MongoDB Aggregation Pipeline to project the year portion of a date object using $year aggregation operator. For example: 

db.collection.aggregate([
     {"$addFields": {"year": {"$year":"$ReportedDate" } } }, 
     {"$match": {"year": 2016 } }
], {cursor:{batchSize:10}})

There are other useful date related operators such as: $dayOfYear, $dayOfWeek, $dayOfMonth, etc.

Upvotes: 0

Related Questions