Cosmos DB filtering documents on date range is not working as expected

Question

We are using ADF to filter the documents on start and end datetimestamps and copy the doucments from cosmos db(mongo api) to ADLS. When we review the documents copied in JSON dumps, the filter is not working as expected. For instance, we have used start date as "2021-12-30 02:00:00.000" and enddate as "2022-1-6 02:00:00.000", the results also have douments that has logTimestamp fields values equals to 2022-1-21 0:45:58.605. It means it also includes the values 2022-1-2X 2022-1-3X etc in the selection criteria which is not desired.

sample values of logTimestamp that are part of result dataset

    "source": {
        "type": "MongoDbV2Source",
        "batchSize": 100,
        "filter": "{\n    \"LogTimestamp\": \n    { \n        \"$gte\": \"2021-12-30 02:00:00.000\", \n        \"$lt\": \"2022-1-6 2:0:0.000\",\n    } \n}"
    },

Answer 1

Hi there,

Looks like your query has an extra comma after the less than date which is not necessary and also, please try something similar to below using ISO function for date conversion and see if that helps.

db.collection.find({
  "LogTimestamp": {
    "$gte": ISODate("2021-01-13T00:00:00.000Z"),
    "$lt": ISODate("2023-01-14T00:00:00.000Z")
  }
})

Another example called out in this document: https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/tutorial-aggregation#example-with-multiple-stages

db.sales.aggregate([
  { $match: { date: { $gte: "2021-01-01", $lt: "2021-03-01" } } },
  { $group: { _id: "$category", totalSales: { $sum: "$sales" } } },
  { $sort: { totalSales: -1 } },
  { $limit: 5 }
])

Hope this info helps.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.