Find By Array Elements Comparison

Question

Let's say I have a collection called test:

> db.test.find()
{ "_id" : ObjectId("5887a9202d599801b7df2c3c"), "name" : "soccer66", "ratings" : [ 5, 3.2, 1 ] }
{ "_id" : ObjectId("5887a9232d599801b7df2c3d"), "name" : "user1", "ratings" : [ 2, 3.2, 1 ] }
{ "_id" : ObjectId("5887a9262d599801b7df2c3e"), "name" : "user2", "ratings" : [ 2.5, 4.9, 1 ] }

What's the optimal way to select the documents where the first rating is greater than the second? I've tried lots of queries. For the sake of this post I ran the following again:

> db.test.aggregate([
         {$project:{"isGreater":{$cond:[{$gt:["$ratings.0","$ratings.1"]},true,false]}}},
         {$match:{"isGreater": true}}]);
>

> db.test.find({$where: "this.ratings.0" < "this.ratings.1"});

Error: error: {
  "ok" : 0,
  "errmsg" : "$where got bad type",
  "code" : 2,
  "codeName" : "BadValue"
}
db.test.find({"ratings.0": {"$gt": "ratings.1"}});

Proof I'm using the correct array index with ratings.#:

> db.test.find({"ratings.0":5});
{ "_id" : ObjectId("5887a9202d599801b7df2c3c"), "name" : "soccer66", "ratings" : [ 5, 3.2, 1 ] }
>

score 4 · Answer 1 · answered Jan 24 '17 at 20:33

4

In your $where statement whole "where" predicate should be in quotes:

db.test.find({$where: "this.ratings[0] < this.ratings[1]"});

answered Jan 24 '17 at 20:33

Maksim Simkin

9,561
4
36
49

Thank you for your solution! Upvoted for the super-fast reply and conciseness (although it may not be optimal haha). – Matt Goodrich Jan 24 '17 at 22:14

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

Do not use the `$where` operator.

You should use the Aggregation Framework for this.

db.test.aggregate([
    { "$match": { "ratings.1": { "$exists": true } } },
    { "$redact": {
        "$cond": [
            { "$gt": [
                { "$arrayElemAt": [ "$ratings", 0 ] },
                { "$arrayElemAt": [ "$ratings", 1 ] }
            ]},
            "$$KEEP",
            "$$PRUNE"
        ]
    }}
])

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 24 '17 at 21:05

styvane

59,869
19
150
156

Perfect, thank you Styvane! Accepted this through the reasoning in your link. – Matt Goodrich Jan 24 '17 at 22:15

Find By Array Elements Comparison

2 Answers2

Do not use the $where operator.

Do not use the `$where` operator.