amazon-dynamodb - 使用带有分区键和范围键的 LSI 查询 Dynamodb

Question

在 DynamoDB 中，我使用分区键和范围键配置了 LSI(本地二级索引)。

如何使用分区键值和范围键值查询 DynamoDB 表？

在 SQL 中，我可以使用 在 运算符(operator):

SELECT *
  FROM genericTable
 WHERE partionKey = "foo"
   AND rangeKey IN ("bar1", "bar11", "bar5")

如何在 DynamoDB 中实现此功能？

按照 Documentation of Amazon query

Query can use KeyConditionExpression to retrieve ... several items that have the same partition key value but different sort key values.

然而，在有效的比较运算符列表中，没有类似于 SQL "IN"的内容。

有没有办法使用多个关键条件表达式，如下面的 SQL ？

SELECT *
  FROM genericTable
 WHERE partionKey = "foo"
   AND (rangeKey = "bar1"
    OR rangeKey = "bar5" ....)

Answer 1

根据 AWS 文档中给出的示例 Local Search Indexes - PHP Low Level API ,

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 thanor equal to :sortkeyval2.
• begins_with ( sortKeyName, :sortkeyval ) - true if the sort key value begins with a particular operand. (You cannot use this functionwith a sort key that is of type Number.)

Note that the function name begins_with is case-sensitive.

所以，只有 和 支持范围。 没有 OR .您也可以尝试使用 开始于 .
您的场景可以转换为以下代码:

$tableName = "genericTable";
$response = $dynamodb->query([
    'TableName' => $tableName,
    'IndexName' => 'OrderCreationDateIndex',
    'KeyConditionExpression' => 'partionKey = :p_key and begins_with(rangekey, :range)',
    'ExpressionAttributeValues' =>  [
        ':p_key' => ['S' => 'foo'],
        ':range' => ['S' => 'bar']
    ],
    'Select' => 'ALL_PROJECTED_ATTRIBUTES',
    'ScanIndexForward' => false,
    'ConsistentRead' => true,
    'Limit' => 5,
    'ReturnConsumedCapacity' => 'TOTAL'
]);