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

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.

CodePudding user response：

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

CodePudding user response：

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