DynamoDB sort and pagination

Question

I'm trying to get data from a dynamoDB table sorted but also I want pagination because there are many many rows. This is my query params, control_id is GSI with timestamp as sortkey and id is primary key. I think what I need is to use both indexes in same query because right now this is what I get ValidationException: The provided starting key is invalid and if I remove ExclusiveStartKey it works.

params = {
 TableName: tableName,
 IndexName: 'control_id',
 KeyConditionExpression: 'control_id = :ci',
 ExpressionAttributeValues: {
  ':ci': 1
 },
 ScanIndexForward: false,
 Limit: 250,
 ExclusiveStartKey: {
  'id': lastKey
 }
}

andy mccullough · Answer 1 · 2020-06-04T10:19:25.940

3

If you you have both a partition key and sort key as your primary key, you need to supply both to the ExclusiveStartKey. Dynamo gives you two options when building a primary key, a partition key, or a composite key (made up of the partition key and sort key). When using a composite key, always need to provide both the partition key and sort key when referencing a single record in the table

From the docs (https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/API_Query_v20111205.html) -

ExclusiveStartKey - Primary key of the item from which to continue an earlier query. An earlier query might provide this value as the LastEvaluatedKey if that query operation was interrupted before completing the query; either because of the result set size or the Limit parameter. The LastEvaluatedKey can be passed back in a new query request to continue the operation from that point.

Type: HashKeyElement, or HashKeyElement and RangeKeyElement for a composite primary key.

here, HashKeyElement refers to the partition key, and RangeKeyElement refers to the sort key

try

ExclusiveStartKey: {
  'id': lastKey
  'created_at': <last key's created_at value>
 }

or it might actually be -

ExclusiveStartKey: lastkey

depending on what you have as lastKey, it might already be the composite key

edited Jun 04 '20 at 10:19

answered Jun 04 '20 at 09:18

andy mccullough

9,070
6
32
55

```id``` is my primary key, this is how my table looks [link](https://i.ibb.co/DRfrQ1d/img.jpg), ```control_id``` is 1 for every row so i can sort ASC or DESC – Mercore Jun 04 '20 at 09:48
but is `control_id` your sort key? – andy mccullough Jun 04 '20 at 10:10
from reading above, you say your timestamp is the sort key? If thats the case, your primary key is made up of both `id` an your timestamp, and therefore you need to provide both of those – andy mccullough Jun 04 '20 at 10:15
No, ```control_id``` is secondary index and ```created_at``` is sort key for ```control_id```, primary key ```id``` has no sort key – Mercore Jun 04 '20 at 10:16
so your primary key for the `control_id` index is a composite key, made of `id` as partition key and `created_at` as sort key, therefore you need to provide both to `ExclusiveStartKey`. See edit above – andy mccullough Jun 04 '20 at 10:17
i have something like this now, but still have same error `params = { TableName: tableName, IndexName: 'control_id', KeyConditionExpression: 'control_id = :ci', ExpressionAttributeValues: { ':ci': 1 }, ScanIndexForward: false, Limit: 250, ExclusiveStartKey: { 'id': lastKeyId, 'created_at': lastKeyCreatedAt } }` – Mercore Jun 04 '20 at 10:28
did you try `ExclusiveStartKey: lastkey` assuming `lastKey` is the key as it returned from the previous query? – andy mccullough Jun 04 '20 at 10:55
I tried and still not working, i send lastKey in my app, i receive first 250 elements sorted then i press load more button to get next 250 elements and throws error. – Mercore Jun 04 '20 at 11:04
maybe worth looking at the answers in https://stackoverflow.com/questions/40988397/dynamodb-pagination-with-withexclusivestartkey-on-a-global-secondary-index looks like there is some stuff around table keys vs index keys – andy mccullough Jun 04 '20 at 11:12

DynamoDB sort and pagination

1 Answers1