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

Mongodb – Fetch data based on date in Mongo DB

I have data in collection named AuditReport as shown below.

{ 
    "_id" : ObjectId("642aa674759fdc00019ddac5"), 
    "Employee Name" : "a1", 
    "Checked in" : "06/20/2023 06:11 APM"
},
 { 
    "_id" : ObjectId("642aa674759fdc00019ddac5"), 
    "Employee  Name" : "a1", 
    "Checked in" : "04/03/2022 10:15 AM"
},
{ 
    "_id" : ObjectId("642aa674759fdc00019ddac5"), 
    "Employee Name" : "a1", 
    "Checked in" : "06/20/2023 08:12 AM"
},
{ 
    "_id" : ObjectId("642aa674759fdc00019ddac5"), 
    "Employee Name" : "a2", 
    "Checked in" : "06/20/2021 09:19 AM"
}

and so on..

I need to fetch data of Employee(a1) on checked in date (06/20/2023).

db.getCollection("AuditReport").find(
    { 
        "Employee Name" : "a1", 
        "Checked in" : { 
            "$eq" : "06/20/2023"
        }
    }
);

But its not giving the desired output.

Tags:

2

Answers


  1. 0

    The issue in your query is that you are using the wrong field for comparison. Instead of comparing the "Employee Name" field with the date "06/20/2023", you should be comparing the "Checked in" field with the desired date.

    Here’s the corrected query to fetch the data of Employee "a1" on the checked-in date "06/20/2023":

    db.getCollection("AuditReport").find({
"Employee Name": "a1",
"Checked in": "06/20/2023"});

    This query will match the documents where the "Employee Name" field is "a1" and the "Checked in" field is "06/20/2023". Please note that the date format should match exactly as it appears in the collection.

    Make sure you have the correct field names and the date format is consistent across all documents in the collection.

    Login or Signup to reply.
  2. 0

    From my point of view, not recommended to store the Date as a string. As you will find difficulties when query the document by a certain date, or date range. Even if it is possible to convert the Date string to Date, you will find the query will be slower.

    Approach 1: Since the Checked in field starts with this format: "MM/dd/yyyy", you can use regex expression to filter.

    db.collection.find({
  "Employee Name": "a1",
  "Checked in": {
    "$regex": "^06/20/2023"
  }
})

    Demo Approach 1 @ Mongo Playground

    Approach 2: Convert the date string into a Date and filter with the date range. Note that this requires aggregation operator, thus you need the $expr operator.

    db.collection.find({
  $expr: {
    $and: [
      {
        $eq: [
          {
            $getField: "Employee Name"
          },
          "a1"
        ]
      },
      {
        $gte: [
          {
            $toDate: {
              $getField: "Checked in"
            }
          },
          new Date("2023-06-20T00:00:00Z")
        ]
      },
      {
        $lte: [
          {
            $toDate: {
              $getField: "Checked in"
            }
          },
          new Date("2023-06-21T00:00:00Z")
        ]
      }
    ]
  }
})

    Demo Approach 2 @ Mongo Playground

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

Back To Top
Search