mongodb query to match each element in doc array to a condition

Question

I have docs analogous to this:

{_id: 1, values : [2,3,4] }

{_id: 2, values: [4] }

{_id: 3, values : [3,4,5,6,7,8,9,10,11] }

in which each doc has an array. I need a query that only returns the doc if EACH element of its array match the desired criteria (rather than if ANY element matches).

Eg. something like (but not)

{ 'values' : { '$gt' : 1, '$lt': 5} })

which would successfully return the first two but not the third doc, as not all elements of the third doc's array 'values' match the criteria.

Apparently mongodb uses an implicit OR in queries on arrays, whereas I need AND.

I guess I could manually index each element, eg.:

collection.find({values.0: {$gt:1,$lt:5}, values.1:{$gt:1,$lt:5}, ... values.n:{$gt:1,$lt:5}}) but this is a pain with my highly dynamic arrays.

Is there a better way?

Note: I asked this over at mongodb-user but being new to mongodb generated confusion with $all operator. Here I am concerned about the doc array, not a query array. Also, in this numeric case I realize one might write a query that negates the range desired, but in general I won't be able to write the negation.

Answer 1

I don't think there's any way to do this yet, apart from manually iterating through your documents and checking each value in the array. That's going to be quite slow because it has to execute JavaScript on each document, and can't take advantage of any index over col.values.

Even a $where JavaScript expression query doesn't seem work here because, possibly because the query contains a callback and is too complex:

db.col.find("this.values.every(function(v) { return (v > 1 && v < 5) })")

Edit: For some queries, including this one, the JavaScript $where expression needs a return statement, so this works fine:

db.col.find("return this.values.every(function(v) { return (v > 1 && v < 5) })")

Answer 2

MongoDB, like most (if not all) databases, only implements the existential quantificator (∃, exists) and its negation (∄, does not exist.) It does not have the universal quantificator (∀, for all) nor its negation, because they cannot be optimized using indices, and therefore wouldn't be useful in practice.

Fortunately, in first-order logic, every statement involving ∀ can be transformed in an equivalent statement involving ∃.

In your example, the statement:

∀ x ∈ values : x > 1 ∧ x < 5

or "all values are > 1 and < 5" is equivalent to

∄ x ∈ values : ¬(x > 1 ∧ x < 5)

or "there is no value that is not > 1 and < 5" which by De Morgan's laws becomes:

∄ x ∈ values : x ≤ 1 ∨ x ≥ 5

or "there is no value that is ≤ 1 or ≥ 5"

The latter can be expressed in MongoDB in many ways, for example:

> db.test.remove()
> db.test.insert({_id: 1, values: [2, 3, 4]})
> db.test.insert({_id: 2, values: [4]})
> db.test.insert({_id: 3, values: [3, 4, 5, 6, 7, 8, 9, 10, 11]})

> db.test.find({$nor: [{values: {$lte: 1}}, {values: {$gte: 5}}]})
{ "_id" : 1, "values" : [  2,  3,  4 ] }
{ "_id" : 2, "values" : [  4 ] }

Answer 3

You'll sacrifice speed, but you could use a JavaScript expression, either passed directly to find() or to $where.

You'd be able to loop through the elements in your array and only return true if all of them satisfied your condition.

Answer 4

This is not possible with MongoDB AFAIK right now.

Edit: I stand corrected. It is possible it's just not indexable so it falls the 'impossible' territory' for me. See where below. You can also map reduce. These are like the "get out of jail" cards in MongoDB but as usual with those cards, there are severe disadvantages compared to a real operator.

Answer 5

The thread

http://groups.google.com/group/mongodb-user/browse_thread/thread/dad19e28c1acbd49

explains your options.

As 'chx' wrote: it is not possible. You now know the functionality of the $all and $or operators. That's all you have and get from MongoDB - nothing more, nothing less.