mapreduce - Is it possible to retrieve a 'time span' from a MongoDB query, using the timestamp within an ObjectId? -

mapreduce - Is it possible to retrieve a 'time span' from a MongoDB query, using the timestamp within an ObjectId? -

we have basic enquiry management tool we're using track website enquiries in our administration suite, , we're using objectid of each document in our enquiries collection sort enquiries date added.

class="lang-js prettyprint-override">{ "_id" : objectid("53a007db144ff47be1000003"), "comments" : "this test enquiry. please ignore. we'll delete shortly.", "customer" : { "name" : "test enquiry", "email" : "test@test.com", "telephone" : "07890123456", "mobile" : "07890123456", "quote" : false, "valuation" : false }, "site" : [], "test" : true, "updates" : [ { "_id" : objectid("53a007db144ff47be1000001"), "status" : "new", "status_id" : objectid("537de7c3a5e6e668ffc2335c"), "status_index" : 100, "substatus" : "new web enquiry", "substatus_id" : objectid("5396bb9fa5e6e668ffc23388"), "notes" : "new enquiry received website.", }, { "_id" : objectid("53a80c977d299cfe91bacf81"), "status" : "new", "status_id" : objectid("537de7c3a5e6e668ffc2335c"), "status_index" : 100, "substatus" : "attempted contact", "substatus_id" : objectid("53a80e06a5e6e668ffc2339e"), "notes" : "in test, pretend we've not managed hold of client on first attempt.", }, { "_id" : objectid("53a80e539b966b8da5c40c36"), "status" : "approved", "status_id" : objectid("52e77a49d85e95f00ebf6c72"), "status_index" : 200, "substatus" : "enquiry confirmed", "substatus_id" : objectid("53901f1ba5e6e668ffc23372"), "notes" : "in test, pretend we've got hold of client after failing contact them on first attempt.", } ] }

within each enquiry updates array of objects have objectid main identity field. we're using $unwind , $group aggregation pull first , latest updates, count of updates, making sure take enquiries there have been more 1 update (as 1 automatically inserted when enquiry made):

class="lang-js prettyprint-override">db.enquiries.aggregate([ { $match: { "test": true } }, { $unwind: "$updates" }, { $group: { "_id": "$_id", "latest_update_id": { $last: "$updates._id" }, "first_update_id": { $first: "$updates._id" }, "update_count": { $sum: 1 } } }, { $match: { "update_count": { $gt: 1 } } } ])

this results in next output:

class="lang-js prettyprint-override">{ "result" : [ { "_id" : objectid("53a295ad122ea80200000005"), "latest_update_id" : objectid("53a80bdc7d299cfe91bacf7e"), "first_update_id" : objectid("53a295ad122ea80200000003"), "update_count" : 2 }, { "_id" : objectid("53a007db144ff47be1000003"), "latest_update_id" : objectid("53a80e539b966b8da5c40c36"), "first_update_id" : objectid("53a007db144ff47be1000001"), "update_count" : 3 } ], "ok" : 1 }

this passed through our code (node.js, in case) perform few operations on , nowadays info on our dashboard.

ideally, i'd add together $group pipeline aggregation query subtract timestamp of first_update_id timestamp of latest_update_id give timespan, utilize $avg on.

can tell me if possible? (thank you!)

as neil pointed out, can't timestamp objectid in aggregation framework.

you said speed not important, using mapreduce can want:

var map = function() { if (this.updates.length > 1) { var first = this.updates[0]; var lastly = this.updates[this.updates.length - 1]; var diff = last._id.gettimestamp() - first._id.gettimestamp(); var val = { latest_update_id : last._id, first_update_id : first._id, update_count : this.updates.length, diff: diff } emit(this._id, val); } }; var cut down = function() { }; db.runcommand( { mapreduce: "enquiries", map: map, reduce: reduce, out: "mrresults", query: { test : true} } );

this results:

{ "_id" : objectid("53a007db144ff47be1000003"), "value" : { "latest_update_id" : objectid("53a80e539b966b8da5c40c36"), "first_update_id" : objectid("53a007db144ff47be1000001"), "update_count" : 3, "diff" : 525944000 } }

edit:

if want average diff documents can this:

var map = function() { if (this.updates.length > 1) { var first = this.updates[0]; var lastly = this.updates[this.updates.length - 1]; var diff = last._id.gettimestamp() - first._id.gettimestamp(); emit("1", {diff : diff}); } }; var cut down = function(key, values) { var reducedval = { count: 0, sum: 0 }; (var idx = 0; idx < values.length; idx++) { reducedval.count += 1; reducedval.sum += values[idx].diff; } homecoming reducedval; }; var finalize = function (key, reducedval) { reducedval.avg = reducedval.sum/reducedval.count; homecoming reducedval; }; db.runcommand( { mapreduce: "y", map: map, reduce: reduce, finalize : finalize, out: "mrtest", query: { test : true} } );

and illustration output:

> db.mrtest.find().pretty() { "_id" : "1", "value" : { "count" : 2, "sum" : 1051888000, "avg" : 525944000 } }

mongodb mapreduce timestamp aggregation-framework