Home > OS >  Having problem with date comparison in @Query [mongo/springboot]
Having problem with date comparison in @Query [mongo/springboot]

Time:08-28

I am trying to create a custom Query annotation in a MongoRepository. Everything is fine except the date comparison. I need to find all items that were created on a specific day, so I give 2 date objects with the good date and the times fixed to 00:00 and 23:59 to gte and lte operators. This is the request sent according to the debug trace:

2022-08-27 01:52:25.817 DEBUG 440959 --- [or-http-epoll-4] o.s.data.mongodb.core.MongoTemplate      : find using query: { "$and" : [{ "$or" : [{ "$where" : "5 == null"}, { "rating" : 5}]}, { "$or" : [{ "$where" : "null == null"}, { "productId" : null}]}, { "$or" : [{ "$where" : "APPROVEDD == null"}, { "moderationStatus" : "APPROVEDD"}]}, { "$or" : [{ "$where" : "true == false"}, { "clientResponses" : { "$exists" : true, "$not" : { "$size" : 0}}}]}, { "$or" : [{ "$where" : "2022-01-29T23:00:00Z == null || 2022-01-30T22:59:59Z == null"}, { "submissionTime" : { "$gte" : { "$date" : "2022-01-29T23:00:00Z"}, "$lte" : { "$date" : "2022-01-30T22:59:59Z"}}}]}]} fields: Document{{}} for class: class com.company.productreviews.cdpresponseportalapi.model.Review in collection: reviews

Dates seem to be in the good format, but mongo gives me an error (only when I use the date filter of my request):

Query failed with error code 139 and error message 'SyntaxError: identifier starts immediately after numeric literal' on server mongodb-databaserevqa-shard-00-02.xxocp.mongodb.net:27017; nested exception is com.mongodb.MongoQueryException: Query failed with error code 139 and error message 'SyntaxError: identifier starts immediately after numeric literal' on server mongodb-databaserevqa-shard-00-02.xxocp.mongodb.net:27017

My repo:

public interface ReviewRepository extends MongoRepository<Review, String> {
    @Query("{ $and : ["  
            "{ $or : [ { $where: '?0 == null' }, { 'rating': ?0 } ] },"  
            "{ $or : [ { $where: '?1 == null' }, { 'productId': ?1 } ] },"  
            "{ $or : [ { $where: '?2 == null' }, { 'moderationStatus': ?2 } ] },"  
            "{ $or : [ { $where: '?3 == false' }, { 'clientResponses': { $exists: true, $not: { $size: 0 } } } ] },"  
            "{ $or : [ { $where: '?4 == null || ?5 == null' }, { 'submissionTime': { $gte: ?4, $lte: ?5 } } ] },"  
            "]}")
    List<Review> findAll(Integer rating, Integer productId, String moderationStatus, boolean withAnswersOnly, Date submissionDateStartRange, Date submissionDateEndRange);

    Review findOneByReviewId(int reviewId);
}

CodePudding user response:

It seems to be a syntax error when you date value are not null. Wrapping your statement in quote should do the trick. So in your last @Query statement do the following:

 "{ $or : [ { $where: '\"?4\" == \"null\" || \"?5\" == \"null\"' }, { 'submissionTime': { $gte: ?4, $lte: ?5 } } ] },"
  • Related