Question 117436 in Mongodb Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    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


  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search