[SERVER-13695] Support $currentDate expression for insert Created: 23/Apr/14  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Write Ops
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: John Butler Assignee: Backlog - Query Execution
Resolution: Unresolved Votes: 19
Labels: expression, insert
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Duplicate
is duplicated by SERVER-22963 New value type that the server transl... Closed
is duplicated by SERVER-17540 Insert support for $currentDate Closed
Related
related to SERVER-22963 New value type that the server transl... Closed
related to SERVER-2064 $sysdate for inserts/updates Closed
is related to SERVER-43014 Timestamp(0,0) not filled in for _id ... Closed
Assigned Teams:
Query Execution
Backwards Compatibility: Fully Compatible
Participants:

 Description   

The `$currentDate` update modifier is great and is something our project really needs. But we want to use it during an `insert`. As it stands we could do an update with upsert:true but we assume that the update will be slow/less-efficient than insert.



 Comments   
Comment by Andrey Hohutkin [ 01/Jun/21 ]

As of MongoDB 4.2 it is possible to do it using aggregation pipelines in update

db.a.update({asd:123}, [{
   timestamp: {
      $cond: {
         if: {$eq: [{$type: "$timestamp"}, "missing"]},
         then: '$$NOW',
         else: '$timestamp'    // it's the update case
      }
   }
}], { upsert: true} )

Comment by Daniel Pasette (Inactive) [ 07/Nov/15 ]

This may be a duplicate of SERVER-2064

Comment by Nils [ 14/Aug/15 ]

Fair enough, for a complex case like that it might work, but not for the simple case of "_id".

Comment by Gian Franco Zabarino [ 14/Aug/15 ]

It does, you could search for three keys and have an unique compound key for two of those three keys. If the third key you are searching is different from a document with the two first keys equal, mongodb will try to insert another document, causing a duplicate error.

Comment by Nils [ 14/Aug/15 ]

Upsert will either update or insert. Not sure how a duplicate key exception makes sense in that context.

Comment by Gian Franco Zabarino [ 14/Aug/15 ]

Hmm I just tried

db.a.update({asd:123}, { $setOnInsert: { $currentDate: { timestamp: true } } }, { upsert: true} )

and it throws the following error:

WriteResult({
	"nMatched" : 0,
	"nUpserted" : 0,
	"nModified" : 0,
	"writeError" : {
		"code" : 52,
		"errmsg" : "The dollar ($) prefixed field '$currentDate' in '$currentDate' is not valid for storage."
	}
})

I'm not using the latest version though. Was this introduced recently? I'm using:
db version v2.6.0
git version: 1c1c76aeca21c5983dc178920f5052c298db616c

Thanks for the support BTW.

Comment by Asya Kamsky [ 14/Aug/15 ]

edit This comment mistakenly says that these two update operators can be combined - that is incorrect. Leaving the comment in to avoid confusion around follow-ups.

-What you are describing already works. It works exactly the way you need it to work. $setOnInsert support $currentDate.-

And upsert will absolutely fail on duplicate constraint Nils.

Comment by Gian Franco Zabarino [ 14/Aug/15 ]

I might be wrong, but I think it does fail on unique indexes constraints. Did you try this and it inserts a duplicated document with the same value for the key that is uniquely indexed?

Comment by Nils [ 14/Aug/15 ]

Upsert is not good enough. It won't fail on duplicate keys, which is required for my use case.

Comment by Gian Franco Zabarino [ 14/Aug/15 ]

I know it doesn't. I was just stating my particular case in which I need to do an update with an upsert operation, do some modifications using $set to all documents (inserted and matching) but I only need to set a timestamp on inserted documents (which is why I was mentioning the $currentDateOnInsert idea). I understand it is not the original JIRA ticket's intention, but still, I wanted to state my case and it seemed appropriate at the time to do so.

NOTE: I just read again your comment, and you are saying to me that $currentDate works when updating with upsert. It does, yes, but it will also modify my existing documents which shouldn't be updated on my particular case. $setOnInsert is a functionality that doesn't have a counterpart for $currentDate. $setOnInsert will only apply to inserted documents. $currentDate will apply to both matching and inserted documents.

Comment by Asya Kamsky [ 14/Aug/15 ]

$currentDate does not work on insert operations. It works on updates whether they are upserts or not.

So if you want to use it on insert you can do it by doing an update with upsert option and $currentDate in $setOnInsert clause.

Comment by Gian Franco Zabarino [ 14/Aug/15 ]

$currentDateOnInsert comes to mind.

Comment by Gian Franco Zabarino [ 13/Aug/15 ]

Yup, it doesn't work:

> db.a.insert({name:'a'})
 
WriteResult({ "nInserted" : 1 })
 
> db.a.update({name:'a'}, {$set:{$currentDate:{asd:true}}})
 
WriteResult({
	"nMatched" : 0,
	"nUpserted" : 0,
	"nModified" : 0,
	"writeError" : {
		"code" : 52,
		"errmsg" : "The dollar ($) prefixed field '$currentDate' in '$currentDate' is not valid for storage."
	}
})

Comment by Gian Franco Zabarino [ 13/Aug/15 ]

Well, the way I see it is that $setOnInsert is a specialized $set operation that is executed during inserts only. Setting a $currentDate entry on a $setOnInsert object is analog to setting a $currentDate entry on a $set object, which as far as I know does not work. That's what I mean when I say that it doesn't look good. It makes sense, but it doesn't feed good to me.

Comment by Asya Kamsky [ 13/Aug/15 ]

Not sure what you mean by "doesn't look very good" - that's exactly the purpose of $setOnInsert to set fields during upsert only if it was an insert and not an update.

Comment by Gian Franco Zabarino [ 13/Aug/15 ]

Yup, I hate having to do something like

var now = new Date();
// maybe this would be inside a bulk of inserts or just a plain insert call
db.collection.insert(

{ timestamp: now, ... }

);

In my particular case I need to insert a timestamp only when inserting, but not when updating, and I would love to be able to do that in just one db operation. Basically, make work $currentDate to apply only when inserting when

{ upsert: true }

. A quick thought I have is to be able to insert a $currentDate entry inside a $setOnInsert one, but I know it doesn't look very good. Any thoughts?

Comment by Nils [ 20/Jan/15 ]

We need $currentDate on insert also, hopefully with the timestamp returned, to avoid a subsequent query.

Comment by Scott Hernandez (Inactive) [ 23/Apr/14 ]

Sort of, the _id check doesn't require an actual query, just an
attempt to record it in the index, like all other indexed fields. So
the query part isn't strictly needed for insert, just for update but I
suspect performance should be good enough going the update route even
with the extra query portion.

Comment by John Butler [ 23/Apr/14 ]

Scott, if the only thing in the query is `_id` would be close in performance to `insert`? After all, `insert` has to do a search on `_id` to ensure no duplicates.

Comment by Scott Hernandez (Inactive) [ 23/Apr/14 ]

There is currently no support for expressions during insert so adding something like this requires addressing that concept first.

(and yes, doing an update will be slightly slower since it has to first query and then if nothing is found, insert – the query may be very fast but still is more work)

Generated at Thu Feb 08 03:32:35 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.