MongoDB aggregate count the number of products between hour range

Question

I'm using mongodb charts. is it possible to get the number of products within the time range of (eg: 12:00am - 5:00 AM)

For example I have 2 products added at 3:00AM and then I will have 5 products added at 4:00AM.

I'm counting the products by it's createdAt field.

I have a sample data like this:

Products: {
        "_id": {
            "$oid": "603c62272aacbc0017e2e4b5"
        },
        "amount": 110,
        "user": {
            "$oid": "5f7408cf7889580017c369a1"
        },
        "transaction_id": "cZ3fgffFI",
        "createdAt": {
            "$date": "2021-03-01T03:40:23.300Z"
        },
        "updatedAt": {
            "$date": "2021-03-01T04:13:25.908Z"
        },
        "__v": 0,
    }, 
{
            "_id": {
                "$oid": "603c62272aacbc0017e2e4b5"
            },
            "amount": 110,
            "user": {
                "$oid": "5f7408cf7889580017c369a1"
            },
            "transaction_id": "cZ3fgffFI",
            "createdAt": {
                "$date": "2021-03-01T03:40:23.300Z"
            },
            "updatedAt": {
                "$date": "2021-03-01T04:13:25.908Z"
            },
            "__v": 0,
        },
{
            "_id": {
                "$oid": "603c62272aacbc0017e2e4b5"
            },
            "amount": 110,
            "user": {
                "$oid": "5f7408cf7889580017c369a1"
            },
            "transaction_id": "cZ3fgffFI",
            "createdAt": {
                "$date": "2021-03-01T03:40:23.300Z"
            },
            "updatedAt": {
                "$date": "2021-03-01T04:13:25.908Z"
            },
            "__v": 0,
        }

Output:

I'm counting the products using the createdAt field.

3:00 AM 2 products 4:00 AM 5 products

```{ '$match':{ '$and':[ {'createdAt':{'$gte':'2021-03-08T00:00:00.000Z'}},{'createdAt':{'$lte':'2021-03-08T05:00:00.000Z'}}] } }``` just add the condition for per day product count in the query filter. The same has been edited in answer. — Archana Agivale, Mar 09 '21 at 06:02

Archana Agivale · Answer 1 · 2021-03-09T06:00:32.823

You can simply use group by hours to get count per hours data like below,

[{
    '$match':{
       '$and':[ {'createdAt':{'$gte':'2021-03-08T00:00:00.000Z'}},{'createdAt':{'$lte':'2021-03-08T05:00:00.000Z'}}]
    }
},
    {
    '$project': {
      'hours': {
        '$hours': 'createdAt'
      }
    }
  }, {
    '$group': {
      '_id': {
        'hours': '$hours', 
      }, 
      'total': {
        '$sum': 1
      }, 
      'hours': {
        '$hours': '$hours'
      }
    }
  }
]

also, you can add any other condition using match for other filters that you need.

MongoDB aggregate count the number of products between hour range

1 Answers1