CODEWITHSUNDEEP
mongodbspring-dataspring-data-mongodb
Saurabh Kumar
Saurabh Kumar

Reputation: 16651

Spring data mongo filter by difference in value of 2 fields

I have a document like following

Document {
   BigDecimal originalPrice
   BigDecimal discounPrice
}

Now i want to get results where the result of[originalPrice - discountPrice] is from biggest difference to smallest difference in a paginated manner.

Is there a way to achieve this using spring data mongodb without having to save the difference in a 3rd field and than query the third column ?

Update:

Based on @HbnKing answer i tried the following using spring-data-mongodb but now i get error

ProjectionOperation projectionOperation = project("originalPrice", "discountPrice").and(Subtract.valueOf("originalPrice")
                .subtract("discountPrice"))
                .as("diff");
SortOperation sortOperation = sort(new Sort(Direction.DESC, "diff"));

TypedAggregation<ProductVariantModel> aggregation = Aggregation
                .newAggregation(ProductVariantModel.class, projectionOperation, sortOperation, skip((long) pageable.getOffset()), limit((long) pageable.getPageSize()));

Error:

Command failed with error 16556 (Location16556): 'cant $subtract astring from a string' on server

Upvotes: 2

Views: 838

Answers (1)

HbnKing
HbnKing

Reputation: 1882

yes . you can use $subtract to compute the differ with your data then $sort

in mongo shell it looks like this 

Enterprise repl2:PRIMARY> db.wang.aggregate([{ $project: { a: 1, b:1, differ: { $subtract: [ "$a", "$b" ] } } },{$sort :{differ:-1}} ] )
{ "_id" : ObjectId("5d494045fd02abc31f622366"), "a" : 100, "b" : 30, "differ" : 70 }
{ "_id" : ObjectId("5d494062fd02abc31f622368"), "a" : 13, "b" : 7, "differ" : 6 }
{ "_id" : ObjectId("5d49406afd02abc31f622369"), "a" : 13, "b" : 9, "differ" : 4 }
{ "_id" : ObjectId("5d494058fd02abc31f622367"), "a" : 10, "b" : 7, "differ" : 3 }
MongoDB Enterprise repl2:PRIMARY> db.wang.find()  
{ "_id" : ObjectId("5d494045fd02abc31f622366"), "a" : 100, "b" : 30 }
{ "_id" : ObjectId("5d494058fd02abc31f622367"), "a" : 10, "b" : 7 }
{ "_id" : ObjectId("5d494062fd02abc31f622368"), "a" : 13, "b" : 7 }
{ "_id" : ObjectId("5d49406afd02abc31f622369"), "a" : 13, "b" : 9 }
MongoDB Enterprise repl2:PRIMARY>

I will give you a java demo later
Edit 

 AggregateIterable<Document> aggregate = collection.aggregate(Arrays.asList(Aggregates.project(fields(include("a", "b"),
                new BasicDBObject("differ",new BasicDBObject("$subtract",Arrays.asList("$a","$b"))))),
                Aggregates.sort(new BasicDBObject("differ",-1)),
                Aggregates.project(fields(include("a","b")))));
        MongoCursor<Document> iterator = aggregate.iterator();
        while (iterator.hasNext()){
            System.out.println(iterator.next());
        }

Edit for your error

Prefix the field with a $ when use Subtract .

Subtract.valueOf("$originalPrice")
                .subtract("$discountPrice"))
                .as("diff");

you'd better show your spring-data-mongodb version

Upvotes: 1

Related Questions