[SERVER-9406] Allow treating ObjectId type as date in $project phase Created: 19/Apr/13 Updated: 06/Apr/23 Resolved: 12/Jun/17 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Aggregation Framework |
| Affects Version/s: | None |
| Fix Version/s: | 3.5.9 |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Asya Kamsky | Assignee: | Asya Kamsky |
| Resolution: | Done | Votes: | 43 |
| Labels: | eng-m, expression | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||
| Backwards Compatibility: | Fully Compatible | ||||||||||||||||
| Participants: | |||||||||||||||||
| Description |
|
Since ObjectId() encompasses an approximation of insertion date it would be helpful if _id/things of ObjectId() type could be operated/projected the same way as Datetime type object. {case}{$project:{$year:"$_id"}} would extract year out of ObjectId().getTimestamp equivalent.{case} One question would be whether ObjectId could be operated on directly via date/interval arithmetic. I.e. whether it can be used anywhere a date can be used returning a standard Datetime type as a result from say {$subtract:["$_id",86400000]} which can only return ts of one day before that ObjectId's timestamp. |
| Comments |
| Comment by David Storch [ 10/Aug/18 ] | ||||||||||||||||||||||||||||||||||||||||||||
|
Hi badil, the "fixVersion" field indicates that this was first released in development version 3.5.9. Therefore, the stable 3.6 and 4.0 release series contain these changes. Best, | ||||||||||||||||||||||||||||||||||||||||||||
| Comment by Laura Badi [ 08/Aug/18 ] | ||||||||||||||||||||||||||||||||||||||||||||
|
any progress ? if so which MongoDB version would support this feature ? | ||||||||||||||||||||||||||||||||||||||||||||
| Comment by Dissatisfied Former User [ 12/Jun/17 ] | ||||||||||||||||||||||||||||||||||||||||||||
|
So… still no way to explicitly target specific ObjectID elements, which would have totally been a generalized and more broadly useful solution to this issue? Guess I'll open another ticket… | ||||||||||||||||||||||||||||||||||||||||||||
| Comment by Githook User [ 12/Jun/17 ] | ||||||||||||||||||||||||||||||||||||||||||||
|
Author: {u'username': u'asya999', u'name': u'Asya Kamsky', u'email': u'asya999@gmail.com'}Message: Closes #1154 Signed-off-by: Charlie Swanson <charlie.swanson@mongodb.com> | ||||||||||||||||||||||||||||||||||||||||||||
| Comment by rain [ 31/May/17 ] | ||||||||||||||||||||||||||||||||||||||||||||
|
Ohh sorry, my bad | ||||||||||||||||||||||||||||||||||||||||||||
| Comment by Dissatisfied Former User [ 31/May/17 ] | ||||||||||||||||||||||||||||||||||||||||||||
|
@rain I'm referring to a hypothetical actual solution to this problem. E.g. the approach MongoDB would need to take to resolve this (writing a new Expression handler to do the ObjectID decomposition). This is not a current feature. | ||||||||||||||||||||||||||||||||||||||||||||
| Comment by rain [ 31/May/17 ] | ||||||||||||||||||||||||||||||||||||||||||||
|
@Alice Bevan-McGregor, is $decomposeId operator available in aggregation framework? which mongodb version you are referring? | ||||||||||||||||||||||||||||||||||||||||||||
| Comment by Dissatisfied Former User [ 31/May/17 ] | ||||||||||||||||||||||||||||||||||||||||||||
|
@rain Quite so. I see an optimum solution as one which allows extraction of all or only one component of the ID. It's useful for more than just the timestamp.
That'd give you:
To get just the timestamp in a usable form:
Bada bing. It would literally just be the addition of a new Expression handler similar to ExpressionDayOfMonth. Ugh, JIRA formatting is a pain. | ||||||||||||||||||||||||||||||||||||||||||||
| Comment by rain [ 31/May/17 ] | ||||||||||||||||||||||||||||||||||||||||||||
|
@Alice Bevan-McGregor, @Asya Kamsky, thanks for replying. | ||||||||||||||||||||||||||||||||||||||||||||
| Comment by Dissatisfied Former User [ 31/May/17 ] | ||||||||||||||||||||||||||||||||||||||||||||
|
@Aysa I'm once again not rightly able to apprehend the kind of confusion of ideas that finds that approach acceptable, reasonable, or even practical. Construction of every possible ObjectId value for *recursive* nested conditional comparison in a generated query limited to a specific range, limited to dates and not times only barely qualifies as functional. The generation of all that data prior to the query, transfer of the amplified query over the wire, and re-execution of the recursive set against every single record returned… the problems with this approach are extensive. The trade-off of saving the storage space for a proper creation time field vs. the above is unreasonable, and your short blog post does an excellent job of casually obfuscating (looping over the result of an external loop) and explicitly not mentioning these problems, and that this approach should not be recommended. It's a trick, certainly. Not a very good one. From the example on the blog post, what you're not clearly telling people is that you're actually transferring the following to the server: (manually wrapped because the mongo shell was just a right-hand column of closing braces when pretty printed)
This solution is its own problem. This is a solution for the actual problem the same way amputation of a finger is a solution for a hangnail. | ||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 31/May/17 ] | ||||||||||||||||||||||||||||||||||||||||||||
|
I wrote a blog post a while back about how to do date type reporting from ObjectId values: http://www.kamsky.org/stupid-tricks-with-mongodb/converting-objectid-to-dates-in-aggregation | ||||||||||||||||||||||||||||||||||||||||||||
| Comment by Dissatisfied Former User [ 31/May/17 ] | ||||||||||||||||||||||||||||||||||||||||||||
|
@rain Actually, yes, if you use pre-aggregated views and use this helper when utilizing standard find queries over the view. Requires creativity, but doable, unfortunately not doable directly with MongoDB. This ticket has languished a bit over the years. I'll also be adding to the framework a "from _id" pseudo "creation time" field to handle transparent storage of a real creation time for you, for use in true aggregates as another DAO patch for this issue. | ||||||||||||||||||||||||||||||||||||||||||||
| Comment by rain [ 31/May/17 ] | ||||||||||||||||||||||||||||||||||||||||||||
|
@Alice Bevan-McGregor , Is there way to project date from ObjectId ? | ||||||||||||||||||||||||||||||||||||||||||||
| Comment by Dissatisfied Former User [ 30/May/17 ] | ||||||||||||||||||||||||||||||||||||||||||||
|
As I've lost faith in MongoDB turnaround time, I've corrected this issue where I can in my DAO layer by allowing ObjectId field comparisons against dates and timedeltas, and date field comparison against ObjectIds. Doesn't fix the projection issue during aggregate, but helps reduce boilerplate on standard queries. The obligatory practical example:
| ||||||||||||||||||||||||||||||||||||||||||||
| Comment by rain [ 30/May/17 ] | ||||||||||||||||||||||||||||||||||||||||||||
|
any update on this? | ||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 27/Sep/13 ] | ||||||||||||||||||||||||||||||||||||||||||||
|
I think providing access to getTimestamp() method of ObjectId() type would be equivalent functionality. | ||||||||||||||||||||||||||||||||||||||||||||
| Comment by Dan Gayle [ 19/Sep/13 ] | ||||||||||||||||||||||||||||||||||||||||||||
|
This ticket referenced here: http://stackoverflow.com/questions/18691689/aggregate-mongodb-results-by-objectid-date/18882827 |