MongoDb "Unknown operator $and" error

[Vladislav Gospodinov]

Hi,

Firstly I want to apologize if there is a similar topic out there.

I'm using mongo 3.4.1 and I'm getting some strange error when i try to "find" with query.

{  
    "$and":[  
        {  
            "chiffreaffaires":{  
                "$not":{  
                    "$and":[  
                        {  
                            "chiffreaffaires":{  
                                "$gt":100.0
                            }
                        },
                        {  
                            "chiffreaffaires":{  
                                "$lt":300.0
                            }
                        }
                    ]
                }
            }
        },
        {  
            "$or":[  
                {  
                    "commercial":{  
                        "$eq":"comm1"
                    }
                },
                {  
                    "commercial":{  
                        "$eq":"l'comm2\n es\"t là"
                    }
                }
            ]
        }
    ]
}

The "or" part is working correctly when executed alone.

The problem is in not>and part, but I can't see anything syntactically wrong with it.

Tried it with "nin" but i can't have any $ in them ($lt , $gt)

Thanks,

Vlaislav

[Ivan Grigolon]

Hi,

You can try below query instead to achieve the same result that you're expecting:

I created some sample docs:

> db.aaa.find()
{ "_id" : ObjectId("58bcb42f528d1c47b2b59a52"), "chiffreaffaires" : 100, "commercial" : "comm1" }
{ "_id" : ObjectId("58bcb436528d1c47b2b59a53"), "chiffreaffaires" : 1000, "commercial" : "comm3" }
{ "_id" : ObjectId("58bcb43b528d1c47b2b59a54"), "chiffreaffaires" : 150, "commercial" : "comm1" }
{ "_id" : ObjectId("58bcb43f528d1c47b2b59a55"), "chiffreaffaires" : 150, "commercial" : "comm2" }
{ "_id" : ObjectId("58bcbad2528d1c47b2b59a56"), "chiffreaffaires" : 80, "commercial" : "comm1" }
{ "_id" : ObjectId("58bcbad7528d1c47b2b59a57"), "chiffreaffaires" : 320, "commercial" : "comm1" }

​

The following query will achieve the same output:

db.aaa.find({  
    "$and":[  
        {  

                    "$or":[  
                        {  
                            "chiffreaffaires":{  
                                "$lt":100.0
                            }
                        },
                        {  
                            "chiffreaffaires":{  
                                "$gt":300.0
                            }
                        }
                    ]
        },
        {  
            "$or":[  
                {  
                    "commercial":{  
                        "$eq":"comm1"
                    }
                },
                {  
                    "commercial":{  
                        "$eq":"l'comm2\n es\"t là"
                    }
                }
            ]
        }
    ]
})
{ "_id" : ObjectId("58bcbad2528d1c47b2b59a56"), "chiffreaffaires" : 80, "commercial" : "comm1" }
{ "_id" : ObjectId("58bcbad7528d1c47b2b59a57"), "chiffreaffaires" : 320, "commercial" : "comm1" }

​

Best Regards,
Ivan

[compchap.nikhil]

Hi Vlaislav

Ivan's solution is apt. However, we could also try without $and as it is implicit query operator.

Thanks,

Nikhil