英文:

dynamodb query with filters returns no items

问题

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

分区键: 消息<br />
排序键: 创建时间<br />
投影键: 所有<br />

对于表格的总结：

• 有些条目没有消息和已完成属性
• 即使消息在 GSI 中用作分区键，对于我的用例来说没问题

我尝试使用 aws-sdk dynamodb 客户端查询，参数如下：

  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 年 6 月 15 日 08:25
    ":createdto": 1686821106000,   // 2023 年 6 月 15 日 09:25
    ":completed": "false",
  },
  ScanIndexForward: false,
  Limit: 5,
  Select: "ALL_ATTRIBUTES",
  FilterExpression: "#completed = :completed",
}
``` 这返回了条目。

但是当我将 `ExpressionAttributeValues` 中的以下值更改为：<br/>
`createdfrom`:  1686731286000 (2023 年 6 月 14 日 08:25) <br/>
这没有返回任何条目（但返回了 LastEvaluatedKey）。<br/>
这很奇怪，因为 6 月 15 日返回了条目，只是 6 月 14 日到 6 月 15 日的条目的子集。

我还尝试在 AWS 控制台 DynamoDB 中复制这个问题，结果相同。

然后我还尝试移除 `ScanIndexForward`，现在返回了条目。

<details>
<summary>英文:</summary>


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

`Partition key`: message&lt;br /&gt;
`Sort key`: created&lt;br /&gt;
`Projected keys`: All&lt;br /&gt;

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: &quot;test-idStatus&quot;,
  IndexName: &quot;messageCreatedIndex&quot;,
  KeyConditionExpression: &quot;#message = :message and #created BETWEEN :createdfrom AND :createdto&quot;,
  ExpressionAttributeNames: {
    &quot;#message&quot;: &quot;message&quot;,
    &quot;#created&quot;: &quot;created&quot;,
    &quot;#completed&quot;: &quot;completed&quot;,
  },
  ExpressionAttributeValues: {
    &quot;:messageFunction&quot;: &quot;Instruction&quot;,
    &quot;:createdfrom&quot;: 1686817506000, // 2023-June-15 08:25
    &quot;:createdto&quot;: 1686821106000,   // 2023-June-15 09:25
    &quot;:completed&quot;: &quot;false&quot;,
  },
  ScanIndexForward: false,
  Limit: 5,
  Select: &quot;ALL_ATTRIBUTES&quot;,
  FilterExpression: &quot;#completed = :completed&quot;,
}

This returned entries

But when I changed the ExpressionAttributeValues of:<br/>
createdfrom: 1686731286000 (2023-June-14 08:25) <br/>
This did not return any entries (but returns a LastEvaluatedKey).<br/>
Which is weird since June-15 returned entries which 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.

答案1

得分: 2

发生的情况是你设置了一个FilterExpression和一个Limit。现在发生的是DynamoDB读取了5个项目，因为这是你的Limit，然后将你的过滤器应用于这5个项目，但没有一个匹配表达式，所以你得到了一个空响应。

更改日期只是简单地改变了读取的5个项目，很幸运它们匹配了你的过滤器。更改扫描方向也是一样的，你只是改变了前5个项目。

英文:

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.