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 ] }
>
In your $where statement whole "where" predicate should be in quotes:
db.test.find({$where: "this.ratings[0] < this.ratings[1]"});
$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"
]
}}
])