3

I am centralizing data on my Atlas Cluster from bare-metal servers running community MongoDB databases locally. However I have an issue with my ISODates() attributes that are converted to "$date" dictionaries, after which the sort() query still works but the "$lt" and "$gt" queries are failing.

Attribute in local db

"createdTime": ISODate("2023-01-20T11:20:50.268Z")

Attribute in the Atlas Cluster'db :

 "createdTime" : { '$date': '2023-01-20T11:20:50.268Z' }

In my Atlas Cluster's collection, I am still able to sort on the time attribute :

db.MyCollection.find().sort({createdTime:-1}).map(x => x.createdTime)
[
  { '$date': '2023-01-20T11:20:50.268Z' },
  { '$date': '2023-01-20T11:20:26.587Z' },
  { '$date': '2023-01-20T11:20:04.108Z' },
]

But when I try to use the operators "$gt" and "$lt" then mongo doesn't know what to do :

db.MyCollection.countDocuments({ createdTime: { $lt: ISODate("2023-02-01T00:00:00.000Z")} })
0
// even with "$date" as a key
db.MyCollection.countDocuments({ "createdTime.$date": { $lt: ISODate("2023-02-01T00:00:00.000Z")}})
0

Eventually, I figured I could make it work with a Javascript function to convert the "$date" objects back to ISODate, but I want to be able to use the "$lt" and "$gt" in my Python API, relying on pymongo.

Pymongo code :

res = db["MyCollection"].count_documents({"createdTime": {"$lt": datetime.now() }})
# res = 0

Any idea how I could solve what I assume is a date formatting issue?

Barthoche
  • 31
  • 5
  • Same issue here and also for filtering, it doesn't work either – A. Attia Feb 01 '23 at 09:55
  • 1
    What python code have you tried? Can you add this to your question. – Belly Buster Feb 01 '23 at 10:05
  • 1
    At a guess I would say that the way you are adding data to Atlas is incorrect. If you run a `db.MyCollection.findOne()` on Atlas it should give the same output as your local example, e.g. `createdTime: ISODate("2023-01-20T11:20:50.268Z")`. – Belly Buster Feb 01 '23 at 10:32
  • 4
    Also generally where you see `'$date'` ,`'$oid'` etc is from the output of a BSON to JSON type converter, e.g. `json.dumps()` or mongoexport. You don't want to store JSON in mongo you need to retain the BSON types. – Belly Buster Feb 01 '23 at 11:02
  • Would you show the code you used to create the Atlas collection? – rickhg12hs Feb 01 '23 at 12:08
  • `ISODate("2023-01-20T11:20:50.268Z")` is the same as `{ '$date': '2023-01-20T11:20:50.268Z' }`, it is just a different display format. `ISODate` is used by mongo shell `mongosh` and `$date` is the [MongoDB Extended JSON](https://www.mongodb.com/docs/manual/reference/mongodb-extended-json/#mongodb-bsontype-Date) format. – Wernfried Domscheit Feb 01 '23 at 14:15
  • @WernfriedDomscheit I wonder if through the method of creating the Atlas collection, `'$date'` is actually a literal rather than Extended JSON. – rickhg12hs Feb 01 '23 at 23:04

1 Answers1

1

Firstly I don't understand why the date is inside another object with a private field (starting with a $ sign). You can directly store the IDODate as you have mentioned in your question.

"createdTime": ISODate("2023-01-20T11:20:50.268Z")

without putting it inside an object.

If you did not do it in purpose, this could be a migration issue between when using mongoexport with a datetime filter using the --query option. This behaviour is documented here.

Your first query:

db.MyCollection.countDocuments({ createdTime: { $lt: ISODate("2023-02-01T00:00:00.000Z")} })

returns nothing, because createdTime is an object.

Your second query:

db.MyCollection.countDocuments({ "createdTime.$date": { $lt: ISODate("2023-02-01T00:00:00.000Z")}})

returns nothing because createdTime.$date cannot be directly be accessed in the query. Fields with $ prefixed are not openly accesible in Mongo. What you have to do to make this work is to use an expression with a getField, that would enable you to access this.

For example, this query should give you the expected result.

db.MyCollection.countDocuments({$expr: {$lt: [{$getField: {$literal: "createdTime.$date"}}, ISODate("2023-02-01T00:00:00.000Z")]}})

But a better solution would be to keep createTime itself as an ISODate.

toing_toing
  • 2,334
  • 1
  • 37
  • 79
  • 1
    JSON doesn't have a date type, so in strict mode, mongodb uses `{'$date': ...}` to encapsulate the ISODate. – Joe Feb 01 '23 at 15:18