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

Answer 1

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

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

Answer 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"
        ]
    }}
])