Dynamodb query with filters returns no items

Question

I have this table:
| id(string)|message(string) | created(number)| completed(boolean)|
|-------- |-------- | --------- |-------- |
|123|Instruction| 1686731293161|-|
|...|...| ...|...|
|129|Instruction| 1686821275413|false|

`Partition key`: message

`Sort key`: created

`Projected keys`: All

In summary for the table:
- some entries does not have message and completed attributes
- even though message is used as partition key in GSI, this is ok for my use case

I tried to query using aws-sdk dynamodb client with this params:
```{
  TableName: "test-idStatus",
  IndexName: "messageCreatedIndex",
  KeyConditionExpression: "#message = :message and #created BETWEEN :createdfrom AND :createdto",
  ExpressionAttributeNames: {
    "#message": "message",
    "#created": "created",
    "#completed": "completed",
  },
  ExpressionAttributeValues: {
    ":messageFunction": "Instruction",
    ":createdfrom": 1686817506000, // 2023-June-15 08:25
    ":createdto": 1686821106000,   // 2023-June-15 09:25
    ":completed": "false",
  },
  ScanIndexForward: false,
  Limit: 5,
  Select: "ALL_ATTRIBUTES",
  FilterExpression: "#completed = :completed",
}
```
This returned entries

But when I changed the `ExpressionAttributeValues` of:

`createdfrom`:  1686731286000  //  (2023-June-14 08:25)

This did not return any entries (but returns a LastEvaluatedKey).

Which is weird since June-15 returned entries and is just a subset of June-14 to June-15 entries

I also tried to replicate this in the AWS console dynamoDB. The same thing happened.

Then I also tried removing `ScanIndexForward`, now it returned entries.

Answer

What's happening here is that you set a FilterExpression and a Limit. What happens now is that DynamoDB reads 5 items as that is your Limit and then applies your filter to those 5 items but none match the expression so you get an empty response.

Changing the date is simply changing the 5 items read and it's just luck that they match your filter. The same goes for changing the scan direction, you're just changing the first 5 items.

https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Query.html
> 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. If LastEvaluatedKey is present in the response, you will need to paginate the result set. For more information, see Paginating the Results in the Amazon DynamoDB Developer Guide.

Answer

Hi there
please refer to the following doc for guidance
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/SQLtoNoSQL.ReadData.Query.html

id(string)	message(string)	created(number)	completed(boolean)
123	Instruction	1686731293161	-
...	...	...	...
129	Instruction	1686821275413	false

Dynamodb query with filters returns no items

Relevant content