1

I have a collection consisting of documents that contain 'firstSeen' and 'lastSeen' fields as Dates, like this:

{
  "_id" : ObjectId("58e4db42a8782455eb037590"),
  "firstSeen" : ISODate("2017-04-05T00:41:17.575Z"),
  "lastSeen" : ISODate("2017-04-05T01:16:28.015Z"),
  ...
}

And I want to aggregate in order to get the number of active items on a per-hour basis so I can plot a graph on a dashboard; e.g.

[
    { ISODate('2017-05-04T01:00:00.000Z'): { total: 1 } },
    { ISODate('2017-05-04T02:00:00.000Z'): { total: 2 } },
    { ISODate('2017-05-04T03:00:00.000Z'): { total: 1 } }
]

So the question I'm trying to ask of Mongo is something like "get me the number of devices where, for each hour between a start point and end point, that hour is between their firstSeen and lastSeen".

I've gotten to this stage:

coll.aggregate([{$match : {
    firstSeen: { $gte: ISODate('2017-01-01T00:00:00.000Z') },
    lastSeen: { $lte: ISODate('2017-05-01T00:00:00.000Z') },
    }},
{
$group : {
    _id : {
        hour: { $hour: "$firstSeen" },
        month: { $month: "$firstSeen" }, 
        day: { $dayOfMonth: "$firstSeen" },
        year: { $year: "$firstSeen" }
    },
    total: { $sum: 1 },
}
}])

Which gets me close, however my $group is not quite there: this will aggregate according to firstSeen, which means that if I have a document whose 'seens' span from 1am to 4am, that will only be reflected in the total for 1am, not 2am/3am/4am.

I believe I need to use $addFields or similar in order to create a 'for every hour' value on the document and then aggregate on that so that it can be included in several totals, but I'm not sure quite how to go about it.

Has anyone done this sort of thing before?

Mongo 3.4, my app is Node.js in case that makes a difference.

Neil Lunn
  • 148,042
  • 36
  • 346
  • 317
Jonny
  • 842
  • 5
  • 14

0 Answers0