Amplify-cli: ElasticSearch date range query

[quorth0n]

Is your feature request related to a problem? Please describe.
To the best of my knowledge, there is no way to perform a date range filter query using the @searchable directive for the AWSDate* scalars.

Describe the solution you'd like
The most optimal solution imo would be to be able to use a range or between key to do so. Using a DynamoDB SCAN operation, I'm able to do this:

      graphqlOperation(listPosts, {
        filter: {
          createdAt: {
            between: [yesterday.toISOString(), today.toISOString()],
          }
        }
      })

But this is expensive, not performant in larger tables, and I lose out on the complex querying capabilities ES offers.

Describe alternatives you've considered
Probably the easiest way currently is to use a range query on an Int storing epoch time. Alternatively, since the AWSDateTime scalar is just a String under the hood, a regexp could potentially be used. But that's gross.

[davidbiller]

So i use a String and new Date();
I can sort on this on elasticSearch.
What is advantage of AWSDATE?

[quorth0n]

@davidbiller how do you sort this on ES? Under the hood AWSDate is just a String, so your implementation should be the same.

[davidbiller]

@davidbiller how do you sort this on ES? Under the hood AWSDate is just a String, so your implementation should be the same.

{
      sort: [{ createdAt: "desc" }],
      size: 35,
      query: {
        bool: {
          must: [
            {

mhhh i guess i didnt undertand you... you want query in a range of dates?

Did you tried this?

{
    "query": {
        "range" : {
            "timestamp" : {
                "gte" : "now-1d/d",
                "lt" :  "now/d"
            }
        }
    }
}

[quorth0n]

@davidbiller You're correct. Thanks for the tip, but according to docs the String type (and by extension AWSDate) doesn't support the gte / lt operations: https://docs.amplify.aws/cli/graphql-transformer/directives#usage-4

[davidbiller]

@davidbiller You're correct. Thanks for the tip, but according to docs the String type (and by extension AWSDate) doesn't support the gte / lt operations: https://docs.amplify.aws/cli/graphql-transformer/directives#usage-4

If it’s not working, you can write a custom resolver.

[quorth0n]

@davidbiller You're correct. Thanks for the tip, but according to docs the String type (and by extension AWSDate) doesn't support the gte / lt operations: https://docs.amplify.aws/cli/graphql-transformer/directives#usage-4

If it’s not working, you can write a custom resolver.

Ofc, but something as simple as this should really be built into the framework imo. Unless I'm missing something; I've seen a few SO posts regarding using a between operation in an ES query but there is nothing referencing this in the docs anymore.

[davidbiller]

@davidbiller You're correct. Thanks for the tip, but according to docs the String type (and by extension AWSDate) doesn't support the gte / lt operations: https://docs.amplify.aws/cli/graphql-transformer/directives#usage-4

If it’s not working, you can write a custom resolver.

Ofc, but something as simple as this should really be built into the framework imo. Unless I'm missing something; I've seen a few SO posts regarding using a between operation in an ES query but there is nothing referencing this in the docs anymore.

Custom resolver:

## Query.nearbyJobs.req.vtl
## Objects of type Todo will be stored in the /todo index

#set( $indexPath = "/job/doc/_search" )
{
  "version": "2017-02-28",
  "operation": "GET",
  "path": "$indexPath.toLowerCase()",
  "params": {
    "body": $util.toJson($ctx.args.body)
  }
}

So you can query whatever you want with his resolver.

[quorth0n]

After some closer inspection, it seems it is possible to do a date range query natively. Thank you to @davidbiller for pointing me in the right direction.

      graphqlOperation(searchPosts, {
        filter: {
          or: [
            {
              createdAt: {
                gte: yesterday.toISOString(),
              },
            },
            {
              createdAt: {
                lte: today.toISOString(),
              }
            }
          ]
        }

After digging around in Amplify source I found the gt* and lt* operators are supported by the String scalar, in contrast to what the docs suggest. I guess I would change my recommendation at this point to documenting this feature.

[quorth0n]

Also, according to https://github.com/aws-amplify/amplify-cli/issues/1044#issuecomment-575212129, you can override the relevant input to allow the range operation for Strings. Perhaps this functionality should be worked into Amplify core since it seems to be the more performant approach?

[davidbiller]

Also, according to #1044 (comment), you can override the relevant input to allow the range operation for Strings. Perhaps this functionality should be worked into Amplify core since it seems to be the more performant approach?

I don’t know, my solution is from elasticSearch itself. A real range operation. Amplify use a filter. Don’t know if it is the same in the end on ES

[SwaySway]

Hello @quorth0n
We do have a PR open for this, however this merging this PR would imply a breaking change as the codegen types would change from SearchableStringFilterInput to SearchableDateFilterInput. We are looking at how to address this.

[edwardfoyle]

This has been released in CLI v 4.32.0