MongoDB! query to get data from specific date of different format

Question

I am new in mongoDB, please help me for below question description:

I have collection xyz having one of column insertDate having date of dd/mm/YYYY format. I want all records from xyz collection whose insertDate is greater than 28/01/2022.

I try below query and it return correct data but return only insertDate column but i want whole json record with above condition:

db.xyz.aggregate([{ $project: { insertDate: { $dateFromString: { format: '%d/%m/%Y', dateString: '$insertDate' } } } }, { $match: { insertDate: { '$gt': new Date("2022-01-01") } } } ]);

Please help me in this problem.

Storing date as string is generally considered as anti-pattern. Nevertheless, have you checked out $dateFromString? — ray
– ray, Commented Jun 28, 2022 at 11:45
@ray Yes i use that one also. Can u provide me full query for that? — ankit
– ankit, Commented Jun 28, 2022 at 11:47
Can you share your current attempt, what is the error you are having, and some sample documents? — ray
– ray, Commented Jun 28, 2022 at 11:48
@ray I use below command also: db.claims.aggregate([{ $addFields: { insertDate: { $dateToString: { format: '%Y-%m-%d', date: '$insertDate' } } } }, { $match: { insertDate: { '$gt': new Date("2022-01-01") } } } ]); — ankit
– ankit, Commented Jun 28, 2022 at 11:49
Please don't post code in comments. Instead, update your questions with the new info. — ray
– ray, Commented Jun 28, 2022 at 11:57

Kevin Smith · Accepted Answer · 2022-06-28 11:58:14Z

1

You'll need to convert your date in your database from a string to a date, and then filter it. You can do this with the aggregation pipeline.

⚠️ Warning: This can become very inefficient if you're working with large data as it requires a whole collection scan to covert each date so you won't be able to utilize indexes.

Let's start with some data:

db.test.insertMany([
   { insertDate: "02/05/2021" },
   { insertDate: "02/05/2019" },
   { insertDate: "02/05/2023" },
   { insertDate: "02/05/2022" },
   { insertDate: "02/05/2025" },
]);

Once we've inserted these documents we can query it using an aggregation query:

db.test.aggregate([
    {
        "$addFields": {
            "insertDate": {
                "$dateFromString": {
                    "dateString": "$insertDate",
                    "format": "%d/%m/%Y"
                }
            }
        }
    },
    {
        "$match": {
            "insertDate": {
                "$gt": ISODate("2022-01-28T00:00:00Z")
            }
        }
    }
])

Then we'll get the following results:

{ "_id" : ObjectId("62baec6a6fd118e327dc7ded"), "insertDate" : ISODate("2023-05-02T00:00:00Z") }
{ "_id" : ObjectId("62baec6a6fd118e327dc7dee"), "insertDate" : ISODate("2022-05-02T00:00:00Z") }
{ "_id" : ObjectId("62baec6a6fd118e327dc7def"), "insertDate" : ISODate("2025-05-02T00:00:00Z") }

For more information research the $dateFromString function and also the aggregation pipeline

answered Jun 28, 2022 at 11:58

Kevin Smith

14.5k5 gold badges63 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ankit Over a year ago

Thanks for answer but i want to return whole json record not only insertDate column.

Kevin Smith Over a year ago

@ankit This will return the whole record and not project any columns.

ankit Over a year ago

ok got it thnx kevin

ankit · Accepted Answer · 2022-06-28 12:07:49Z

0

Below query help me:

db.xyz.aggregate([{ $project: { insertDate: { $dateFromString: { format: '%d/%m/%Y', dateString: '$insertDate' } },claimNo: 1 } }, { $match: { insertDate: { '$gt': new Date("2022-01-01") } } } ]);

Where it returns:

{
"_id" : ObjectId("61d25493fc1f7b20de3c970e"),
"claimNo" : "03CL01",
"insertDate" : ISODate("2022-01-03T05:30:00.000+05:30")}

Thanks to https://stackoverflow.com/a/72786002/6097074

answered Jun 28, 2022 at 12:07

ankit

2,8853 gold badges32 silver badges58 bronze badges

Collectives™ on Stack Overflow

MongoDB! query to get data from specific date of different format

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related