Searching dates with Integromat

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

Hi Spencer, I hadn’t seen MongoDB Integration | Workflow Automation | Make 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

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