DynamoDB：查询所有数据并按日期降序排序

Question

我是AWS DynamoDB的新手，需要专家在这里给予指导。

我正在创建一个应用程序，它将显示一个事件列表，并且事件日期必须大于今天的日期，并按照事件日期降序排列。我的表模式如下：

分区键=事件ID

排序键=事件日期

如果我使用关系型数据库，我可以使用“SELECT * FROM events where eventdate > todaydate ORDERBY eventdate DESC”，但我怎样才能在AWS DynamoDB中实现这一点呢？我希望使用QUERY而不是SCAN。

- Zalizan Zolkipali

---

让我们在聊天室上讨论。 - LuFFy

Answer 1

根据DynamoDB查询API文档所述：

Query :

A Query operation uses the primary key of a table or a secondary index to directly access items from that table or index.

Use the KeyConditionExpression parameter to provide a specific value for the partition key. The Query operation will return all of the items from the table or index with that partition key value. You can optionally narrow the scope of the Query operation by specifying a sort key value and a comparison operator in KeyConditionExpression. You can use the ScanIndexForward parameter to get results in forward or reverse order, by sort key.

KeyConditionExpression :

The condition that specifies the key value(s) for items to be retrieved by the Query action.

The condition must perform an equality test on a single partition key value. The condition can also perform one of several comparison tests on a single sort key value. Query can use KeyConditionExpression to retrieve one item with a given partition key value and sort key value, or several items that have the same partition key value but different sort key values.

The partition key equality test is required, and must be specified in the following format:

partitionKeyName = :partitionkeyval

If you also want to provide a condition for the sort key, it must be combined using AND with the condition for the sort key. Following is an example, using the = comparison operator for the sort key:

partitionKeyName = :partitionkeyval AND sortKeyName = :sortkeyval

Valid comparisons for the sort key condition are as follows:

• sortKeyName = :sortkeyval - true if the sort key value is equal to :sortkeyval.
• sortKeyName < :sortkeyval - true if the sort key value is less than :sortkeyval.
• sortKeyName <= :sortkeyval - true if the sort key value is less than or equal to :sortkeyval.
• sortKeyName > :sortkeyval - true if the sort key value is greater than :sortkeyval.
• sortKeyName >= :sortkeyval - true if the sort key value is greater than or equal to :sortkeyval.
• sortKeyName BETWEEN :sortkeyval1 AND :sortkeyval2 - true if the sort key value is greater than or equal to :sortkeyval1, and less than or equal to :sortkeyval2.
• begins_with ( sortKeyName, :sortkeyval ) - true if the sort key value begins with a particular operand. (You cannot use this function with a sort key that is of type Number.)

You can optionally use the ExpressionAttributeNames parameter to replace the names of the partition key and sort key with placeholder tokens. This option might be necessary if an attribute name conflicts with a DynamoDB reserved word. For example, the following KeyConditionExpression parameter causes an error because Size is a reserved word:

Size = :myval

To work around this, define a placeholder (such a #S) to represent the attribute name Size. KeyConditionExpression then is as follows:

#S = :myval

For a list of reserved words, see Reserved Words in the Amazon DynamoDB Developer Guide.

For more information on ExpressionAttributeNames and ExpressionAttributeValues, see Using Placeholders for Attribute Names and Values in the Amazon DynamoDB Developer Guide.

Type: String

Required: No

您的场景可以转换为以下代码：

$tableName = "genericTable";
$response = $dynamodb->query([
    'TableName' => $tableName,
    'IndexName' => 'OrderCreationDateIndex',
    'KeyConditionExpression' => 'partitionKeyName = :partitionkeyval AND sortKeyName = :sortkeyval',
    'ExpressionAttributeValues' =>  [
        ':partitionkeyval' => ['S' => 'pkey'],
        ':sortkeyval' => ['S' => 'sortkey']
    ],
    'Select' => 'ALL_PROJECTED_ATTRIBUTES',
    'ScanIndexForward' => false,
    'ConsistentRead' => true,
    'Limit' => 5,
    'ReturnConsumedCapacity' => 'TOTAL'
]);

- LuFFy

---

@zalizan-zolkipali，将创建日期转换为Unix时间戳，然后按照我的答案完成任务。这已经是我端上正在运行的解决方案了。 - LuFFy

@Zalizan Zolkipali，以上解决方案不可行，因为eventId不是查询的一部分。如果eventId可用，则Query API用法是一个直截了当的用例。 - notionquest

Answer 2

很遗憾，您无法使用Query API实现上述用例。

原因：

• 您需要大于比较运算符。非相等比较运算符可以在FilterExpression或使用KeyConditionExpression的排序键属性上使用。

• 在上述用例中，您没有eventId，因此无法使用KeyConditionExpression。即使您使用eventDate作为哈希键创建了GSI，也无法对哈希键使用非相等运算符，并且除排序键以外的任何属性都不支持排序。因此，您无法使用GSI选项实现排序要求。

使用Query API的解决方法：

• 创建一个哈希键为 eventDate 的 GSI

• 使用 Query API 查询索引，通过 eventDate 进行查询

• 在客户端按照 eventDate 对数据进行排序。大多数编程语言都有良好的排序 API，您可以轻松地在客户端实现此操作

注意：

• 您无法同时满足排序和使用 Query API 的要求，尤其是在不知道主表哈希键的情况下

- notionquest

---

谢谢您的回复，先生。这意味着我无法绕过来实现我在上面问题中提到的结果了吗？ - Zalizan Zolkipali

@notionquest，请注意，使用查询 API，您可以使用 ScanIndexForward 参数按排序键正向或反向获取结果。 - LuFFy

@LuFFy 这里的用例没有散列键值。 OP希望在没有散列键值的情况下使用查询API。这就是我建议使用以eventDate为散列键的GSI的原因。 - notionquest

@notionquest，如果OP只想针对dynamodb获得解决方案，那么他只有一个选择，就是更改表结构并添加SortKey和DONE...他没有发布任何关于他是否使用Hash Key Value的信息，您能告诉我他在哪里说了这样的话吗？ - LuFFy

@LuFFy，“如果我使用关系型数据库，我可以使用“SELECT * FROM events where eventdate > todaydate ORDERBY eventdate DESC”，但是我想用AWS DynamoDB实现这个功能”……这里提到了哈希键在哪里。OP想要上述查询的等效结果。 - notionquest

这应该是被接受的答案，因为你无法从查询的哈希键中实现条件运算符。 - Yasith Prabuddhaka