AWS DynamoDB Scan Filter Expression Returning Empty

Question

I'm stumped trying to figure out why my scan won't return anything but [ ]. Here are my scan params:

var params = {
                TableName: tableName,
                FilterExpression: "#wager = :wager",
                ExpressionAttributeNames: {
                    "#wager": "wager"
                },
                ExpressionAttributeValues: {
                    ":wager": wager
                }
            };

My DynamoDB table works perfectly when I run a filter expression in the DynamoDB dashboard, like "wager [NUMBER] = 0.001".

Nadav Har'El Nadav Har'El · Accepted Answer · 2020-12-14T11:55:28

jellycsc and Seth Geoghegan already mentioned the two most likely explanations in comments:

First, make sure you do not call a single Scan operation, but rather do a loop to get all the pages of the scan result. The specific way to do this depends on which programing language you are using. When your filter leaves only a small subset of the results (e.g., only when wage is exactly 0.001) remember to read all the pages is critical, because the first page may be empty: DynamoDB might have read 1MB of items (the default page size), and none of them matched wager=0.001 so an empty first page is return.

Second, the wager might have a wrong type. Obviously, if you store numbers but search for a string, nothing would match, so check you didn't do that. But a more subtle problem can be how you store numbers. DynamoDB holds floating-point in an unusual manner - using decimal, not binary, digits. This means that DynamoDB can hold the number "0.001" precisely, without any rounding errors. The same cannot be said for most programming languages. On my machine, if I set a "double" variable to 0.001, the result is 0.0010000000000000000208. If I pass this to DynamoDB, the equality check will not match! This means you should make sure that wager variable is not a double. In Python, for example, wager should be set to Decimal("0.001") - note how it is constructed from the string "0.001", not from the floating-point 0.001 which already has rounding errors.

AWS DynamoDB Scan Filter Expression Returning Empty

2 Answers