DynamoDB query Invalid FilterExpression when filtering with BETWEEN operator

[nmajor]

I'm trying to filter dynamodb scan results based on a date range. The plan is to connect it to a date range component eventually but for now I am just trying to get a single dynamodb scan to work filtering between 2 dates.

I cant for the life of me get a BETWEEN filter to work. I keep getting the error:

"Invalid FilterExpression: Incorrect operand type for operator or function; operator or function: BETWEEN, operand type: M"

I've double checked that the updatedAt fields store a string date. And thinking maybe I was doing something wrong I recreated the query in the AWS console.

In the AWS console created a scan filter between 2 dates. And it returned results without any issue. When I look at the parameters its sending it looks like this:

{
  "TableName": "Invo...aple",
  "ReturnConsumedCapacity": "TOTAL",
  "Limit": 50,
  "FilterExpression": "#n0 BETWEEN :v0 AND :v0p2",
  "ExpressionAttributeNames": {
    "#n0": "updatedAt"
  },
  "ExpressionAttributeValues": {
    ":v0": {
      "S": "2023-01-01T20:35:22.519Z"
    },
    ":v0p2": {
      "S": "2023-02-17T20:35:22.519Z"
    }
  },
  "Select": "ALL_ATTRIBUTES"
}

I copied directly the values for FilterExpression, ExpressionAttributeNames, ExpressionAttributeValues, and Select into a dynamodb query in retool:

But no matter what I try I always get this same error:

"Invalid FilterExpression: Incorrect operand type for operator or function; operator or function: BETWEEN, operand type: M"

I also tried a more simple version of the query

It seems like its thinking updatedAt is a Map and not a string. But I dont understand why then it works in the AWS console and not in retool.

Does anyone have any ideas of what to try?

[nmajor]

Same error when running the query using the JSON editor:

[Kabirdas]

Hey @nmajor!

It looks like Retool uses the DocumentClient abstraction of the DynamoDB SDK so you may need to modify your syntax as described here. Can you try using

{   
  ":v0": "2023-01-01T20:35:22.519Z",
  ":v0p2": "2023-02-17T20:35:22.519Z"
}

instead of

{
  ":v0": {
    "S": "2023-01-01T20:35:22.519Z"
  },
  ":v0p2": {
    "S": "2023-02-17T20:35:22.519Z"
  }
}

and let me know how that works?

[nmajor]

Wow, you are a lifesaver. It works! And this also helps with some other queries I was trying to sort out. Thanks a million.