Jay Hu Jay Hu -4 years ago 125
SQL Question

Querying Dynamodb using LSI with partition key and range key

In DynamoDB I configured LSI(Local Secondary Index) using a Partition Key and Range Key.

How can I Query DynamoDB Table with Partition Key value and Range Key value ?

In SQL, I can use the IN operator:

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


How do I achieve this functionality in DynamoDB ?

As Per Documentation of Amazon query


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


However in the list of valid comparison operators, there is nothing analogous to SQL "IN".

Is there any way to use multiple key condition expressions like below SQL ?

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

Answer Source

As Per Example Given In AWS Document of 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 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.)

Note that the function name begins_with is case-sensitive.

So, only AND supported for ranges. There is no OR. Also you could try using begins_with.

Your Scenario Can be converted to following Code :

$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'
]);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download