Dynamic Queries for MongoDB Atlas Charts

MongoDB Atlas MongoDB Charts
1

I have a collection called MachineData, which holds the data of all the machines, and the dates they were made. Each document has a field called makeMonth and makeYear, which would have values of “Apr” and “2023”, respecitvely. I want to have an email that sends out monthly on the first of each month, and grabs the data for the reporting month/year (so in this case, on Aug. 1st all machines in July 2023 would be graphed).

This is rather simple to do without having a dynamic query that will know the current month/year, just by doing

{"makeMonth": "Apr", "makeYear": "2023"}

I was not sure if it is even possible to grab the current month/year and compare it to the field within the data set. As well, I know how to set up the charts to go out via email, so that is not the issue. Issue stems from creating the correct query, if anyone can lead me in the right direction. I was looking at ISODATE, but not sure how I would do that, and the document would need to be updated to match that string created by ISODATE, which is not too much of an issue.

As well, I do not really mind if the email has to go out on the last day of the month, just to make it easier grabbing the date, really would just like to get the query to be dynamic with the changing month/year, so we do not have to manually edit the query once a month via the interface.

Thanks in advance for any leads!

2

I answered something similar recently that may be help, you can add a custom pipeline to the report query:

You should be able to do something similar in your query that the report uses.

3

Awesome, thanks @John_Sewell ! I will give it a look.

4

@John_Sewell

So I have the query that can grab the date, although one thing I am having trouble with is the start point.

So right now I can grab all machines that are made on August 11th, 2023. Although, how would I get something that puts a filter of August 1st, 2023 through August 11th, 2023? Not sure if I would have to do something like grab the current month/year within my code, put it into a document within the collection and check that document using an expression (?) in the query? Sorry for the questions if they are elementary…

Edit:

It would be something like this, correct?

db.monthlyBudget.find( { $expr: { $gt: [ “$spent” , “$budget” ] } } )

Although like this for the example:

{
  $expr: {
    $and: [
      {
        $lte: [
          '$date',
          {
            $dateFromString: {
              dateString: {
                $dateToString: {
                  date: '$$NOW',
                  format: '%Y-%m-%d'
                }
              }
            }
          }
        ]
      },
      {
        $gte: ['$date', '$beginningDate']
      }
    ]
  }
}

Where ‘beginningDate’ field is within a document within the collection, that will get updated every time the application itself (software which interacts with the DB), gets updated to hold the Month/Year where we start, and then the $$NOW variable will grab until current time.

5

Note that the easiest way to filter data for the current or previous month is to drag a date field into the Filters pane and choose the Period option:

image

In your case the challenge may be that you don’t actually store the relevant data in a date field, but you could probably get around this by putting a $set in the query bar and using $dateFromString to assemble it from its component paths.

Tom

6

@tomhollander

Thanks for the input Tom! So in order to get to this, I would need to be using ISODATE function in the date field for it to be relevant?

Otherwise, you would be using the $set and $dateFromString to create basically variables within the query to probably query the data?

Thanks! Sorry if the questions are basic…just getting started with Mongo!

7

IsoDate() is just a helper function to help you can use to create constant date values in your queries. You can also use the normal JavaScript Date() function for this purpose. I don’t see a need to use either here.

The $set pipeline stage creates a calculated field in your pipeline that can set a new value derived from other values in each document. In your case I suggested using the $dateFromString to assemble the Date-typed field since it provides options to parse the month name which you mentioned was in your data.

So I’d expect your query to look something like (note, not tested):

[
  { 
    $set: {
      parsedDate: {
        $dateFromString: {
          dateString: { $concat: [ "$makeMonth", " 01 ", "$makeYear" ] },
          format: "%b %d %Y"
        }
      }
    }
  }
]
8

@tomhollander,

I wanted to make this easier for the future so I am trying to correctly format the dates of the documents. I went ahead and added this within my Java code

LocalDateTime currentDateTime = LocalDateTime.now();
DateTimeFormatter format = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
String isoFormattedDate = currentDateTime.format(format);

and received the expected output, which now the collection has a field called isoFormattedDate, which has

isoFormattedDate: "2023-08-21T09:27:32.581Z"

within the document, along with other fields.

Although, when I go to create a chart first thing I notice is the isoFormattedDate field is not present within the collection, so I press “Add Fields” and then look it up explicitly and it adds it in. Following this, when I go to move that field into the “Filter” category, I do not receive what you were showing me in a previous reply. I get this

image

Is it because the date field is still not correctly formatted? Thanks.

9

The issue here is that you are storing your date as a string. No matter how you format it, a string is a different data type to a date, and you can’t do any kind of date arithmetic on it. If you put your Java currentDateTime directly into the MongoDB document it should be persisted with the correct date type.