[SERVER-1089] $and operator Created: 05/May/10 Updated: 12/Jul/16 Resolved: 03/Jun/11 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | None |
| Fix Version/s: | 1.9.1 |
| Type: | New Feature | Priority: | Major - P3 |
| Reporter: | Guillaume Bodi | Assignee: | Aaron Staple |
| Resolution: | Done | Votes: | 59 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||
| Participants: | |||||||||||||||||||||
| Description |
|
In addition to the $or operator (http://jira.mongodb.org/browse/SERVER-205), I feel there is a need for a $and operator. $and: [ { <condition1> }, { <condition2> }] Why it is needed: Example: A document sample would be , , { name: 'john', #the user corrected the initial input timestamp: 20100504120100 }] Now, when I want to query for a session with an user named john, I would need to use $elemMatch, since I can't match the full element because of the variable timestamp, like below: find({ Now, I want to look for john doe. But since I can't repeat the same $elemMatch key in the data condition object, I am stuck. find({ , } Possible solution with $and operator: find({ |
| Comments |
| Comment by Antoine Girbal [ 22/Jun/11 ] |
|
it could also be useful to be able to do ranges in the $in query. , { "$gte" :012}]}}) |
| Comment by auto [ 03/Jun/11 ] |
|
Author: {u'login': u'astaple', u'name': u'Aaron', u'email': u'aaron@10gen.com'}Message: |
| Comment by Aaron Staple [ 03/Jun/11 ] |
|
Hi Folks, Thanks for letting us know of your interest in nesting $or within $and. Support for nesting $or within $and is a nontrivial task, and it requires that we implement I've filed Thanks, |
| Comment by Andy Jefferson [ 03/Jun/11 ] |
|
+1 for nested $or/$and combinations. |
| Comment by Ed Rooth [ 03/Jun/11 ] |
|
+1 for Mikael Nousiainen's comment. |
| Comment by rgpublic [ 03/Jun/11 ] |
|
I second this. It's certainly a step forward that we have $and now. I don't know why exactly others are voting and watching this issue but for our company the underlying idea was to port applications with flexible web-based search interfaces to MongoDB. This means search interfaces where the users themselves can create logical AND/OR queries by drag&drop. Thus, we don't know in advance how a query will look like. Without $and we would for instance need to combine different RegEx queries of the same key into one single RegEx etc. A very difficult task - if not impossible. So I had put the project on hold until this was resolved and we were waiting for $and. Now that $and-support is there I wonder what we can actually do with it. If I'm not mistaken, we could now get $and support, but would lose $or support, right? Sigh. One more round of waiting... Should perhaps anyone file another issue on this? Even if $and doesnt seem to help very much at the moment, I'd like to take the chance and say thank you for you work so far. It's much appreciated. |
| Comment by Mikael Nousiainen [ 03/Jun/11 ] |
|
Will support for nesting $or inside $and be added later, possibly before 2.0 release? Not allowing nested $or makes $and quite useless for most queries. |
| Comment by auto [ 02/Jun/11 ] |
|
Author: {u'login': u'astaple', u'name': u'Aaron', u'email': u'aaron@10gen.com'}Message: |
| Comment by auto [ 02/Jun/11 ] |
|
Author: {u'login': u'astaple', u'name': u'Aaron', u'email': u'aaron@10gen.com'}Message: |
| Comment by auto [ 02/Jun/11 ] |
|
Author: {u'login': u'astaple', u'name': u'Aaron', u'email': u'aaron@10gen.com'}Message: |
| Comment by auto [ 02/Jun/11 ] |
|
Author: {u'login': u'astaple', u'name': u'Aaron', u'email': u'aaron@10gen.com'}Message: |
| Comment by Eliot Horowitz (Inactive) [ 28/Apr/11 ] |
|
Sorry - your're 2nd one is correct. > db.foo.find( { $or : [ { x : 1 }, { y : 1 } ] } ) , { y : 1 }] } ) |
| Comment by John Crenshaw [ 08/Mar/11 ] |
|
The lack of both $and and nested $or is really ugly. You don't notice it early on, and then it bites you pretty hard when it's too late to turn back. I should point out though, that both $and and nested $or can be implemented client side fairly trivially (as in, it takes just a couple of hours). If you have any abstraction layer at all, you can easily run the query through a rewrite filter before sending it out. The filter is a piece of cake to write because of the simplicity of the syntax. All you have to do is recursively check the query for $and, $or, and $nor. You let the first $or/$nor through, any others get rewritten with $where (remember to json_encode or you will have just introduced a code injection vulnerability.) $and is more complicated, but can generally be rewritten using $all on any duplicate keys. |
| Comment by Flavien [ 18/Feb/11 ] |
|
Agreed, both $and and nested $or are a must. |
| Comment by Kareem Kouddous [ 14/Feb/11 ] |
|
+1 for both the $and operator and nested $or. This would dramatically simplify our queries given that we have a flexible query UI that we expose to our users (think of the Zendesk search page as a good example). |
| Comment by Jamie Orchard-Hays [ 08/Feb/11 ] |
|
I've also run into a need for this. After trying a large number of unsuccessful permutations, I want $and. |
| Comment by Fred Stluka [ 07/Feb/11 ] |
|
+1 for $and operator and for nested $or. Same use case as others: I have now had to implement a generic mechanism for converting: Fortunately, I was able to rely on the fact that we currently need only one A generic $and and nestable $or native to Mongo would be a great addition! Thanks! |
| Comment by Rakeesh Ranjan [ 07/Feb/11 ] |
|
+1 for the $and operator Here is my use case: http://groups.google.com/group/mongodb-user/browse_frm/thread/a94ceeb6227929e8 Thanks |
| Comment by Mikael Nousiainen [ 29/Dec/10 ] |
|
I couldn't agree more with Ed. My use case is similar, as I create arbitrarily complex queries on large collections programmatically (for searching based on user-defined criteria). Transforming them to another, possibly a lot slower, form would not make any sense, because query execution speed is critical. |
| Comment by Ed Rooth [ 27/Dec/10 ] |
|
Would just like to add my 2 cents and experience. I have been trying to implement basic searching functionality into my app. Simple cases are fine: However the insufficiency of no $and operator becomes quickly apparent when trying to do queries where you have many variables and they are dependent upon each other. Building the query becomes insanely complex. ie writing the following logic in a mongo query: A && B && C && D || (E && F) || (G && H) || (G && I) || (F && G && H) || ((F && G && H) || X) ... You end up having to copy all the top-level ANDs and duplicate them with each OR block then project out all the different permutations into one giant OR statement. So Implementing even mildly complex searching without the $and operator has been extremely cumbersome. I would think that searching through data based on business logic is a popular use case, but currently there is no practical solution. So in terms of querying, I think this is the one crucial missing feature that needs to be addressed. I really hope this gets implemented soon so I can refactor all the messy code I had to write to get around this limitation. All that said... aside from the searching, Mongo has been great so far. |
| Comment by Aaron Staple [ 15/Sep/10 ] |
|
I believe the $elemMatch example above can be done with the current code as follows: > db.f.drop() , { key: 'lastname', value: 'doe', timestamp: 20100505120000 }, { key: 'name', value: 'john', timestamp: 20100504120100 } ] }); }, { $elemMatch: { key: 'lastname', value: 'doe' } } ] } } ); , , { "key" : "name", "value" : "john", "timestamp" : 20100504120100 }] } }, { $elemMatch: { key: 'lastname', value: 'doerr' } } ] } } ); |
| Comment by Mikael Nousiainen [ 18/Aug/10 ] |
|
Can you offer any kind of workaround for the issue I've described in the URL I posted above? I just need to get rid of the top-level AND operation. I've tried to do other kind of transformations such as modifying and to $or: (A && B) -> !(!A || !B), ] } } does not work. I'm really desperate with this as we need this kind of functionality in a production system. |
| Comment by Eliot Horowitz (Inactive) [ 17/Aug/10 ] |
|
1.7.x means its likely to be included in 1.7/1.8 There is very little chance of it happening for 1.7.0 as there is already a lot of work planned for it. Seems likely it'll happen in 1.7. For features like this priority is decided heavily by # of votes. |
| Comment by Mikael Nousiainen [ 17/Aug/10 ] |
|
I have yet another use case for $and operator: Yes, the same can be achieved with several transformations, but they make the query unnecessarily complex. In my opinion basic $and, $or and $not should all be explicit operators to allow easy construction of any kind of query. Could you please comment on the schedule of this feature (what does 1.7.x actually mean here) and |
| Comment by Brian Link [ 19/Jul/10 ] |
|
Just popping in to add my support for $and. My use case is the same as the OP. I need to combine multiple $elemMatch conditions and the PHP driver is unable to do this without some mechanism to avoid the problem of duplicate keys. $and would solve this. |
| Comment by Andrey Mikhaylenko [ 12/Jul/10 ] |
|
A related use case: http://groups.google.com/group/mongodb-user/browse_thread/thread/e427fe8a2a940eb4 I'll copy the last example from there: >>> db.test.save( {"x": "hello world"}) ) ) I need the "hello mongo" document instead of [] in the last query. Unfortunately $all does not work with $regex (and maybe any nested operators except for the exact match) as of MongoDB version 1.5.3. UPD: looks like I should have had a good cup of coffee before posting the example above. Actually the correct regular expressions would be "hello" and "mongo", and yes, MongoDB handles them as expected as of 1.5.4. instead of re.compile('foo') with pymongo and this approach failed indeed. |
| Comment by Arek Turlewicz [ 24/May/10 ] |
|
Solution for this problem is to use $all, $in, $nin operators If we want simple AND t = Thing.all("name"=> {"$all"=>["/foo/","/bar/"]}) !B AND !C <=> !(B OR C) t = Thing.all("name"=> {"$nin"=>["/foo/","/bar/"]}) I hope this helps. |
| Comment by Eliot Horowitz (Inactive) [ 11/May/10 ] |
|
$and can be usefor for some cases like this |
| Comment by Guillaume Bodi [ 05/May/10 ] |
|
Sorry, I tried to overly simplify my model and introduced a simple solution. My actual document structure is { , , { key: 'name', value: 'john', timestamp: 20100504120100 } ] I organized this key, value structure so that I could index the array of objects for performance. In this case, a find query for john doe would turn into find({ , } which only matches the last condition. If indexing the array would guarantee performance for querying with any possible 'key' in either design (could you confirm this please?), I would go with the first model and my specific problem would indeed be solved. However, even beyond my specific modelization (which might very well not be optimal), it is impossible to have a query object involving the same key twice. So I'd like to know more about the rationale behind the 'should never need an and' if possible. |
| Comment by Eliot Horowitz (Inactive) [ 05/May/10 ] |
|
You should never need an and find( { "data.name" : "john" , "data.lastname" : "doe" } ) if not - please explain |