MongoDB: Find items where two specified key values are equal

Question

I have a mongoDB collection and an item in the collection looks like below:

{
"_id": "52f535b56268a019bd11cc2a",
"description": "Some description",
"entry_date": "2014-02-07T19:36:21.430Z",
"last_update": "2014-02-07T19:36:21.430Z",
"r": "samestring",
"s": "samestring"
}

Dates are ISODate objects.

This query returns items correctly

db.myCollection.find({$where : "this.entry_date < this.last_update"});

Below query returns nothing (I expect that it returns the above item):

db.myCollection.find({$where : "this.entry_date == this.last_update"});

And this query returns all items (I expected again it returns the above item):

 db.myCollection.find({$where :"this.r == this.s"});

What am I doing wrong? Thanks!!

---EDIT----

So I tried to test with a small data like below:

> db.myCollection.find({},{ _id: 0, start_date: 1, end_date: 1});

{ 
   "start_date" : ISODate("2014-02-07T19:36:21.430Z"),
   "end_date" : ISODate("2014-02-07T19:36:21.430Z") 
}
{  
   "start_date" : ISODate("2014-02-07T19:36:21.430Z"),
   "end_date" : ISODate("2014-02-07T22:39:02.114Z")
}

It didn't work for Date as you can see:

> db.myCollection.find(
    {$where: "Date(this.start_date) == Date(this.end_date)"},
    { _id: 0, start_date: 1, end_date: 1 }
  );



{
    "start_date" : ISODate("2014-02-07T19:36:21.430Z"),
    "end_date" : ISODate("2014-02-07T19:36:21.430Z") 
}
{
    "start_date" : ISODate("2014-02-07T19:36:21.430Z"),
    "end_date" : ISODate("2014-02-07T22:39:02.114Z")
}

Works for string values:

> db.myCollection.find({$where: "this.title == this.description"},{ _id: 0, title: 1 });

{ "title" : "samedescription" }

I tried your queries with that document and those last two that weren't working for you worked fine for me. — JohnnyHK, Feb 08 '14 at 02:34
Realize that when you use `$where` all documented in the collection must be scanned. It's very inefficient. If you can store in the document precomuted results and index them, you'll have much better performance overall. — WiredPrairie, Feb 08 '14 at 13:59
@WiredPrairie Let's say I create a field "precomuted = start_date - end_date" again I will need to use $where to find this field's value equals to zero, to find the items where start_date = end_date. Can you explain your suggestion on this example? Thanks — user3211198, Feb 08 '14 at 20:18

score 3 · Accepted Answer · answered Feb 08 '14 at 05:29

You have to be really careful when comparing dates in JavaScript - use valueOf() or getTime():

> db.myCollection.find({$where: "this.start_date.getTime() == this.end_date.getTime()"});

{ "_id" : ObjectId("52f5b7e316d795f0a076fbdf"), "description" : "a description", "title" : "a title", "start_date" : ISODate("2014-02-07T19:36:21.430Z"), "end_date" : ISODate("2014-02-07T19:36:21.430Z") }

Here is why your other queries didn't work.

db.myCollection.find({$where: "Date(this.start_date) == Date(this.end_date)"});

This didn't work because you didn't use new when initializing the dates. This generally has hilarious results with all dates being equal to each other:

> Date(2014,2,8) == Date(1941,12,7)
true
> Date(2000,1,1) == Date(1995,2,8)
true

But even if you properly instantiate the date using new, you still get hilarious results when comparing dates using ==, as demonstrated in this gist:

var dateValue = 504001800000; // Saturday, December 21st, 1985 at 3:30am EST
var date1 = new Date(dateValue);
var date2 = new Date(dateValue);

console.log(date1 == date2);  // false (different instances)
console.log(date1 === date2); // false (different instances)
console.log(date1 > date2);   // false (date1 is not later than date2)
console.log(date1 < date2);   // false (date1 is not earlier than date2)
console.log(date1 >= date2);  // true (rofl)
console.log(date1 <= date2);  // true (ahahahaha)

As for your other query:

It didn't work if I consider them as strings either:

db.myCollection.find({$where: "this.start_date == this.end_date"});

You're not actually comparing them as strings, you're comparing ISODate objects, which is how they're stored. For ISODate, similar to Date, the == operator will return false unless you're comparing the exact same instance. Using getTime should work, however, which is what I did up above.

Hopefully, none of this makes any sense whatsoever, because if it does, I'm worried about your sanity.

Neil Lunn · Answer 2 · 2014-02-08T05:42:18.057

0

--EDITED--

You were looking for the $where operator. Your query must be in a valid JSON notation and outside of this operator there is no other access to that kind of raw JavaScript notation.

Dates

finally:

{$where: "this.start_date.valueOf() == this.end_date.valueOf()"}

Also be careful not to run into reserved words and other traps.

Be very careful to read the documentation on this operator and make sure you absolutely need it. It will slow things down considerably as your find will scan the entire collection. It cannot use an index.

edited Feb 08 '14 at 05:42

answered Feb 08 '14 at 01:11

Neil Lunn

148,042
36
346
317

Thanks Neil I edited my question. I have actually used $where operator too. It doesn't change the result. I can try to explain in a different way if you didn't understand my problem. – user3211198 Feb 08 '14 at 02:12
@user3211198 Your Date comparison is self apparent as shown in my own edit. There is no reason for strings not to match unless they really are not equal. – Neil Lunn Feb 08 '14 at 02:42
I tried with Date and didnt work maybe there something wrong with my data or code. Can I use this: http://stackoverflow.com/a/6399470/3211198 with dates? and query for field3 value = 0 but I guess $where will still be required isn't it? – user3211198 Feb 08 '14 at 03:46
@user3211198 You can use exactly what I typed in the answer above. Failing that, I know you assert that you have ISODate objects but if your included document sample is an exact dump from the mongo shell then they are in fact strings. There is no other answer other than what I have provided. If you are not getting what you expect then the problem is your data. – Neil Lunn Feb 08 '14 at 03:51
@Neil_Lunn Can you see second edit. Somehow your suggestion doesn't work for Date. Any idea? Thanks – user3211198 Feb 08 '14 at 04:46
@user3211198 My bad. Shouldn't cargo cult and should actually research it myself. Different handling again. – Neil Lunn Feb 08 '14 at 05:43
Thanks again this worked, below explanation is very helpful too. – user3211198 Feb 08 '14 at 05:54

MongoDB: Find items where two specified key values are equal

2 Answers2

Dates