[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:
Documented
is documented by DOCS-10404 Docs for SERVER-9406: Allow treating ... Closed
Related
is related to SERVER-24947 Need a type conversion mechanism for ... Closed
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,
Dave

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: SERVER-9406 treat ObjectId type as Date in aggregation date expressions

Closes #1154

Signed-off-by: Charlie Swanson <charlie.swanson@mongodb.com>
Branch: master
https://github.com/mongodb/mongo/commit/90c32254af51f11aa494bc061f0afe2669c1189d

Comment by rain [ 31/May/17 ]

Ohh sorry, my bad . yes, that's what exactly I'm looking for

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.

{$project: {parts: {$decomposeId: {_id: true}}}

That'd give you:

{parts: {ts: ISODate, host: int, pid: int, counter: int}

To get just the timestamp in a usable form:

{$project: {created: {$decomposeId: {_id: "ts"}}}

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.
My concern is, since the _id, ObjectId by default stores the 4-byte timestamp, why it's not supporting to do the type conversion at the aggregation level, because it already supports a number of date aggregation operators. So if it allows converting the _id to timestamp or date, this won't be that much complicated.

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)

{"$cond":{"if":{"$lt":["$_id",{"$oid":"52c35a80f000000000000000"}]},"then":"< 2014-01","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"52ec3900f000000000000000"}]},"then":"2014-01","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"53112300f000000000000000"}]},"then":"2014-02","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"533a0180f000000000000000"}]},"then":"20
14-03","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"53618e80f000000000000000"}]},"then":"2014-04","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"538a6d00f000000000000000"}]},"then":"2014-05","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"53b1fa00f000000000000000"}]},"then":"2014-06","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"53dad880f000000000000000"}]
},"then":"2014-07","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"5403b700f000000000000000"}]},"then":"2014-08","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"542b4400f000000000000000"}]},"then":"2014-09","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"54542280f000000000000000"}]},"then":"2014-10","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"547baf80f000000000000000"}]},"then":"2014-11","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"54a48e00f000000000000000"}]},"then":"2014-12","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"54cd6c80f
000000000000000"}]},"then":"2015-01","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"54f25680f000000000000000"}]},"then":"2015-02","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"551b3500f000000000000000"}]},"then":"2015-03","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"5542c200f000000000000000"}]},"then":"2015-04","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid
":"556ba080f000000000000000"}]},"then":"2015-05","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"55932d80f000000000000000"}]},"then":"2015-06","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"55bc0c00f000000000000000"}]},"then":"2015-07","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"55e4ea80f000000000000000"}]},"then":"2015-08","else":
{"$cond":{"if":{"$lt":["
$_id",{"$oid":"560c7780f000000000000000"}]},"then":"2015-09","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"56355600f000000000000000"}]},"then":"2015-10","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"565ce300f000000000000000"}]},"then":"2015-11","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"5685c180f000000000000000"}]},"then":"2015-12","else":
{"$cond":{"i
f":{"$lt":["$_id",{"$oid":"56aea000f000000000000000"}]},"then":"2016-01","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"56d4db80f000000000000000"}]},"then":"2016-02","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"56fdba00f000000000000000"}]},"then":"2016-03","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"57254700f000000000000000"}]},"then":"2016-04","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"574e2580f000000000000000"}]},"then":"2016-05","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"5775b280f000000000000000"}]},"then":"2016-06","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"579e9100f000000000000000"}]},"then":"2016-07","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"57c76f80f000000000000000"}]},"then":"2016
-08","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"57eefc80f000000000000000"}]},"then":"2016-09","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"5817db00f000000000000000"}]},"then":"2016-10","else":
{"$cond":{"if":{"$lt":["$_id",{"$oid":"583f6800f000000000000000"}]},"then":"2016-11","else":">2016-12"}
}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}

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:

# Find all threads created or commented on in the last 7 days.
collection.find((Thread.id | Thread.reply.id) >= -timedelta(days=7))

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

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