CODEWITHSUNDEEP
javamongodbspring-dataspring-data-mongodb
Hanan Bareket
Hanan Bareket

Reputation: 195

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

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.

Upvotes: 6

Views: 8059

Answers (4)

Ashish Sharma
Ashish Sharma

Reputation: 627

One way is this:

Criteria c = Criteria.where("total_units").gt("$purchased_unit");
AggregationOperation matchOperation = Aggregation.match(c);
Aggregation aggregation = Aggregation.newAggregation(matchOperation);
mongoTemplate.aggregate(aggregation, "collectionNameInStringOnly", ReturnTypeEntity.class);

Remember to put collection name in string so as to match the spellings of fields mentioned in criteria with fields in database collection.

Upvotes: -1

boly38
boly38

Reputation: 1955

Thanks @Andrew Onischenko for the historic good answer.

On more recent version of spring-data-mongodb (ex. 2.1.9.RELEASE), I had to write the same pattern like below:

import org.bson.Document;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;

// (...)

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

Query query = Query.query(criteria);

Upvotes: 1

Andrew Onischenko
Andrew Onischenko

Reputation: 431

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);

Upvotes: 5

chridam
chridam

Reputation: 103355

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);

Upvotes: 4

Related Questions