Aws AppSync - query for all items between two dates

Question

I want to query all the items between two dates using AWS AppSync. This my resolver :

{
 "version" : "2017-02-28",
"operation" : "Query",
"index" : "userInfoDate-index",
"query" : {
    ## Provide a query expression. **
    "expression": "userInfoDate BETWEEN :start AND :end",
    "expressionValues" : {
        ":start" : { "S" : "${ctx.args.start}" },
        ":end" : { "S" : "${ctx.args.end}" }
    }
}

//Schema
type UserQuestionInfo @model {
  id: ID!
  userId: String!
  userName: String!
  userInfoDate : String!
  weight: String!
  height: String!
}

type Query {
   getWeightByCreatedAt(start: String!, end: String!): UserQuestionInfo
 }

But I am getting this error :

Query key condition not supported (Service: AmazonDynamoDBv2; Status Code: 400; Error Code: ValidationException) I don't know what's wrong or this does not support between query. Please help

Is there any reason not to use AppSync scalar date types https://docs.aws.amazon.com/appsync/latest/devguide/scalars.html ? — Neill, Apr 23 '19 at 15:22
please take a look here, this is not an issue with appSync https://stackoverflow.com/questions/31830888/dynamodb-query-error-query-key-condition-not-supported — Vasileios Lekakis, Apr 24 '19 at 23:49

bolino · Answer 1 · 2022-08-24T10:27:11.013

Most probably too late but I had the same issue and figured out.

~~You cannot use BETWEEN for types that are not integers (whether String or Scalars like AWSDate).~~

EDIT: Sorry I'm wrong, you actually can use BETWEEN for String or Scalars like AWSDate. But you need a dedicated secondary DynamoDB index (like userId-userInfoDate-index) with a hash key on another field like userId where you would look for the exact key, and the range key on userInfoDate. The resolver would then look like:

{
    "version" : "2018-05-29",
    "operation" : "Query",
    "index" : "userId-userInfoDate-index",
    "query": {
        "expression": "userId =:userId AND userInfoDate BETWEEN :start AND :end",
        "expressionValues": {
            ":userId": $util.dynamodb.toDynamoDBJson($ctx.args.userId),
            ":start": $util.dynamodb.toDynamoDBJson($ctx.args.start),
            ":end": $util.dynamodb.toDynamoDBJson($ctx.args.end),
    }
}

}

That means of course you need another criteria in your query, like userId.

If you can't, you can also use a Scan query with two filters on the same key. But a Scan would probably be way slower than a Query since it would search on the whole table not using indexes.

{
    "version" : "2017-02-28",
    "operation" : "Scan",
    "filter": {
        "expression": "userInfoDate >= :start AND userInfoDate <= :end",
        "expressionValues" : {
            ":start" : $util.dynamodb.toDynamoDBJson($ctx.args.start),
            ":end" : $util.dynamodb.toDynamoDBJson($ctx.args.end)
        }
    }
}

Aws AppSync - query for all items between two dates

1 Answers1