CODEWITHSUNDEEP
mongodbmongodb-query
Anon
Anon

Reputation: 133

Remove documents from a MongoDB collection based on "time" of a Date field

I have a collection which stores documents with the following structure:

{ 
    "_id" : NumberLong(-6225637853094968071), 
    "Id" : "1585534", 
    "Type" : NumberInt(42), 
    "InDate" : ISODate("2017-10-05T18:30:00.000+0000"), 
}
{ 
    "_id" : NumberLong(-622563784353458071), 
    "Id" : "15832422", 
    "Type" : NumberInt(42), 
    "InDate" : ISODate("2017-10-06T00:00:00.000+0000"), 
}

I want to delete all documents with a particular "Id" and "Type" where the "InDate" field has time 18:30:00.000+0000. I tried using this query:

 db.collection.remove({
"ChannelType": NumberInt(28),
"HotelId": "1585534",
"CheckInDate": /$18:30:00.000+0000/
})

but it doesnt work. How to do this?

Upvotes: 1

Views: 2641

Answers (2)

Neil Lunn
Neil Lunn

Reputation: 151122

A Regular Expression will not work on something that is not a string. There actually is no simple way of selecting a BSON Date by purely it's time value in a regular query expression.

In order to do this you would supply a $where condition with the additional logic to match on just the time portion of the date value.

Based on the data actually supplied in the question:

db.getCollection('collection').remove({
  "Id": "1585534",
  "Type": NumberInt(42),
  "$where": function() {
    return this.InDate.getUTCHours() === 18
      && this.InDate.getUTCMinutes() === 30
  }
})

Which is of course just the first document in the provided data, even though it's really just identified by "Id" anyway since that value is unique between the two.

The .getUTCHours() and .getUTCMinutes() are JavaScript Date object methods, which is how a BSON Date is expressed when used within a $where expression.

Trivially, MongoDB 3.6 ( upcoming as of writing ) allows a more efficient form than $where in aggregation expressions:

db.getCollection('collection').aggregate([
  { "$match": {
    "Id": "1585534",
    "Type": NumberInt(42),
    "$expr": {
      "$and": [
        { "$eq": [{ "$hour": "$InDate" }, 18 ] },
        { "$eq": [{ "$minute": "$InDate" }, 30] } 
      ] 
    }   
  }}
])

However such expressions cannot be used directly with methods such as .remove() or .deleteMany(), but $where expressions can.

Upvotes: 2

sheplu
sheplu

Reputation: 2975

Iso date don't match a string, you need to use isodate in the query too

db.collection.remove({
    "ChannelType": NumberInt(28),
    "HotelId": "1585534",
    "InDate": ISODate(2017-10-05T18:30:00.000+0000)
})

Upvotes: 1

Related Questions