How to compare 2 fields in Spring Data MongoDB using query object

Question

What seems almost natural in simple SQL is impossible in mongodb.

Given a simple document:

{
    "total_units" : 100,
    "purchased_unit" : 60
}

I would like to query the collection, using spring data Criteria class, where "total_units > purchased_units".

To my understanding it should be as trivial as any other condition.

Found nothing to support this on Spring api.

Answer 1

You can use the following pattern:

Criteria criteria = new Criteria() {
    @Override
    public DBObject getCriteriaObject() {
        DBObject obj = new BasicDBObject();
        obj.put("$where", "this.total_units > this.purchased_units");
        return obj;
    }
};

Query query = Query.query(criteria);

Answer 2

I don't think Spring Data API supports this yet but you may need to wrap the $where query in your Java native DbObject. Note, your query performance will be fairly compromised since it evaluates Javascript code on every record so combine with indexed queries if you can.

Native Mongodb query:

db.collection.find({ "$where": "this.total_units > this.purchased_units" });

Native Java query:

DBObject obj = new BasicDBObject();
obj.put( "$where", "this.total_units > this.purchased_units");

Some considerations you have to look at when using $where:

Do not use global variables.

$where evaluates JavaScript and cannot take advantage of indexes. Therefore, query performance improves when you express your query using the standard MongoDB operators (e.g., $gt, $in). In general, you should use $where only when you can’t express your query using another operator. If you must use $where, try to include at least one other standard query operator to filter the result set. Using $where alone requires a table scan. Using normal non-$where query statements provides the following performance advantages:

MongoDB will evaluate non-$where components of query before $where statements. If the non-$where statements match no documents, MongoDB will not perform any query evaluation using $where. The non-$where query statements may use an index.

As far as I know you can't do query.addCriteria(Criteria.where("total_units").gt("purchased_units"));

but would go with your suggestion to create an additional computed field say computed_units that is the difference between total_units and purchased_units which you can then query as:

Query query = new Query();
query.addCriteria(Criteria.where("computed_units").gt(0));

mongoOperation.find(query, CustomClass.class);