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

Mongodb – Mongo query to filter today data when date & time is in string in different keys

How can I write a mongo query to filter today’s record with time when I have date and time in string in json object in mongo.

My Json Object is

{
  "_id": ""
  "data_id": 3227,
  "series_id": 240,
  "date_wise": "17 Sep 2023, Sunday",
  "match_date": "17-Sep",
  "match_time": "01:30 PM",
}

I need to find out if the following date is $lt the current date/time by comparing the match_date and match_time key in the json object.

I have wrote query but its not working because I believe the json object date and time is in string.

db.posts.find({match_date: {$lt: new Date()},match_time: {$lt: new Date()}});

Any suggestions of great help!!

Tags:

2

Answers


  1. 0

    The challenge here is that your dates and times are stored as strings and not in a format that’s easily compared. To perform the query you’re aiming for, we’ll need to piece together the date and time strings into a single Date object for comparison.

    Here’s one approach to doing this:

    Use the $dateFromString operator to convert your string representation of date and time into a single Date object.

    Use the $expr operator to form a condition that can compare this newly created Date object with the current date and time.

    Here’s how you might write this query:

    db.posts.find({
    $expr: {
        $lt: [
            {
                $dateFromString: {
                    dateString: {
                        $concat: [
                            { $substr: ["$match_date", 0, 2] }, 
                            " ", 
                            { $substr: ["$match_date", 3, 3] }, 
                            " 2023 ", 
                            { $substr: ["$match_time", 0, 5] }, 
                            ":00"
                        ]
                    },
                    format: "%d %b %Y %H:%M:%S"
                }
            },
            new Date()
        ]
    }
});

    This query does the following:

    Concatenates the date and time strings into a format suitable for $dateFromString.

    Uses $dateFromString to convert this concatenated string into a Date object.
    Compares this Date object to the current date and time.

    Note: This approach assumes the year is 2023 for all records. Adjustments might be needed if your collection spans multiple years.

    Login or Signup to reply.
  2. 0

    One option is to use $dateFromString with a $expr:

    db.collection.find({
  $expr: {$lte: [
      {$dateFromString: {dateString: {$concat: ["$date_wise", " ", "$match_time"]}}},
      "$$NOW"
    ]}
})

    See how it works on the playground example

    But a better option is to add an ISODate to your document, which can be done like this:

    db.collection.updateMany(
  {},
  [{$set: {date: {$dateFromString: {
          dateString: {$concat: ["$date_wise", " ", "$match_time"]}
  }}}}],
)

    See how it works on the playground example

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

Back To Top
Search