Using Retool date range component to query MongoDB data using aggregates not working? What I am doing wrong?

App Building
, , , ,
1

I have a simple UI on my Retool dashboard.

It contains a number of text fields which get populated with a count from the result of several different MongoDB queries.

image
image1533×126 21.3 KB

Everything is fine, I am able to see the results when I load the page. All good!

Now, I want to be able to filter the results using a date range, using the updatedAt field in my MongoDB collection.

image

I started implementing this by editing my first query (the text field on the left, in my dashboard).

My aggregation looks like this:

image
image949×373 39.6 KB

[ 
  {
    "$match": {
      "status": {
        "$eq": "translated"
      },
      "updatedAt": {
        "$gte": {{ dateRange.value.start ? moment(dateRange.value.start).toISOString() : moment("1970-01-01").toISOString() }},
        "$lte": {{ dateRange.value.end ? moment(dateRange.value.end).toISOString() : moment().toISOString()  }}
      }
    }
  },
  {
    "$count": "count"
  }
]

The logic I am following here is:

  • If no date range set at all, fetch all results (everything that is between 1970-01-01 and now())
  • If no start date set, set it to the ISO date of 1970-01-01
  • If no end date set, set it to the ISO date of today/ now.

An example of my collection & aggregation can be found here (which works perfectly fine):

My MongoDB Compass aggregation pipeline looks like this, queries of which also work perfectly fine:
image

However, my query in Retool doesn't seem to be returning any data (despite working without issue via MongoDB Compass & Mongo Playground).

image

I can't figure out where I'm going wrong.

Can anyone spot what I could be doing wrong with the Retool MongoDB query?

My date range has all of the default properties (no event handlers for now, but I expect the MongoDB query to still return results, with my $gte and $lte properties of the aggregation containing conditional operators/ 'default' values).

The format for my dateRange is: MMM d, yyyy if that makes any difference?

2

It seems my aggregation was incorrect.

The aggregation I had was:

[ 
  {
    "$match": {
      "status": {
        "$eq": "translated"
      },
      "updatedAt": {
        "$gte": {{ dateRange.value.start ? moment(dateRange.value.start).toISOString() : moment("1970-01-01").toISOString() }},
        "$lte": {{ dateRange.value.end ? moment(dateRange.value.end).toISOString() : moment().toISOString()  }}
      }
    }
  },
  {
    "$count": "count"
  }
]

But needed to be:

[ 
  {
    "$match": {
      "status": {
        "$eq": "translated"
      },
      "updatedAt": {
        "$gte": { "$date": {{ dateRange.value.start ? moment(dateRange.value.start).toISOString() : moment("1970-01-01").toISOString() }}  },
        "$lte": { "$date": {{ dateRange.value.end ? moment(dateRange.value.end).toISOString() : moment().toISOString()  }} }
      }
    }
  },
  {
    "$count": "count"
  }
]

Note the "$date" prefix before the JS {{ ... }} section.