Querying Dynamodb using LSI with partition key and range key

Question

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" ....)

I disagree with the edit of my original question, I am asking specifically how to query with a partition key and a list of range key values. The title of the question loses that distinction. Querying given a partition key and a single range key is supported, but not querying for a list of range key values, which is what I was asking — Jay Hu

LuFFy LuFFy · Accepted Answer · 2017-02-28T10:21:03

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'
]);

Querying Dynamodb using LSI with partition key and range key

1 Answers