Query DynamoDB secondary index by date range returns "Query key condition not supported"-CodePudding

CURRENTLY

I am trying to retrieve a subset of data filtered by date range transDate (secondary index) from DynamoDB using Lambda.

Function:

const AWS = require("aws-sdk");
var dynamodbClient = new AWS.DynamoDB.DocumentClient();

let params = {
    "TableName": "sales",
    "IndexName": "transDate-index",
    "KeyConditionExpression": "#tdate BETWEEN :v_start AND :v_end",
    "ExpressionAttributeNames": {
        "#tdate": "transDate"
    },
    "ExpressionAttributeValues": {
        ":v_start": "2021-07-29T00:00:00",
        ":v_end": "2021-08-01T24:00:00"
    }
}

await new Promise((resolve, reject) => {
   dynamodbClient.query(params, function (err, data) {
      if (err) {
        console.error(
           "Unable to query. Error:",
           JSON.stringify(err, null, 2)
        );
        reject(err);
      } else {
        console.log("Query succeeded.");
        resolve(data);
      }
  });
});

Secondary Index:

Name: transDate-index
Partition key:  transDate (String)
Sort key: -
Read capacity:  On-demand
Projected attributes: Include: total, pluGroup, plu, transDate
Size: 0 Bytes
Item count: 0

ISSUE

I receive the following error:

"message": "Query key condition not supported",
"code": "ValidationException"

Notes

I assume the Size and Item Count for the secondary index are both showing 0 because they haven't been populated yet (i.e. newly created index)

QUESTION

How can I get my query to work?

CodePudding user response：

As the name implies, a partition key (aka a HASH key) is used to partition your data into multiple parts. It cannot be sorted or queried by range. To do that, you would need to make it a sort key (aka a RANGE key), and choose something else as a partition key (as a partition key is required while a sort key is optional).

Edit: DynamoDB Query only supports querying on a single partition, thus a partition key is also required in the KeyConditionExpression:

"KeyConditionExpression": "partitionKeyName = :partitionkeyval AND #tdate BETWEEN :v_start AND :v_end"

If you need to query your whole table, then in your secondary index, every record needs to have the same partition key value (i.e. there is only one partition).