How to make several conditions on sort key with dynamodb queries?

Question

In their documentation AWS says about KeyConditionExpression for queries :

The condition can optionally perform one of several comparison tests on a single sort key value. This allows Query 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.

However I couldn't manage to make it works for example to make something like pk = :pk AND (begins_with(sk,:foo) OR begins_with(sk,:bar)) I get error Invalid operator used in KeyConditionExpression: OR. I saw some people with this issue as well on older posts saying dynamodb doesn't allow that but then I don't understand what AWS means in their documentation about doing several tests on sort keys. What does it means and how does it works ?

Thanks,

Answer 1

Records in a particular partition are stored in order of range/sort key. When retrieving data from a particular partition, we can retrieve a record matching a sort key or set of records adjacent to a sort key.

So, all the logical operations that Dynamo supports for a query operations EQ | LE | LT | GE | GT | BEGINS_WITH | BETWEEN are only for fetching adjacent records , not random records.

The query you are trying with OR, (begins_with(sk,:foo) OR begins_with(sk,:bar) will result in fetching records from two different locations with in Dynamo, for which we need to run two different queries.