CODEWITHSUNDEEP
mongodbspring-datamongodb-queryspring-data-mongodb
Nitesh Virani
Nitesh Virani

Reputation: 1712

MongoDB Query Or Condition Spring Data

I have status collection which has four fields abc, xyz, status, time

I want to create MongoDB query for the below MySql query:

select * from status where abc = false and xyz = true and (status = pending or (status = inprogress and time = "2015-08-04"))

Basically status can be pending or inprogress but if it is inprogress then time must be compare.

I have tried to create below MongodDB query but it did not work

db.status.find
{   
    "abc" : false , 
    "xyz" : true , 
    "status" : "pending" , 
    "$and" : [ 
    { "status" : "inprogress"} , 
    { "time" : { "$eq" : { "$date" : "2015-08-04"}}}
    ]
}

I am using Spring MongoDB, I would appreciate if someone can guide me how to create it in Spring Data MongoDb.

Thanks

Upvotes: 1

Views: 7368

Answers (1)

Blakes Seven
Blakes Seven

Reputation: 50426

There area couple of things wrong with the current query you are trying. The first thing to understand is that all operations are implicitly and "AND" condition unless stated otherwise. Therefore the $or condition that you are missing here must contain "both" of the status test and other combined "time" conditions like so:

db.status.find({   
    "abc" : false, 
    "xyz" : true, 
    "$or" : [ 
        { "status" : "pending" },
        { 
           "status" : "inprogress"
           "time" : "2015-08-04"
        }
    ]
})

Your SQL representation here also implies that "time" is in fact a string. If it has been stored the same way in your MongoDB document then the above is fine. If not, and the values have been converted to actual Date objects in BSON, then you must supply a BSON date ( or something that converts ) as an argument. Typically dates are not "exact", so you use a "range" instead:

db.status.find({   
    "abc" : false, 
    "xyz" : true, 
    "$or" : [ 
        { "status" : "pending" },
        { 
           "status" : "inprogress"
           "time" : { "$gte": new Date("2015-08-04"), "$lt": new Date("2015-08-05") }
        }
    ]
})

For spring translation that would be:

Query query = new Query(Criteria.where("abc").is(false)
    .and("xyz").is(true)
    .orOperator(
        Criteria.where("status").is("pending"),
        Criteria.where("status").is("inprogress")
            .and("time").is("2015-08-04")
    )
);

Or with real date object and a range:

DateTime startDay = new DateTime(2015, 8, 4, 0, 0, DateTimeZone.UTC);
Date startDate = startDay.toDate();
Date endDate = startDay.plusDays(1).toDate();

Query query = new Query(Criteria.where("abc").is(false)
    .and("xyz").is(true)
    .orOperator(
        Criteria.where("status").is("pending"),
        Criteria.where("status").is("inprogress")
            .and("time").gte(startDate).lt(endDate)
    )
);

At least from memory that should be correct, as sitting away from an IDE right now. Also note the "chaining" of operators here such as $gte and $lt also means an "AND" condition as assigned to the values of the same key.

When dealing with dates, the underlying driver expects all input to match a BSON date be cast as a java.util.Date type from however you instantiate the date.

Upvotes: 2

Related Questions