DynamoDB Between query on GSI does not work as expected

Question

It is a jobPosts schema that has a posted_date as one of the attributes. The goal is to query all the job posts between two dates. Here is the schema for your reference:

{
  'job_id': {S: jobInfo.job_id},
  'company': {S: jobInfo.company},
  'title': {S: jobInfo.title},
  'posted_on': {S: jobInfo.posted_on},
}

posted_on' is based on ISO string (2019-11-10T10:52:38.013Z). job_id is the primary key (partition key) and since I need to query the dates, I created GSI(partition key) on posted_on. Now here is the query:

  const params = {
    TableName : "jobPosts",
    IndexName: 'date_for_filter_purpose-index',
    ProjectionExpression:"job_id, company, title, posted_on",
    KeyConditionExpression: "posted_on BETWEEN :startDate AND :endDate",
    ExpressionAttributeValues: {
        ":startDate": {S: "2019-10-10T10:52:38.013Z"},
        ":endDate": {S: "2019-11-10T10:52:38.013Z"}
    }
  };

I have one document in dynamoDB and here it is:

{
  job_id:,
  company: "xyz",
  title: "abc",
  posted_on: "2019-11-01T10:52:38.013Z"
}

Now, on executing this, I get the following error:

{
    "message": "Query key condition not supported",
    "code": "ValidationException",
    "time": "2019-11-11T06:15:37.231Z",
    "requestId": "J078NON3L8KSJE5E8I3IP9N0IBVV4KQNSO5AEMVJF66Q9ASUAAJG",
    "statusCode": 400,
    "retryable": false,
    "retryDelay": 12.382362030893768
}

I don't know what is wrong with the above query.

Update after Tommy Answer: I removed the GSI on posted_on and re-created the table with job_id as partition key and posted_on as sort key. I get the following error:

{
    "message": "Query condition missed key schema element: job_id",
    "code": "ValidationException",
    "time": "2019-11-12T11:01:48.682Z",
    "requestId": "M9E793UQNJHPN5ULQFJI2NR0BVVV4KQNSO5AEMVJF66Q9ASUAAJG",
    "statusCode": 400,
    "retryable": false,
    "retryDelay": 42.52613025785952
}

As per this SO answer, GSI should be able to query the dates using BETWEEN keyword.

jarmod jarmod · Accepted Answer · 2019-11-12T22:15:56

The answer you refer to relates to a query where the partition key has a specific value and the sort key is in a given range. It's analagous to select * from table where status=Z and date between X and Y. That's not what you're trying to do, if I read your question correctly. You want select * from table where date between X and Y. You cannot do this with DynamoDB query - you cannot query a partition key by range.

If you knew that your max range of query dates was on a given day then you could create a GSI with a partition key set to the computed YYYYMMDD value of the date/time and whose sort key was the full date/time. Then you could query with a key condition expression for a partition key of the computed YYYYMMDD and a sort key between X and Y. For this to work, the YYYYMMDD of X and Y would have to be the same.

If you knew that your max range of query dates was a month then you could create a GSI with partition key set to the computed YYYYMM of the date/time and whose sort key was the full date/time. For this to work, the YYYYMM of X and Y would have to be the same.

DynamoDB Between query on GSI does not work as expected

2 Answers