[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:
Depends
depends on SERVER-958 Range queries on arrays behave differ... Closed
Related
related to SERVER-3192 allow nesting $or within $and Closed
is related to SERVER-3192 allow nesting $or within $and Closed
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.
A possible way to write it could be:

$and: [

{ <condition1> }

,

{ <condition2> }

]

Why it is needed:
Condition blocks are physically objects. Thus, we cannot have the same property used twice, preventing the use of the same construct/property multiple times in a query.

Example:
Suppose I have a collection of user sessions.
Each session can contain an arbitrary list of key-value pairs, possibly repeated (ie. an identical key can be listed several times in different objects of the list). Additionally, each key value pair has a timestamp, for history purpose.

A document sample would be
{
session_id: 1,
data: [

{ name: 'johm', timestamp: 20100504120000 }

,

{ lastname: 'doe', timestamp: 20100505120000 }

,

{ 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({
data: {$elemMatch: {name: 'john'}}
});

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.
The query below would run, but ignore the first condition, since it is being overwritten by the second.

find({
data: {
$elemMatch:

{name: 'john'}

,
$elemMatch:

{lastname: 'doe'}

}
});

Possible solution with $and operator:

find({
data: {
$and: [
{$elemMatch: {name: 'john'}},
{$elemMatch: {lastname: 'doe'}}
]
}
});



 Comments   
Comment by Antoine Girbal [ 22/Jun/11 ]

it could also be useful to be able to do ranges in the $in query.
This would be equivalent to a nested implicit $or on a single key.
Typically smthing like {prop: {$in: [1,2,3]}} gets converted into single value ranges to look at in index.
So one could write smthing like:
db.foo.find({"bar" : {$in: [

{ "$gte" : 012 , "$lt" : 014}

,

{ "$gte" :012}

]}})
Dont know if that is planned or if there would be complications, but would make that kind of query easy and efficient.

Comment by auto [ 03/Jun/11 ]

Author:

{u'login': u'astaple', u'name': u'Aaron', u'email': u'aaron@10gen.com'}

Message: SERVER-1089 add some checks for multiple where fields
Branch: master
https://github.com/mongodb/mongo/commit/36a3b7aed3470677944ebf75ee8d018ead897d72

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 SERVER-2585 first.

I've filed SERVER-3192 to specifically cover nesting $or within $and, so you can track the issue there.

Thanks,
Aaron

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.
Nested $or is crucial.

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: SERVER-1089 don't allow nesting or within and for now, as it will complicate query parsing for index bounds
Branch: master
https://github.com/mongodb/mongo/commit/69713ca560d81c127d915b6e7290ca24d99c6ef7

Comment by auto [ 02/Jun/11 ]

Author:

{u'login': u'astaple', u'name': u'Aaron', u'email': u'aaron@10gen.com'}

Message: SERVER-1089 tests for nested and/or cases
Branch: master
https://github.com/mongodb/mongo/commit/69ea6ab82248388168c0f747bcb60e1c502f8b75

Comment by auto [ 02/Jun/11 ]

Author:

{u'login': u'astaple', u'name': u'Aaron', u'email': u'aaron@10gen.com'}

Message: SERVER-1089 merge assert codes
Branch: master
https://github.com/mongodb/mongo/commit/c2f4f2c8da17af6f4699fe2e34d4405cf600bbea

Comment by auto [ 02/Jun/11 ]

Author:

{u'login': u'astaple', u'name': u'Aaron', u'email': u'aaron@10gen.com'}

Message: SERVER-1089 add and operator
Branch: master
https://github.com/mongodb/mongo/commit/635d2a680d46a09969a9616584a272c2cf395201

Comment by Eliot Horowitz (Inactive) [ 28/Apr/11 ]

Sorry - your're 2nd one is correct.
Same as $or

> db.foo.find( { $or : [

{ x : 1 }

,

{ y : 1 }

] } )
> db.foo.find( { $and : [

{ x : 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.
I created an issue about the nested $or: http://jira.mongodb.org/browse/SERVER-2585
Please come vote for it if you want it too.

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:
http://groups.google.com/group/mongodb-user/browse_frm/thread/a94ceeb6227929e8
http://groups.google.com/group/mongodb-user/browse_thread/thread/131b456224b4a3cd

I have now had to implement a generic mechanism for converting:
(a or b) and (c or d or e or f ...)
to:
(a and c) or (b and c)
or (a and d) or (b and d)
or (a and e) or (b and e)
or (a and f) or (b and f)
...
Which is tedious, error prone, and probably inefficient.

Fortunately, I was able to rely on the fact that we currently need only one
AND clause and that the left side of it is always an OR of exactly 2 clauses.
That simplified things a lot.

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
(Same as what Ed has mentioned above)

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:
ie (A || B) && (C || D)
becomes:
A && C || B && C || A && D || B && D

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()
true
> db.f.save({ session_id: 1, data: [

{ key: 'name', value: 'johm', timestamp: 20100504120000 }

,

{ key: 'lastname', value: 'doe', timestamp: 20100505120000 }

,

{ key: 'name', value: 'john', timestamp: 20100504120100 }

] });
> db.f.find( { data: { $all: [ { $elemMatch:

{ key: 'name', value: 'john' }

}, { $elemMatch:

{ key: 'lastname', value: 'doe' }

} ] } } );
{ "_id" : ObjectId("4c90142f27df0d163acb9a77"), "session_id" : 1, "data" : [

{ "key" : "name", "value" : "johm", "timestamp" : 20100504120000 }

,

{ "key" : "lastname", "value" : "doe", "timestamp" : 20100505120000 }

,

{ "key" : "name", "value" : "john", "timestamp" : 20100504120100 }

] }
> db.f.find( { data: { $all: [ { $elemMatch:

{ key: 'name', value: 'john' }

}, { $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?
The solution given in the post (converting AND to OR and vice versa) is not an option
as the queries will become too complex (the one used in the post was just a simplified example)
and I have to generate them programmatically.

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),
but in this kind of approach I face another limitation of the MongoDB query language
as you cannot use nested $or operators. Also, it seems that { $not : { $or : [

{ ... }

] } } 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:
http://groups.google.com/group/mongodb-user/browse_thread/thread/131b456224b4a3cd

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
the possibility of including it in 1.7.0 release already?

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"}

)
ObjectId('4c3b4080a3baa42f5d000000')
>>> db.test.save(

{"x": "mongodb"}

)
ObjectId('4c3b4086a3baa42f5d000001')
>>> db.test.save(

{"x": "hello mongo"}

)
ObjectId('4c3b408ea3baa42f5d000002')
>>> list(db.test.find({"x": {"$all": [re.compile("mongo"), re.compile("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.
However, the original problem was experienced not with the badly formed example but with real code — an adapter for a DB-agnostic Python API (Docu). The adapter for MongoDB used

{'$regex': 'foo'}

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

{
session_id: 1,
data: [

{ key: 'name', value: 'johm', timestamp: 20100504120000 }

,

{ key: 'lastname', value: 'doe', timestamp: 20100505120000 }

,

{ 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({
data: {
$elemMatch:

{key: 'name', value: 'john'}

,
$elemMatch:

{key: 'lastname', value: 'doe'}

}
});

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
For your case, I believe

find(

{ "data.name" : "john" , "data.lastname" : "doe" }

)
should work.

if not - please explain

Generated at Thu Feb 08 02:56:02 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.