Filter Expression not working in DynamoDb Table
I have a DynamoDb Table named "MenuItem" which has GSI named "CategoryId-CreatedDate-index" CategoryId is the Partition Key and CreatedDate is the Sort key. I am doing the following query operation in the AWS Console and getting 28 records as shown in the below screenshot.
But when I am doing the same thing via C# code then I am getting zero row count.
var menuRequest = new QueryRequest
{
TableName = MenutableName,
IndexName = "CategoryId-CreatedDate-index",
Limit = 10,
ScanIndexForward = false,
KeyConditionExpression = "CategoryId = :v_Id",
FilterExpression = "IsPublished = :ispub",
ExpressionAttributeValues = new Dictionary<string, AttributeValue>
{
{":v_Id", new AttributeValue { N = "1" }},
{":ispub", new AttributeValue { BOOL = true }},
}
};
var menuResponse = await client.QueryAsync(menuRequest).ConfigureAwait(false);
- 최신
- 최다 투표
- 가장 많은 댓글
Issue Explained
You API is not returning item due to the use of Limit = 10 parameter.
A single Query operation will read up to the maximum number of items set (if using the Limit parameter) or a maximum of 1 MB of data and then apply any filtering to the results using FilterExpression.
So in your case, you read 10 items, apply the filter IsPublished=true, of which there are no matches, so you get returned an empty response.
ScannedCount
The number of items evaluated, before any QueryFilter is applied. A high ScannedCount value with few, or no, Count results indicates an inefficient Query operation
Count
The number of items in the response. If you used a QueryFilter in the request, then Count is the number of items returned after the filter was applied, and ScannedCount is the number of matching items before the filter was applied. If you did not use a filter in the request, then Count and ScannedCount are the same.
Resolution
To overcome this issue you can:
- Increase the limit, also consider using pagination incase your item collection exceed 1MB.
- Re-design your data model to include the
IsPublishedattribute as part of your sort key:
| GSIPK | GSISK | IsPublished |
|---|---|---|
| CategoryId123 | false#2023-09-11T20:00:000z | false |
| CategoryId123 | false#2023-09-11T21:00:000z | false |
| CategoryId123 | true#2023-09-11T17:00:000z | true |
| CategoryId123 | true#2023-09-11T10:00:000z | true |
This approach you can use Limit=10 and get exactly 10 items:
var menuRequest = new QueryRequest
{
TableName = MenutableName,
IndexName = "CategoryId-CreatedDate-index",
Limit = 10,
ScanIndexForward = false,
KeyConditionExpression = "CategoryId = :v_Id AND begins_with(GSISK, :tt)",
ExpressionAttributeValues = new Dictionary<string, AttributeValue>
{
{":v_Id", new AttributeValue { N = "1" }},
{":tt", new AttributeValue { S = "true" }},
}
};
Hi Atif,
The important thing to consider when using a Query Filter is the order that DynamoDB processes actions:
- Read items from your table
- If a filter expression is present, remove items that don't match the filter
- Return items
Your Query includes a Limit for the Query, so I suspect what's happening is that DynamoDB is reading 10 records as indicated by your "Limit=10", applying the filter to these 10 records, finds that none of them match your filter so returns (correctly) 0 items to you. In your output image you can see that "Scanned Count" is 10 showing that 10 records have been processed, but none have been returned.
I suggest having a read of the blog "When to use (and when not to use) DynamoDB Filter Expressions" as it explains what's going on and gives alternatives for how to query your data. With the information you've provided I suggest experimenting with using a new Global Secondary Index with CategoryId as the PK and IsPublished as the Sort Key. This should be a more efficient query for the use case you've described in your question, and will mean you can do a Query without using a Filter expression.
