Dynamodb query - OR condition in KeyConditionExpression

Question

I have a DynamoDB table with feed_guid as the global secondary index. I want to query with a set of feed_guid in that table. Since feed_guid is not my primary keys, I can't use getBatchItem. When I tried the following method, I got this error:

Invalid operator used in KeyConditionExpression: OR

    $options = array(
                'TableName' => 'feed',
                    'IndexName' => 'GuidIndex',
                    'KeyConditionExpression' => 'feed_guid = :v_guid1 or feed_guid = :v_guid2',

                    'ExpressionAttributeValues' =>  array (
                        ':v_guid1' => array('S' => '8a8106e48bdbe81bf88d611f4b2104b5'),
                        ':v_guid2' => array('S' => '19cab76242a6d85717de64fe4f8acbd4')
                    ),
                    'Select' => 'ALL_ATTRIBUTES',
                );
                $response = $dynamodbClient->query($options);

Clerissa

Answer 1

You can't use OR condition. You should use

rangeAttributeName BETWEEN :rangeval1 AND :rangeval2

if possible or

feed_guid IN (:v_guid1, :v_guid2)

See ExpressionAttributeValues and KeyConditionExpression

Bornite

This is not working. It shows "Invalid operator used in KeyConditionExpression: IN" error. – Clerissa

Try using between. IN is used for ExpressionAttributeValues – Bornite

Just read the definition first. docs.aws.amazon.com/amazondynamodb/latest/APIReference/… – Clerissa

Yes. documentation says only AND supported for ranges. There is no OR. Also you could try using begins_with – Bornite

As far as I can tell, there's no support for either OR or IN when using KeyConditionExpression -- not when using the Query API, anyway. – Boarfish

What if instead of 2 values you have 20? How would you use between for 20 values? – Polygamy

@Polygamy feed_guid IN (:v_guid1, :v_guid2, :v_guid3 ...) – Bornite

@Bornite IN is not supported in KeyConditionExpression – Polygamy

Answer 2

In order to achieve what you want here, you'll need to take the union of two separate queries.

Currently, DynamoDB's Query API only supports having one condition on Hash AND Range Keys in only the KeyConditionExpression because this limits the items you search and ultimately reduces cost of say a more complex query like what you've described here.

Clift

Can you please give an example "How to use union of queries?" Every where in Amazon documentation they have described theoretically. – Mortify

They wrote "union of two separate queries" (my emphasis) I.e. you should make two separate queries and combine the results – Urochrome