Is Filter expression in DynamoDB .NET query applied after "page limit" condition is applied?

Question

I am writing a Dynamo Db .NET query with both "Limit" as well as "Filter Expression" as shown below:

var search = testTable.Query(new QueryOperationConfig
                {
                    Filter = new QueryFilter("Name", QueryOperator.BeginsWith,  "B"),
                    IndexName = "IsActive-gsi",
                    Limit = 5,
                    PaginationToken = paginationToken,
                });

I expect that filter expression should be evaluated first and then limit condition is applied to the filtered result. But query seems to work in other way. That is, Limit condition is getting applied first and then filter expression is applied on the limited result. Due to this, I am not getting the expected result.

For example, say I have 10 rows in a table (with only one column of string type). Say first 5 rows starts with letter "A" and then 6th row starts with letter "B". I have added Limit as 5 and filter expression as "BeginsWith('B')" as shown above in the query. I expect to get at least one result(6th row) but query returns zero results.

My question: Is my query wrong or does Dynamo DB works this way? If it works this way, then how to get around this issue i.e. first apply filter expression on all rows and then apply limit condition, both in a single call to database?

Iris Hunkeler · Accepted Answer · 2020-05-22 15:14:30Z

2

FilterExpression do not actually change the query itself (i.e. all elements that match your query are returned from DynamoDB), but only limit the results afterwards. See the FilterExpression documentation:

A filter expression is applied after a Query finishes, but before the results are returned.

So this means, that your observed behaviour is the expected behaviour.

I think to get the result you want, you need to make sure that the attribute on which you want to apply a filter is part of the key (i.e. probably the sort key), then you can use Key Condition Expressions.

edited May 22, 2020 at 15:14

answered Mar 2, 2020 at 15:12

Iris Hunkeler

2082 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AMIT SHARMA Over a year ago

Thanks for the answer. This explains why I am not getting the expected result. Clearly, I have to use Key Condition Expression and in case of querying for multiple attributes I may need to use different approach as suggested, for ex, here: aws.amazon.com/blogs/database/…

Collectives™ on Stack Overflow

Is Filter expression in DynamoDB .NET query applied after "page limit" condition is applied?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related