Post endpoint workflow mongodb find

Help
,
1

Hi,

I have a post workflow that is searching a mongodb for matching devices. I’m struggling with including a $gt and $lt for a date range in the query.

workflow:
endpoint
mongodb: find with this as the json template:

{
“serial”: “{{data.body.serial}}”,
“chamber”: “{{data.body.chamber}}”,
“time”: “{$gt: ISODate(’{{data.body.startDate}}’)}”
}

if I take out the “time” field it returns exactly what I expect–all matches for serial and chamber. But every format I try with the “time” field just returns nothing. I’ve tried everything I can think of with the $gt section but can’t land on one that works. any ideas?

3

Hey @Tim_Brennan,

This is a difficult question as I don’t know much about your MongoDB setup. If you use the query you sent with a hardcoded date, rather than ISODate('{{data.body.startDate}}'), do you get any results?

Also, it looks like you’ve got a little bit of incorrect JSON. With your query, you are trying to compare time to the string "{$gt: ISODate(’{{data.body.startDate}}’)}” which will not work. Your query should look like this:

{
 “serial”: “{{data.body.serial}}”,
 “chamber”: “{{data.body.chamber}}”,
 “time”: {
   "$gt": ISODate(’{{data.body.startDate}}’)
 }
}

If time is a standard Mongo time field, then I would suggest you use:

{
  “serial”: “{{data.body.serial}}”,
  “chamber”: “{{data.body.chamber}}”,
  “time”: {
    "$gt": {{formatDate data.body.startDate 'x'}}
  }
}

The above does assume that data.body.startDate is a date or a date-like string.

Let me know if this works for you.

Thank you,
Heath

5

thanks Heath. I’m going to give those a try. So far the first bit of code you gave me fixing my JSON error is giving me an error in the MongoDB query field.

if I put a hardcoded date into my original query nothing changes, but it makes sense if you are saying I’m JSON was formatted incorrectly. but I can’t get either of your suggestions to work.

my mongodb setup is pretty simple. here’s the form of one of the records:

{
    "_id": {
        "$oid": "60eef##############"
    },
    "serial": "60ce###############",
    "chamber": "landfill",
    "weight": 36,
    "time": {
        "$date": "2021-07-14T15:13:08.679Z"
    }
}

If I use Mongo Compass or even am logged into mongodb.com looking at my collection, I can use the following filter to get the results I expect: {“time”: {"$gt": ISODate(‘2021-07-17’)}}

7

Hi Heath,

sorry to bother, but I still can’t get this to work. I tried copying and pasting your suggestions in just to see if it would give me progress I could build on, but your first suggestion looks like this when I put it into the mongoDB query argument box:

image
image916×672 40.2 KB

any suggestions?

8

@Tim_Brennan

My apologies. It looks like I forgot quotations around ISODate(’{{data.body.startDate}}’).

So, your JSON should look like:

{
 “serial”: “{{data.body.serial}}”,
 “chamber”: “{{data.body.chamber}}”,
 “time”: {
   "$gt": “ISODate(’{{data.body.startDate}}’)”
 }
}

Let me know if this works.

Heath

9

thanks Heath! definitely cleared up the json error in the module, but isn’t returning anything from mongo. I just can’t figure out why it’s not. the same query directly in mongo compass returns exactly what I want. is there a way to see the behind the scenes of what’s happening when the endpoint is hit in terms of how it’s requesting from mongo? the only different in mongo Compass is that it’s in a single line, but I’ve tried the “string template”. I’ve got the paths set for the response from mongo and the error if there is one. but with your example there is no response from mongo and no error.

10

@Tim_Brennan,

Have you tried the second option I’ve given?

{
  “serial”: “{{data.body.serial}}”,
  “chamber”: “{{data.body.chamber}}”,
  “time”: {
    "$gt": {{formatDate data.body.startDate 'x'}}
  }
}

ISODate is an option that you can only use in the MongoDB console. You will need to send just the date along in the query.

Let me know if this works!

Thank you,
Heath

11

I’ll try it again now. before it didn’t return anything, but I’ll give it a shot. data.body.startDate is a date like format in my payload : “2021-07-17” for example. should the ‘x’ in the formatDate handlebar be x or should I try putting my format (YYYY-MM-DD) in for x?

12

@Tim_Brennan,

The ‘x’ is correct. It is a Moment.js format string. You can certainly try YYYY-MM-DD for x instead, also, if x does not work.

Let me know what happens!

Thank you,
Heath

13

still no results from mongo. if i try to put in ‘YYYY-MM-DD’ or that without the single quotes or that with double quotes the block errors and I can’t save. with ‘x’ it saves, but returns no results. no errors being returned either. I expect 23 records from mongo with what the data.body payload contains.

14

@Tim_Brennan,

I didn’t forget about you! :slight_smile:

I did some testing on a collection I have. Here is what worked for me in the MongoDB Node:

{
    "createdOn": {
        "$gt": {
            "$date": "{{formatDate data.date 'x'}}"
        }
    }
}

So the next query you will try is:

{
  “serial”: “{{data.body.serial}}”,
  “chamber”: “{{data.body.chamber}}”,
  “time”: {
    "$gt": {
      "$date": "{{formatDate data.body.startDate 'x'}}"
    }
  }
}

Let me know if this works!

Thank you,
Heath

16

genius. thank you Heath. I never would have gotten to the momentjs format. i thought I tried every iteration of time, date, time–>date, everything. thank you so much for your help as always.