how to validate date range and insert new data in a single command

Question

Let's say i have a collection named "rooms" that contain 20 documents, each document has a "start_date" and "end_date" in "room_record" field, now i wanted to insert a new record in the room_record field but i wish to check the new record start and end date isn't in between the existed record start_date and end_date if it is overlap then the query have to fail or not insert the new record else insert the new record. i have found a similar question in here Query and Insert with a single command however i can't solve my problem by trying their approach.

this is what i have so far

room collections document

{
    "_id" : "0JikW3",
    "room_capacity" : "2",
    "room_description" : "bla bla bla",
    "room_image" : [
        "http://i.imgur.com/n99LkGg.jpg",
        "http://i.imgur.com/zLPNy83.jpg"
    ],
    "room_price" : "250",
    "room_record" : {
        "end_date" : "2014-08-18T13:54:52.891Z",
        "start_date" : "2014-08-16T13:54:52.891Z"
    },
    "room_status" : "0",
    "room_type" : "A"
}

this is my approach

 //build a record with new start and end date
 var room_record = {
         "booking_id":bookingID,
         "start_date":data.startDate,
         "end_date":endDate
   }

    rooms.update( 
      {"room_record":{"$elemMatch":{"room_record.start_date":{"$lt":data.startDate},"room_record.end_date":{"$gt":endDate}},"room_type":data.roomType}},
      { $setOnInsert:{"room_record":room_record}},
    {upsert:1},
        function(err,result){
            if (err) return callback(err, null);
                callback(err, result);
                console.log(result)
           })

after perform my approach this is what i get in my room collection, instead of insert into the existing document it created a new document.

{
    "_id" : ObjectId("53ef765974574412ef7d0a49"),
    "room_record" : {
        "end_date" : ISODate("2014-08-21T13:54:52.891Z"),
        "start_date" : "2014-08-19T13:54:52.891Z"
    }
}

this is the result i expected

{
        "_id" : "0JikW3",
        "room_capacity" : "2",
        "room_description" : "bla bla bla",
        "room_image" : [
            "http://i.imgur.com/n99LkGg.jpg",
            "http://i.imgur.com/zLPNy83.jpg"
        ],
        "room_price" : "250",
        "room_record" : [{
            "end_date" : "2014-08-18T13:54:52.891Z",
            "start_date" : "2014-08-16T13:54:52.891Z"
        },{
            "end_date" : ISODate("2014-08-21T13:54:52.891Z"),
            "start_date" : "2014-08-19T13:54:52.891Z"
        }],
        "room_status" : "0",
        "room_type" : "A"
    }

your help is appreciated, thank you !

Answer 1

$elemMatch only works for array field. Your update fails to find any matched document because your room_record is a sub-document, not array. Then upsert affects to insert a new document. According to the output you expect, I assume that you want to push a new record into the array whose document maybe exist, so you can not adopt $setOnInsert as another question did.

Following code for your information, running in mongo shell.

var startDate = ISODate("2014-08-17T13:54:52.891Z");
var endDate = ISODate("2014-08-18T17:54:52.891Z");
var roomType = "A";
var room_record = {
        "start_date" : startDate,
        "end_date" : endDate
    };

// check if any document overlapped by the time interval
db.rooms.find({
    "room_record" : {
        "$elemMatch" : {
            "start_date" : {
                "$lte" : startDate
            },
            "end_date" : {
                "$gte" : endDate
            }
        }
    },
    "room_type" : roomType
}).forEach(printjson);

// Push new record into array field "room_record" if the record has not been overlapped by any element in the array.
// Please note that update will fail and produce an error if query succeeds and "room_record" exists but is not an array field.
// New document will be inserted into collection if no document matched.
db.rooms.update({
    "room_record" : {
        $not : {
            "$elemMatch" : {
                "start_date" : {
                    "$lte" : startDate
                },
                "end_date" : {
                    "$gte" : endDate
                }
            }
        }
    },
    "room_type" : roomType
}, {
    $push : {
        "room_record" : room_record
    }
}, {
    upsert : true
});