Room reservation query from SQL to MongoDB -

i working on conference room reservation application based mongodb. have business entities:

class room {     private string id;     // ... }  class roomreservation {    private string id;    private string room_id;    private string user_id;    private date start;    private date end;    // ... } 

to find out available conference rooms in given period (start, end), have query following if use relationship database.

select * room r r.id not in (   select room_id room_reservation rr   (rr.start <= start , rr.end >= start)   union   select room_id room_reservation rr   (rr.start <= end , rr.end >= end)   union    select room_id room_reservation rr   (rr.start >= start , rr.end <= end)   union   select room_id room_reservation rr   (rr.start <= start , rr.end >= end) ) 

i can have similar query statement mongodb. approach won't effective however. unlike individual field returned in sql statement, whole document data, roomreservation needs returned result of mongodb query.

is aggregation more suitable problem? if so, how use aggregation operations solve problem?

unless i'm missing here union queries overkill here. in essence asking items contained within each condition set not part of results obtained collection.

this $nor operator in mongodb making sure none of conditions specified met in selection of documents.

in essence structure this:

db.collection.find({     "$nor": [         { "start": { "$lte": start }, "end": { "$gte": start } },         { "start": { "$lte": end },   "end": { "$gte": end } },         { "start": { "$gte": start }, "end": { "$lte": end } },         { "start": { "$lte": start }, "end": { "$gte": end } }     ] }) 

not sure of helper method construction without firing ide, operators here should looking query structure come out as.

--

using helper methods spring data:

    query query = new query().addcriteria(         new criteria().noroperator(                 criteria.where("start").lte(start).and("end").gte(end),                 criteria.where("start").lte(end).and("end").gte(end),                 criteria.where("start").gte(start).and("end").lte(end),                 criteria.where("start").lte(start).and("end").gte(end)         )     );