Filter Expression not working in DynamoDb Table

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.

reference

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:

1. Increase the limit, also consider using pagination incase your item collection exceed 1MB.
2. Re-design your data model to include the IsPublished attribute 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" }},
                }
            };