Searching dates with Integromat

MongoDB Atlas Atlas Search
1

Hi there,

Is it possible to search dates within the mongodb Integromat/Make module?

Here is the search query I’m trying to run

{
   "$and":[
         {
         "inquiryDate":{
            "$gte":ISODate("2022-04-01"),
            "$lt":ISODate("2022-05-01")
         },
         "reasonLost":{
            "$ne":"Existing Customer"
         }
      }
   ]
}

This is the error I’m getting in integromat:
“Query: invalid JSON. Unexpected token I in JSON at position 59”

Thank you!

1 Like
2

Hi Spencer, I hadn’t seen https://www.make.com/en/integrations/mongodb before to be honest with you: super interesting that they ave search called out. It’s unclear to me if this is using mongodb’s legacy text search indexes or Atlas Search: do you have a relationship with the Make folks to push to get to the bottom of this? we’d be happy to help/reach out to them separately

1 Like
3

Unfortunately, this is not actually a $search query. The good thing about the question the question, though, is that the reasonLost field suggests that this query is a good fit for Atlas Search and would likely be lightning fast. I’ve put some details on how to solve this problem with Atlas Search below.

It is possible to search by dates but you first need to create a search index with date type for inquiryDate and string for reasonLost. Then it will be relatively easy. As an FYI, $and becomes must in $search and $ne becomes mustNot in Atlas Search.

An index definition that would satisfy this query is:

{
  "analyzer": "lucene.english",
  "searchAnalyzer": "lucene.english",
  "mappings": {
    "dynamic": false,
    "fields": {
      "reasonLost": {
          "type": "string"
        },
      "inquiryDate": {
        "type": "date"
      }
    }
  }
}

And a query, assuming your index is named default that would satisfy the requirement would be:

{
  index: 'default',
  compound: {
    must: [{
      range: {
        'gte':ISODate('2022-04-01'),
        'lt':ISODate('2022-05-01'),
        path: 'inquiryDate'
      }
    }],
    mustNot: [{
      string: {
        query: 'Existing Customer',
        path: 'reasonLost'
      }
    }]
  }
}
1 Like
4

Hey Andrew, another frustrated Integromat/Make Mongo DB User here. I wanted to share what worked for me:

{
"stringField": "62c66186eb4ce3f6851bb798",
   "dateField": {
      "$lt": "2022-11-04T23:28:34.201Z",
      "$gt": "2022-10-05T23:28:34.201Z"
   }
}

The dates should be in ISO 8601 format. Make.com’s built-in {{now}} and date functions like {{formatDate()}} should work.