DynamoDB: filter and sort the same field?

user_14002615 0
Viewed 1199

I have a table with Local Index like below.

how to make a query like startDate < InvoiceDate < endDate and sort the result descending by InvoiceDateLocalIndex ??

as far as I know, to sort the result I need to using InvoiceDateLocalIndex. But if I'd used , I got the error "Filter Expression can only contain non-primary key attributes: Primary key attribute: InvoiceDate"

"IndexName": "InvoiceDateLocalIndex",
 "KeySchema": [
  {
     "AttributeName": "FinanceYear",
     "KeyType": "HASH"
  },
  {
     "AttributeName": "InvoiceDate",
     "KeyType": "RANGE"
  }
]
amazon-dynamodb-index dynamodb-queries amazon-dynamodb
1 Answers

Indeed, you can't use a FilterExpression over the range key (InvoiceDate). But the reason for that is not that it couldn't be done - the reason why this is forbidden is to prevent you from making a costly mistake:

To request only a range of your range key, you should use KeyConditionExpression, not FilterExpression.

Using KeyConditionExpression is faster and cheaper because DynamoDB only retrieves the matching items - whereas FilterExpression would read (and charge you) the entire partition. This is why "range keys" are called "range keys" - they allow you to efficiently request a range of this key. They are alternatively called a "sort key" because they don't just do ranges - they are actually sorted.

user_88912240
Related