How can I do DynamoDB limit after filtering?

Question

I would like to implement a DynamoDB Scan with the following logic:

Scanning -> Filtering(boolean true or false) -> Limiting(for pagination)

However, I have only been able to implement a Scan with this logic:

Scanning -> Limiting(for pagination) -> Filtering(boolean true or false)

How can I achieve this?

Below is an example I have written that implements the second Scan logic:

    var parameters = {
        TableName: this.tableName,
        Limit: queryStatement.limit
    };
    if ('role' in queryStatement) {
        parameters.FilterExpression = '#role = :role';
        parameters.ExpressionAttributeNames = {
            '#role': 'role'
        };
        parameters.ExpressionAttributeValues = {
            ':role': queryStatement.role
        };
    }
    if ('startKey' in queryStatement) {
        parameters.ExclusiveStartKey = { id: queryStatement.startKey};
    }

    this.documentClient.scan(parameters, (errorResult, result) => {
        if (errorResult) {
            errorResult._status = 500;
            return reject(errorResult);
        }

        return resolve(result);
    });

This codes works like second one.

Scanning -> Limiting -> Filtering

Answer 1

Use --max-items=2 instead of --limit=2, max-items will do limit after filtering.

Sample query with max-items:

aws dynamodb query --table-name=limitTest --key-condition-expression="gsikey=:hash AND gsisort>=:sort"  --expression-attribute-values  '{ ":hash":{"S":"1"},    ":sort":{"S":"1"}, ":levels":{"N":"10"}}'   --filter-expression="levels >= :levels"  --scan-index-forward  --max-items=2  --projection-expression "levels,key1" --index-name=gsikey-gsisort-index

Sample query with limit:

aws dynamodb query --table-name=limitTest --key-condition-expression="gsikey=:hash AND gsisort>=:sort"  --expression-attribute-values  '{ ":hash":{"S":"1"},    ":sort":{"S":"1"}, ":levels":{"N":"10"}}'   --filter-expression="levels >= :levels"  --scan-index-forward  --limit=2  --projection-expression "levels,key1" --index-name=gsikey-gsisort-index

Answer 2

The DynamoDB LIMIT works as mentioned below (i.e. second approach in your post) by design. As it works by design, there is no solution for this.

LastEvaluatedKey should be used to get the data on subsequent scans.

Scanning -> Limiting(for pagination) -> Filtering(boolean true or false)

In a request, set the Limit parameter to the number of items that you want DynamoDB to process before returning results.

In a response, DynamoDB returns all the matching results within the scope of the Limit value. For example, if you issue a Query or a Scan request with a Limit value of 6 and without a filter expression, DynamoDB returns the first six items in the table that match the specified key conditions in the request (or just the first six items in the case of a Scan with no filter). If you also supply a FilterExpression value, DynamoDB will return the items in the first six that also match the filter requirements (the number of results returned will be less than or equal to 6).

For either a Query or Scan operation, DynamoDB might return a LastEvaluatedKey value if the operation did not return all matching items in the table. To get the full count of items that match, take the LastEvaluatedKey value from the previous request and use it as the ExclusiveStartKey value in the next request. Repeat this until DynamoDB no longer returns a LastEvaluatedKey value.

Answer 3

If there is just one field that is of interest for the pagination you could create an index with that field as a key. Then you do not need to recurse for the number of items in the limit.