Mongodb Aggregate - Grouping by hour for today

Question

I was wondering if someone could help me get my aggregation function right. I'm trying to count load of visits of my Fitness. I have Visits table where every visit (from, to) dates are saved. Also sub-visits to some services saved too.

[{
  "from": ISODate("2020-01-17T10:23:27.645Z"),
  "to": ISODate("2020-01-17T12:23:28.760Z"),
  "visits": [
    {
      "from": ISODate("2020-01-17T10:23:27.646Z"),
      "to": ISODate("2020-01-17T10:30:28.760Z"),
      "service": ObjectId("5e05f17d6b7f7920d4a62403")
    },
    {
      "from": ISODate("2020-01-17T10:30:29.760Z"),
      "to": ISODate("2020-01-17T12:23:28.760Z"),
      "service": ObjectId("5d05f17dt57f7920d4a62404")
    }
  ],
  ...
},
{
  "from": ISODate("2020-01-17T10:40:00.000Z"),
  "to": ISODate("2020-01-17T11:30:28.760Z"),
  "visits": [
    {
      "from": ISODate("2020-01-17T10:40:00.000Z"),
      "to": ISODate("2020-01-17T11:30:28.760Z"),
      "service": ObjectId("h505f17s6b2f7920d4a6295y")
    }
  ],
  ...
}
]

I wanted to reach the result for today from 00:00 if current time is 13:35 then get until 13:00 like :

[{
  'date': '2020-01-18T00:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T00:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T00:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T01:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T02:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T03:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T04:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T05:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T06:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T07:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T08:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T09:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T010:00:0.000Z'
  'visits': 2
},
{
  'date': '2020-01-18T11:00:0.000Z'
  'visits': 2
},
{
  'date': '2020-01-18T12:00:0.000Z'
  'visits': 1
},
{
  'date': '2020-01-18T13:00:0.000Z'
  'visits': 0
}]

Tried too many examples, but nothing worked, Please help !

Valijon · Accepted Answer · 2020-01-18T11:17:49.707

EXPLANATION

If we use $$NOW, we get current date. If you setup currDate, today, nextDay manually, MongoDB aggregation will limit desired values.
Now, we calculate how many hours from today (yyyy-MM-dd 00:00:00) - currDate (yyyy-MM-dd 13:35:00) ~ 13 hours and with $range operator create array
We flatten interval and apply filtering

You may use such query:

db.collection.aggregate([
  {
    $addFields: {
      nextDay: {
        $add: [
          {
            $dateFromString: {
              dateString: {
                $substr: [
                  {
                    $toString: "$$NOW"
                  },
                  0,
                  10
                ]
              },
              format: "%Y-%m-%d"
            }
          },
          {
            $multiply: [
              24,
              60,
              60,
              1000
            ]
          }
        ]
      },
      today: {
        $dateFromString: {
          dateString: {
            $substr: [
              {
                $toString: "$$NOW"
              },
              0,
              10
            ]
          },
          format: "%Y-%m-%d"
        }
      },
      currDate: {
        $dateFromString: {
          dateString: {
            $substr: [
              {
                $toString: "$$NOW"
              },
              0,
              13
            ]
          },
          format: "%Y-%m-%dT%H",
          timezone: "-01"
        }
      }
    }
  },
  {
    $addFields: {
      interval: {
        $range: [
          0,
          {
            $add: [
              {
                $divide: [
                  {
                    $subtract: [
                      "$currDate",
                      "$today"
                    ]
                  },
                  {
                    $multiply: [
                      60,
                      60,
                      1000
                    ]
                  }
                ]
              },
              1
            ]
          },
          1
        ]
      }
    }
  },
  {
    $unwind: "$interval"
  },
  {
    $addFields: {
      date: {
        $add: [
          "$today",
          {
            $multiply: [
              "$interval",
              60,
              60,
              1000
            ]
          }
        ]
      },
      nextHour: {
        $add: [
          "$today",
          {
            $multiply: [
              {
                $add: [
                  "$interval",
                  1
                ]
              },
              60,
              60,
              1000
            ]
          }
        ]
      }
    }
  },
  {
    $project: {
      _id: 0,
      date: 1,
      today: 1,
      nextDay: 1,
      visits: {
        $reduce: {
          input: "$visits",
          initialValue: 0,
          in: {
            $add: [
              "$$value",
              {
                $cond: [
                  {
                    $or: [
                      {
                        $and: [
                          {
                            $gte: [
                              "$$this.from",
                              "$date"
                            ]
                          },
                          {
                            $lte: [
                              "$$this.from",
                              "$nextHour"
                            ]
                          }
                        ]
                      },
                      {
                        $and: [
                          {
                            $lte: [
                              "$date",
                              "$$this.to"
                            ]
                          },
                          {
                            $gte: [
                              "$date",
                              "$$this.from"
                            ]
                          }
                        ]
                      }
                    ]
                  },
                  1,
                  0
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    $match: {
      $expr: {
        $and: [
          {
            $gte: [
              "$date",
              "$today"
            ]
          },
          {
            $lte: [
              "$date",
              "$nextDay"
            ]
          },

        ]
      }
    }
  },
  {
    $group: {
      _id: "$date",
      visits: {
        $sum: "$visits"
      }
    }
  },
  {
    $sort: {
      _id: 1
    }
  }
])

MongoPlayground

For JS setup, click here

You may try this solution if you want more elegant way

Take a look [https://mongoplayground.net/p/tYDbBghxn16](https://mongoplayground.net/p/tYDbBghxn16) — Valijon, Jan 18 '20 at 09:51
Sure, thank you for helping me. Now I have last problem, it is getting the load of my Fitness. Like if my visit was from 10:00 to 12:30 I needed to show that at 10:00 1 visit, at 11:00 1 visit, and at 12:00 also 1 visit. How can I reach this logic? — Azamat Ahunjanov, Jan 18 '20 at 10:08
Can you setup MongoPlayground and click "Share" button. I need to see what is your input data — Valijon, Jan 18 '20 at 10:13
https://mongoplayground.net/p/o5thxBQPYEb there I need to get visit for 10, 11 and 12 hours. So it gives me load of people of my Fitness because person was in my fitness for 3 hours — Azamat Ahunjanov, Jan 18 '20 at 10:16
Isn't correct output result? https://mongoplayground.net/p/irtKhgUQJr5 — Valijon, Jan 18 '20 at 10:24
It is but why it is 2 visits for 2020-01-17T10:00:00Z? It shoulb be 1 visit. We can ignore subvisits. Like there https://mongoplayground.net/p/0lJGGh5jhBZ — Azamat Ahunjanov, Jan 18 '20 at 10:26
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/206190/discussion-between-valijon-and-azamat-ahunjanov). — Valijon, Jan 18 '20 at 10:27

Mongodb Aggregate - Grouping by hour for today

1 Answers1

EXPLANATION