DynamoDB: Query for range key in a set of non-sequential range keys

2
votes

I have a table that uses a hash key and sort (range) key. My range key is a uuid. I have a case where I have an arbitrary set of range keys that I want to query the database for using DynamoDBMapper. They are in no way sequential, and unfortunately for my purpose there is no way that they could be sequential, so I cannot query a range of keys (i.e. key is between a and b). Is there any way to have a query condition that searches for the range key that is in a set of potential values? Essentially an sql WHERE condition like: rangeKey IN (val1, val2, ...);

According to the docs for DynamoDBQueryExpression this doesn't appear to be possible using setKeyConditionExpression or setRangeKeyConditions. Is there any efficient way of doing this? Or is my best bet iterating through my set of potential values and using the load method to individually retrieve them from the database?

1
amazon-web-servicesamazon-dynamodb
can you share a bit more details about your data model? seems a bit strange to use uuids as range keys because they have no natural order. – hellomichibye
I second hellomichibye's comment. It sounds like your data model needs some work. Using a UUID as a range key makes no sense. – Mark B
I agree that it's not ideal but its the best solution we came up with. We have partitions of the database for different customers that are accessed via the hash key. Within the partition, all documents are very independent of eachother so any sorting or ordering would be arbitrary. This query is needed because we have a Reference table that associates multiple documents to another entity. However, the documents reference to that entity changes often, so again any sorting or ordering would be very arbitrary. – Geoff McLennan

1 Answers

3
votes

IN operation is not supported in Query as per the documentation here:

The sort key condition must use one of the following comparison operators:

  • a = b — true if the attribute a is equal to the value b
  • a < b — true if a is less than b
  • a <= b — true if a is less than or equal to b
  • a > b — true if a is greater than b
  • a >= b — true if a is greater than or equal to b
  • a BETWEEN b AND c — true if a is greater than or equal to b, and less than or equal to c.

The following function is also supported:

begins_with (a, substr)— true if the value of attribute a begins with a particular substring.

Also, you always need the exact hash key to do a Query. If that is OK for your use case, you should Query DynamoDB and apply the IN filter at the application layer.

If you don't have the exact hash key, then do a Scan, which does have an IN operation for filtering. Documentation here.