Query and Insert with a single command

Question

I have the following documents:

{
  "_id": "538584aad48c6cdc3f07a2b3",
  "startTime": "2014-06-12T21:30:00.000Z",
  "endTime": "2014-06-12T22:00:00.000Z",
},
{
  "_id": "538584b1d48c6cdc3f07a2b4",
  "startTime": "2014-06-12T22:30:00.000Z",
  "endTime": "2014-06-12T23:00:00.000Z",
}

All of them have startTime and endTime value. I need to maintain consistency that no two date spans in the collection overlap.

Let's say if I add the following document with the following dates:

db.collection.insert({
                      "startTime": "2014-06-12T19:30:00.000Z",
                      "endTime": "2014-06-12T21:00:00.000Z"
                     });

This date span insert should fail because it overlaps with an existing interval.

My questions are:

How to check for date span overlap?
How to check and insert with a single query?

EDIT: to prevent duplicate I ask there and start a bounty. I need to make update operation by using single query as described here: How to query and update document by using single query?

Normally, that's where unique indexes are able to help. But this is beyond their functionality. I'm afraid, you'll have to manage locking in your app. — Sergio Tulentsev, May 28 '14 at 10:08
Thanhs for the reply. How does unique indexes may help with my problem? — Erik, May 28 '14 at 10:14
You can have a look at them here : http://docs.mongodb.org/manual/tutorial/create-a-unique-index/ — Lalit Agarwal, May 28 '14 at 10:26
Are you actually storing your dates as strings? Are you able to store them as timestamp/seconds since epoch? — Asya Kamsky, Jun 03 '14 at 16:22
Yes I'm storing dates as strings but in mongoose Model they get converted into Date objects. — Erik, Jun 03 '14 at 16:29
what document are you updating? It seems like you are trying to do an insert, no? Or does the document already exist and you are trying to update it with time ranges but only if they don't overlap other documents in the collection? — Asya Kamsky, Jun 03 '14 at 17:04
The last one: "The document already exists and I'm trying to update it with time ranges but only if they don't overlap other documents in the collection" — Erik, Jun 03 '14 at 17:14
you kept saying "insert" - so I edited my answer to show how to insert using update. I think you *are* inserting (your other question had a find and an update, but that is not atomic - you don't need to create/find the document if you can insert it with an upsert). — Asya Kamsky, Jun 03 '14 at 18:16

Asya Kamsky · Answer 1 · 2014-06-03T18:15:42.470

The query is not as complicated as it may look at first - the query to find all documents which "overlap" the range you are given is:

db.test.find( { "startTime" : { "$lt" : new_end_time }, 
                "endTime"   : { "$gt": new_start_time } 
            } 
)

This will match any document with starting date earlier than our end date and end date greater than our start time. If you visualize the ranges as being points on a line:

-----|*********|----------|****|-----------|******||********|---
    s1         e1         s2   e2         s3     e3s4       e4

the sX-eX pairs represent existing ranges. If you take a new s5-e5 you can see that if we eliminate pairs that start after our end date (they can't overlap us) and then we eliminate all pairs that end before our start date, if we have nothing left, then we are good to insert.

That condition would be does a union of all documents with end date $lte our start and those with start date $gte ours include all documents already in collection. Our query flips this around to make sure that no documents satisfy the opposite of this condition.

On the performance front, it's unfortunate that you are storing your dates as strings only. If you stored them as timestamps (or any number, really) you could make this query utilize indexes better. As it is, for performance you would want to have an index on { "startTime":1, "endTime":1 }.

It's simple to find whether the range you want to insert overlaps any existing ranges, but to your second question:

How to check and insert with a single query?

There is no way proper way to do it with an inserts since they do not take a query (i.e. they are not conditional).

However, you can use an updates with upsert condition. It can insert if the condition doesn't match anything, but if it does match, it will try to update the matched document!

So the trick you would use is make the update a noop, and set the fields you need on upsert only. Since 2.4 there is a $setOnInsert operator to update. The full thing would look something like this:

db.test.update( 
   { startTime: { "$lt" : new_end_time }, "endTime" : { "$gt": new_start_time } }, 
   { $setOnInsert:{ startTime:new_start_time, endTime: new_end_time}},
   {upsert:1}
)
WriteResult({
"nMatched" : 0,
"nUpserted" : 1,
"nModified" : 0,
"_id" : ObjectId("538e0f6e7110dddea4383938")
})
db.test.update(
   { startTime:{ "$lt" : new_end_time }, "endTime" : { "$gt": new_start_time } },
   { $setOnInsert:{ startTime:new_start_time, endTime: new_end_time}},
   {upsert:1}
)
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 0 })

