I'm trying to understand mongodb queries, but one of my queries is returning a record that it shouldn't.

I am trying to call

db.user.find({'referral.money':{'$ne':0, '$ne':null}})

to get all users who have some cents in their referral data. Some users have referral data which doesn't include "money" and others don't have any referral data at all.

The problem is that after executing that command, the following record is returned:

    "_id" : ObjectId("58362414af5d810d1c9ec7f0"),
    "user_id" : 22,
    "referral" : {
        "_cls" : "ReferralProgramA",
        "end_dtm" : ISODate("2016-12-23T23:19:48.014Z"),
        "money" : 0

If I'm correct it shouldn't be returned since "money" is obviously 0.

My collections and records are created with python's mongoengine hence the "_cls" fields for inheritance.

I'm making the find({}) call with a raw statement so mongoengine isn't messing it up( I think ).

| improve this question | | | | |

The problem is that your query object uses the same key ($ne) twice, so the second value is overwriting the first. Most languages do not support duplicate keys in a standard object / hash / dictionary representation.

The outcome is more evident if you evaluate your query object in the mongo shell:

> query = {'referral.money':{'$ne':0, '$ne':null}}
  "referral.money": {
    "$ne": null

However, you could use a different operator with equivalent outcome in an implicit $and.

For your example query, $exists: true is equivalent to $ne: null so the following would work:

 db.user.find({'referral.money':{'$ne':0, $exists: true}})

Alternatively you could rewrite the query to use an explicit $and:

 db.user.find({$and: [{'referral.money': {'$ne':0}}, {'referral.money':{'$ne':null}} ]})

Note: if you are storing currency values as floating points you should be wary of rounding with floating point numbers. The MongoDB manual has examples of how to Model Monetary Data.

| improve this answer | | | | |
  • I was unaware that the $ keys are unique. Thanks. Also I'm storing money as integer cents, thanks for the link. – jakedipity Nov 24 '16 at 21:04
  • @jthull To be clear the issue isn't with $ keys needing to be unique: this is a problem with common data representations. For example, doc = {"a":1, "a":2} ends up as the JavaScript object {"a":2 }. An explicit $and operator avoids this issue by describing each expression as a document in an array (rather than trying to combine multiple expressions with duplicate keys in the same document). Some MongoDB drivers (eg. C++, Java) do have objects that allow duplicate keys, which can be useful for working with internal data structures such as the replication oplog. – Stennie Nov 24 '16 at 23:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.