Mongodb - Get sales per hours

Question

Good people! I am in need of your help.

I am trying to create a line graph using apexcharts with data imported from Mongodb. I am trying to graph hourly sales, so I need the number of sales for each hour of the day.

Example Mongodb document.

{
    "_id" : ObjectId("5dbee4eed6f04aaf191abc59"),
    "seller_id" : "5aa1c2c35ef7a4e97b5e995a",
    "temp" : "4.3",
    "sale_type" : "coins",
    "createdAt" : ISODate("2020-05-10T00:10:00.000Z"),
    "updatedAt" : ISODate("2019-11-10T14:32:14.650Z")
}

Up to now I have a query like this:

    db.getCollection('sales').aggregate([
          { "$facet": {
            "00:00": [
              { "$match" : {createdAt: {$gte: ISODate("2020-05-10T00:00:00.000Z"),$lt: ISODate("2020-05-10T00:59:00.001Z")},seller_id: "5aa1c2c35ef7a4e97b5e995a",
        }},
              { "$count": "sales" },
            ],
            "01:00": [
              { "$match" : {createdAt: {$gte: ISODate("2020-05-10T01:00:00.000Z"),$lt: ISODate("2020-05-10T01:59:00.001Z")},seller_id: "5aa1c2c35ef7a4e97b5e995a",
        }},
              { "$count": "sales" },
            ],
             "02:00": [
              { "$match" : {createdAt: {$gte: ISODate("2020-05-10T02:00:00.000Z"),$lt: ISODate("2020-05-10T02:59:00.001Z")},seller_id: "5aa1c2c35ef7a4e97b5e995a",
        }},
              { "$count": "sales" },
            ],
             "03:00": [
              { "$match" : {createdAt: {$gte: ISODate("2020-05-10T03:00:00.000Z"),$lt: ISODate("2020-05-10T03:59:00.001Z")},seller_id: "5aa1c2c35ef7a4e97b5e995a",
        }},
              { "$count": "sales" },
            ],
          }},
          { "$project": {
            "ventas0": { "$arrayElemAt": ["$01:00.sales", 0] },
            "ventas1": { "$arrayElemAt": ["$02:00.sales", 0] },
            "ventas3": { "$arrayElemAt": ["$03:00.sales", 0] },
          }}
        ])

But I am sure there is a more efficient way to do this.

My expected output looks like this:

[countsale(00:00),countsale(01:00),countsale(02:00),countsale(03:00), etc to 24 hs]

Answer 1

You are correct, there is a more efficient way to do this. We can use Date expression operators and specifically by grouping with $hour.

db.getCollection('sales').aggregate([
    {
        $match: {
            createdAt: {$gte: ISODate("2020-05-10T00:00:00.000Z"), $lt: ISODate("2020-05-11T00:00:00.001Z")}
        }
    },
    {
        $group: {
            _id: {$hour: "$createdAt"},
            count: {$sum: 1}
        }
    },
    {
       $sort: {
          _id: 1
       }
    }
]);

This will give you this result:

[
    {
        _id: 0,
        count: x
    },
    {
        _id: 1,
        count: y
    },
    ...
    {
        _id: 23,
        count: z
    }
]

From here you can restructure the data easily as you wish.

A problem I forsee happening are hours without any matches (i.e count=0) will not exists in the result set. you'll have to fill in those gaps manually.