I just did the same "update" twice - the first time, there was no overlap document(s) so the update performed an "upsert" which you can see in the WriteResult it returned.

When I ran it a second time, it would overlap (itself, of course) so it tried to update the matched document, but noticed there was no work to do. You can see the returned nMatched is 1 but nothing was inserted or modified.

I don't understand how your approach could update document by using single query? Could you provide query with update operator? — Erik, Jun 03 '14 at 17:18
You asked how to find whether the range overlaps anything in the collection, that's the find for it (simplest possible syntax). I'll add a second bullet for the insert part - inserts are not conditional. — Asya Kamsky, Jun 03 '14 at 18:02
thanks for the reply. But my question is how could I "update" not "insert" document. And related to this post: http://stackoverflow.com/questions/24009553/how-to-query-and-update-document-by-using-single-query I've asked about update in this post because some admin marks the post above as "duplicate". Can I review this post http://stackoverflow.com/questions/24009553/how-to-query-and-update-document-by-using-single-query and answer there? — Erik, Jun 03 '14 at 18:37
your *other* question was about update. *This* question is most certainly about insert. Why don't you edit the other question to remove the duplicated part (the "how to do the query" part) and then that question will be about how to update one document based on properties of another document) FYI, I voted to reopen the other question since it's not a duplicate of this one. — Asya Kamsky, Jun 03 '14 at 19:09
Atomicity is not guaranteed in this solution as well. It is possible that two different queries finish the check at the same time and then write to the database while they themselves maybe conflicting. Or am i wrong? But i agree that the condition you have written for testing is good. Better than the answer that got up-voted. I am borrowing your test and putting it in my answer. — displayName, Jun 08 '14 at 18:28
@displayName since you are using an indexed query to search for the overlapping document, it's unlikely (if not impossible) that you could get a duplicate inserted (at least in the current version - it's possible such a race may have existed in an earlier version). — Asya Kamsky, Jun 16 '14 at 09:15

score 1 · Answer 2 · answered May 28 '14 at 10:52

This query should return all documents that somehow overlap with the new start/end-Time values.

db.test.find({"$or":[
    {"$and":[{"startTime":{"$lte":"new_start_time"}, "endTime":{"$gte":"new_start_time"}},  //new time has an old startTime in the middle
             {"startTime":{"$lte":"new_end_time"},   "endTime":{"$lte":"new_end_time"}}]},
    {"$and":[{"startTime":{"$gte":"new_start_time"}, "endTime":{"$gte":"new_start_time"}},  //new time sorounds and old time
             {"startTime":{"$lte":"new_end_time"},   "endTime":{"$lte":"new_end_time"}}]},
    {"$and":[{"startTime":{"$gte":"new_start_time"}, "endTime":{"$gte":"new_start_time"}},  //an old time has the new endTime in the middle
             {"startTime":{"$lte":"new_end_time"},   "endTime":{"$gte":"new_end_time"}}]},
    {"$and":[{"startTime":{"$lte":"new_start_time"}, "endTime":{"$gte":"new_start_time"}},  //new time is within an old time
             {"startTime":{"$lte":"new_end_time"},   "endTime":{"$gte":"new_end_time"}}]}
      ]})

Thanks for the answer may you refactor your snippet to be possible that endTime of one document allows same startTime another document? — Erik, May 28 '14 at 10:55
@Erik: you need to replace some `"$lte"` (lesser than or equal) by `"$lt"` (strictly lesser than) and some `"$gte"` (greather than or equal) by `"$gt"` (strictly greather than). I hope the logic is clear by my comments so you can change this by your self, this also helps you understanding the code. — Simulant, May 28 '14 at 11:04
Is it possible to make update in single query http://stackoverflow.com/questions/24009553/how-to-query-and-update-document-by-using-single-query ? — Erik, Jun 03 '14 at 08:39

score 0 · Answer 3 · edited May 23 '17 at 11:57

0

You want to run both queries at the same time. It means you want Synchronous in your code visit this question it may help for your answer

Synchronous database queries with Node.js

edited May 23 '17 at 11:57

Community

1
1

answered Jun 10 '14 at 06:15

Engineer

1,436
3
18
33

Query and Insert with a single command

3 Answers3

Linked