CODEWITHSUNDEEP
mongodb
Vish
Vish

Reputation: 175

What's the difference between following two mongodb queries?

I ran following two queries and they returned different results.

// my query 1
> db.events.count({"startTimeUnix":{$lt:1533268800000},"startTimeUnix":{$gte:1533182400000}})
131
// existing app query 2
> db.events.count({"startTimeUnix":{"$lt":1533268800000,"$gte":1533182400000}})
0

The query 2 is already being used in the batch application but it reported to pulling less records which I confirmed from these queries.

//these counts are confusing
> db.events.count()
2781
> db.events.count({"startTimeUnix":{$lt:1533268800000}})
361
> db.events.count({"startTimeUnix":{$gte:1533182400000}})
2780

Upvotes: 1

Views: 52

Answers (3)

simagix
simagix

Reputation: 1912

Use the second query. You can add explain() to find out the query plans. The first query

db.events.count({"startTimeUnix":{$lt:1533268800000},"startTimeUnix":{$gte:1533182400000}})

is evaluated the same as 

db.events.explain().count({"startTimeUnix":{$gte:1533182400000}})

Use the command below to view the query plans.

db.events.explain().count({"startTimeUnix":{$lt:1533268800000},"startTimeUnix":{$gte:1533182400000}})

Upvotes: 2

Naya
Naya

Reputation: 870

The problem is that mongo doesn't parse operators if the are in quotes. db.events.count({"startTimeUnix":{"$lt":1533268800000,"$gte":1533182400000}}) means that it looks for the entries where startTimeUnix is an object and contains fields "$lt" and "$gte" If you'll the next command, this query starts returning 1: 

db.events.insert({"startTimeUnix":{"$lt":1533268800000,"$gte":1533182400000}})

Upvotes: 0

mickl
mickl

Reputation: 49945

query 2 is an impicit (and proper) way of building AND condition, query 1 is incorrect in terms of MongoDB syntax. The way it gets analyzed is pretty simple, MongoDB takes first condtion and then overrides it with second one so it has the same meaning as:

db.events.count({"startTimeUnix":{$gte:1533182400000}})

first condition simply gets ignored and that's why you're getting more results (described here)

Upvotes: 1

Related Questions