Can strict JSON $dates be used in a MongoDB query?

Question

I'm trying to write a date comparison query using MongoDB's strict JSON representation of BSON.

I'd like it to work in the MongoDB shell (v2.4.3)

Here's what I've tried...

Setup: create a new document with an at date of Jan 1, 2020

> db.myTimes.insert({"at": new Date("2020-01-01")})

Using non-strict query for date > 2010, no problem:

> db.myTimes.find({"at": {"$gt": new Date("2010-01-01")}})
{ "_id" : ObjectId([snipped]), "at" : ISODate("2020-01-01T00:00:00Z") }

Using strict JSON query, however... NO DICE

> db.myTimes.find({"at": {"$gt": {"$date":"2010-01-01T00:00:00Z"}}})
> db.myTimes.find({"at": {"$gt": {"$date":"2010-01-01"}}})
> db.myTimes.find({"at": {"$gt": {"$date": 1262304000000}}})
> db.myTimes.find({"at": {"$lte": {"$date": 1262304000000}}})

(As you can see, I tried ISO8601 dates, epoch times, and also changing my $gt to $lte on the theory that these would be mutually exclusive, so one of them should return something :-)

Thanks for any pointers!

-B

Answer 1

I am not sure but everything suggests that it is impossible to build valid query using strict JSON. Although you can run query combining $date with $gt, $gte, $lt, $lte it seems, like in your case, to be always evaluated as false.

When you combine $date with $ne or $nin it will match every document in collection so I think it confirms previous observation.

What is more important when you try to get exact match like this db.foo.find({at: {"$date":"2010-01-01T00:00:00Z"}}) you will get invalid operator error (10068).

My guess is When try to create document in Mongo shell using $date

doc = {at: {"$date":"2010-01-01T00:00:00Z"}}

it is not evaluated as date and there is no way to insert document like this into collection. As you see it seems that strict JSON is parsed correctly only by tools like mongoimport. There is similar question here: Is there a way to run MongoDB shell (or tojson method) in strict JSON mode?.

Answer 2

The mongo shell doesn't support Strict JSON mode. See this ticket: https://jira.mongodb.org/browse/SERVER-6813.

This library here may do what you are looking for (although I haven't tried it): https://www.npmjs.com/package/mongodb-extended-json. I don't use the mongo shell for anything complex so I don't know if or how you could get this working in the shell.

As a side note, if you are willing to use a different language, I'm using the following code for pymongo.

import bson.json_util
mongo_queryD = bson.json_util.loads(mongo_query_str)
db.collection.find(mongo_queryD)

Unfortunately, this will only work for the last two examples with the timestamp in int64 format.