CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
pudge
pudge

Reputation: 13

MongoDb: querying against a collection's own fields

I've done some research and it seems that it's possible to query (i.e. compare) two fields in the same collection using the aggregation framework. It's also possible with the $where operator but I want to avoid a low performance Javascript solution.

Here's an example document:

{
    "_id" : ObjectId("541ba14d2208236d06ff1e57"),
    "a" : "foo",
    "d" : {
        "e" : "foo"
    }
}
{
    "_id" : ObjectId("541ba14d2208236d06ff1e58"),
    "a" : "foo",
    "d" : {
        "e" : "bar"
    }
}

I'd like to pick the documents where 'a' != 'd.e'. I've attempted the following without success: db.test.aggregate([{$match: {$ne: ['$a', '$d.e']}}]);

Upvotes: 1

Views: 298

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151190

As you said the query can be done with JavaScript by issuing a $where condition in your query:

db.test.find(function() { return this.a != this.d.e } )

Which is the short form of the query.

While you can do other manipulation in the aggregation framework, it does not change the basic nature of the query in that you cannot place a query condition that compares the values of two fields. This is why $match alone cannot do this because it follows the same rules.

What you "can" do is $project another field value that matches the same logical conditions that you want to enforce. Depending on your actual implementation this may or may not be better for performance:

db.test.aggregate([
    { "$project": {
        "a": 1,
        "d": 1,
        "notEqual": { "$ne": [ "$a", "$d.e" ] }
    }},
    { "$match": { "notEqual": true } }
])

That probably is not going to make a lot of sense on it's own unless some other filtering is done in the overall process though. But the general comparison is done with a comparison operator to return a true/false result that can then be filtered.

So the best thing to do if you can is to actually maintain the result of this in a similar way by a field that is present on your document. Then you have a basic query condition to look for that value rather than the comparison. This is if you need to regularly do these kinds of checks.

But for "ad-hoc" purposes, you either stick with the JavaScript evaluation or use the "projection" form in aggregation queries ( where you cannot use a $where clause ) in order to do the field level comparison.

Upvotes: 1

Related Questions