Go DynamoDB Query returns no item with Filter and Limit=1

Question

I've following dynamoDB table

user_id	date	game
user1	2021-12-06 14:36:46	game1
user1	2021-12-06 15:36:46	game1
user1	2021-12-07 11:36:46	game2
user1	2021-12-07 12:36:46	game2

partition key: user_id

sort key: date

I want to Query the latest entry of user for game game1 (Which is the second item from table with date 2021-12-06 15:36:46). I can achieve this from code as follows;

expr, _ := expression.NewBuilder().
    WithKeyCondition(expression.Key("user_id").Equal(expression.Value("user1"))).
    WithFilter(expression.Name("game").Equal(expression.Value("game1"))).
    Build()
var queryInput = &dynamodb.QueryInput{
    KeyConditionExpression:    expr.KeyCondition(),
    ExpressionAttributeNames:  expr.Names(),
    ExpressionAttributeValues: expr.Values(),
    FilterExpression:          expr.Filter(),
    ScanIndexForward:          aws.Bool(false),
    TableName:                 aws.String(table),
}

This returns me all items of user user1 for game game1. Problem occurs when I apply limit=1 Limit: aws.Int64(1) in QueryInput, it returns nothing. Could someone explain why is that so ?

When I change Limit: aws.Int64(4) (total number of items in table), only then the query returns single expected item. How is this limit working ?

Do I need to use game as GSI ?

user_7223663 · Answer

The limit on a DDB parameter is applied before your filter expressions.

Essentially with a limit of 1, it retrieves 1 record, then applies the filters and returns you the items that match (0).

See https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Query.html#Query.Limit for more details, copied in case link breaks

Limiting the Number of Items in the Result Set

The Query operation allows you to limit the number of items that it reads. To do this, set the Limit parameter to the maximum number of items that you want.

For example, suppose that you Query a table, with a Limit value of 6, and without a filter expression. The Query result contains the first six items from the table that match the key condition expression from the request.

Now suppose that you add a filter expression to the Query. In this case, DynamoDB reads up to six items, and then returns only those that match the filter expression. The final Query result contains six items or fewer, even if more items would have matched the filter expression if DynamoDB had kept reading more items.

Go DynamoDB Query returns no item with Filter and Limit=1

1 Answers