Mongodb request to match each element of the doc array with a condition

Question

I have documents similar 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 returns a document only if the EACH element of its array meets the required criteria (and does not match ANY).

Eg. something like (but not)

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

which will successfully return the first two, but not the third document, since not all elements of the third value of the doc array meet the criteria.

Apparently mongodb uses an implicit OR in requests for arrays, whereas I need AND.

I think I could manually index each element, for example:

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 dynamic arrays.

Is there a better way?

Note. I asked a mongodb user about this, but a newbie to mongodb created confusion with $ all operator. Here I am concerned about the doc array, not the array of requests. Also, in this numerical case, I understand that you can write a query that negates the desired range, but in general I will not write negation.

Answer 1

I don’t think there is a way to do this yet, except by manually repeating your documents and checking each value in the array. This will be rather slow because it has to execute JavaScript on every document and cannot use any index on col.values .

Even $ where The request for a JavaScript expression does not work here because it is possible because the request contains a callback and is too complicated:

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

Strike>

Edit: For some queries, including this one, the JavaScript $ where expression requires a return expression, so this works great:

 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 quantifier (∃, exists) and its negation (∄, does not exist.) It does not have a universal quantifier (∀, for all) and its negation, since they cannot be optimized using indexes, and therefore will not be useful in practice.

Fortunately, in a first-order logic, every operator containing ∀ can be transformed into an equivalent operator containing ∃.

In your example, the statement:

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

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

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

or "there is no value not > 1 and <5", which according to the laws of De Morgan will look like this:

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

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

The latter can be expressed in MongoDB in various 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

This is not possible with MongoDB AFAIK right now.

Edit: I stand fixed. Perhaps it is simply not indexed, so for me it is an “impossible” territory. See below. You can also display the abbreviation. They are similar to cards "get out of jail" in MongoDB, but, as usual, with these cards, there are serious drawbacks compared to the real operator.

Answer 4

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

You can scroll through the elements in your array and return only true if all of them satisfy your condition.

Answer 5

A thread

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

explains your options.

As 'chx' wrote: this is not possible. Now you know the functionality of $ all and $ or operators. That's all you have, and getting from MongoDB is nothing more, nothing less.