Aggregate Mongodb: Grouping documents by hour within the current day

Seeking assistance with fine-tuning my aggregation function. I am attempting to calculate the number of visits to my fitness center. In the Visits table, I have records of each visit (from, to) date and also sub-visits to certain services.

[{
  "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 aim to generate results for the current day starting from 00:00. For example, if it is currently 13:35, then count up until 13:00 as follows:

[{
  '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
}]

I've attempted various examples without success. Any assistance would be greatly appreciated!

javascriptmongodbmongooseaggregation-framework

Answer №1

SOLUTION

  1. Utilizing $$NOW will retrieve the current date. Creating manual setups for currDate, today, and nextDay in MongoDB aggregation allows for limitation of desired values.

  2. The next step involves calculating the number of hours between today (yyyy-MM-dd 00:00:00) and currDate (yyyy-MM-dd 13:35:00), which is approximately 13 hours. By utilizing the $range operator, an array is created.

  3. We then flatten the interval and apply filtering to refine the result set.

A suggested query implementation:

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
    }
  }
])

Link to view the implementation on MongoPlayground: View Here

If you prefer a JavaScript setup, click here

You can also explore this solution for a more elegant approach.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

"Rearranging the Firefox ghost image by dragging and dropping it

My drag and drop game is working perfectly on all browsers except for Firefox. In Firefox, the ghost image that appears when an item is dragged seems to be positioned very far away from the cursor. Although the ghost image can still be seen on the page, it ...

javascripthtmlcssfirefox

Callback function in Aframe not triggering

My camera motion callback function in aframe is not being called. I have registered the component like this: window.onload = function () { AFRAME.registerComponent('listener', { tick: function () { console.log("TICK IS CALLED"); ...

javascriptthree.jsaframe

Exploring Checkbox Handling in AngularJS and ng-Grid

In my AngularJS application, I am using ng-grid to display data retrieved from a server. One of the fields, "Active," is boolean and I have created a template to show it as a checkbox: $scope.gridOptions = { data: 'myData', enableCellSelection: ...

javascriptjqueryhtmlangularjs

Is it necessary to have both variables present in an if statement for it to be evaluated?

In an attempt to determine if a custom Date widget in JavaScript is empty or not, the following function is created. The challenge lies in the fact that there are multiple variations of this widget - some display M/D/Y fields, while others may only show M/ ...

javascript

AngularJS enhances user experience by allowing textareas to expand upon click

I am just starting to learn about angular.js and I'm wondering how to add a text area when clicking on an image. ....... ..... ..... </thead> <tbody> <tr ng-repeat="stu in Studentlist"> <td>{{stu.rollno}}</td> <td> ...

javascriptjqueryhtmlangularjs

Encountering an issue in Next.js when using getStaticProps: reading 'map' of undefined properties

The Image above shows the error and the code I have attempted.Server Error TypeError: Cannot read properties of undefined (reading 'map') This particular error occurred during the page generation process. Any console logs will appear in the term ...

javascriptnode.jsreactjsnext.jsgetstaticprops

Troubleshooting Vue: Why is my component not visible when using v-form and v-text-field components in Vuetify?

Attempting to implement the v-form and v-text-field components from the Vuetify node package. <template> <v-form> <v-text-field label="Test" type="foo"></v-text-field> <v-text-field label="bar&q ...

javascriptvue.jsvuetify.js

Carrying over additions in arrays using only Javascript

I'd like to implement a basic array addition with carryover. Additionally, I need to display the carryover and result values. For instance: e.g var input = [[0,0,9],[0,9,9]]; var carryover = []; var result = []; Thank you! ...

javascriptarraysmathaddition

What is the best way to query MongoDB documents based on a specific time range in Epoch time format?

I am working with a MongoDB document format that includes an UpdatedOn field containing Epoch time. I am wondering if there is a way to filter the documents in my collection so that only those updated within the past hour are returned. { "_id" : Obj ...

mongodb

Unleashing the Power of Object Destructuring in React Hooks

Here is a straightforward code snippet: import React from "react"; import { useForm } from "react-hook-form"; export default function App() { const { register, formState: { errors }, handleSubmit } = useForm(); return ( < ...

javascriptreactjs

Tips for implementing draggable elements using JavaScript and React hooks, creating a more interactive user experience

I'm working on a project where I need to incorporate draggable divs, each corresponding to an image in an array of images, into a container. The current implementation seems to function properly, but there's an issue - the dragging action only w ...

javascripthtmlreactjsdraggable

Assign CSS properties based on the value defined in the component selector in Angular 5

In order to achieve the desired goal outlined below, I am looking to utilize mat-icon in Angular/Material to access material icons. My objective is to dynamically adjust the size of these icons using the ngStyle directive or a more efficient alternative if ...

javascriptangularng-style

attempting to shift course, but finding no success

I'm attempting to include dir=rtl or direction: rtl in the CSS, but it doesn't seem to have any effect on the browser and the content still displays left to right. What steps can I take to fix this? This template is pre-made from Colorlib. It&ap ...

javascripthtmlcsstwitter-bootstrap

expanding animation featured on Wikipedia

Have you ever noticed that when you visit a Wikipedia page on Chrome and use the ctrl+scrollup or ctrl+scrolldown command, the page resizes in a smooth animation? Do you wonder how this effect is achieved? (Interestingly, in Firefox, only the links in th ...

javascriptjquery

Comparing $(document).width() to document.body.clientWidth

When it comes to fixing CSS issues on the mobile version of a responsive site, I'm torn between two declarations. Some tutorials recommend using $(document).width(), while others suggest using document.body.clientWidth. The former belongs to jquery, w ...

javascriptjquery

Resolving problems with MongoDB connections in Node (Express)

I have a situation where my Express App connects to a MongoDB server at startup and handles requests as they come in. The problem is that the MongoDB server may not be available for periods of time, causing requests to hang indefinitely until the connectio ...

node.jsmongodbexpress

Combining JSON fields and searching based on the combined value within MarkLogic

In the JSON format provided below, I have stored a person's first name and last name along with their nickname. { "Person": { "Records": [ { "nameType": "Primary", "firstName": "Sagar", "lastName" ...

javascriptjsonsearchmarklogic

Share a URL seamlessly without the need for a page refresh or AJAX request

I am looking for a way to trigger a script to save an image to the server when a link is clicked, without actually navigating to a different page. Even though I tried using e.preventdefualt to prevent the link from submitting, it didn't work as expec ...

javascriptphpjqueryajax

Can config values be dynamically set from an Excel file in Protractor?

I am currently working on parameterizing capabilities using an Excel sheet. For this task, I am utilizing the npm exceljs package for both reading and writing data. Below is a snippet of the code that demonstrates how I am trying to achieve this: //This f ...

javascriptangularjsnpmautomationprotractor

Check out the source code of the file causing the redirection

Whenever I click on a URL, it triggers a redirect using the window.location function. I am curious to examine the code within this HTML file. Unfortunately, as it is constantly redirecting, I am finding it difficult to access the source code. Is there a w ...

javascriptjquerygoogle-chrome