DynamoDB: Batch query items with highest range key given a set of hash key

Question

I have a table Book with bookId and lastBorrowed as hash and range keys, respectively. Let's say each time a book is borrowed, a new row is created.

(Yes, this is NOT sufficient and I can just add a column to keep track of the count and update lastBorrowed date. But let's just say I'm stuck with this design there's nothing I can do about it.)

Given a set of bookIds (or hashKeys), I would like to be able to query the last time each book is borrowed.

I attempted to use QueryRequest, but kept getting com.amazonaws.AmazonServiceException: Attempted conditional constraint is not an indexable operation

final Map<String, Condition> keyConditions =
                Collections.singletonMap(hashKeyFieldName, new Condition()
                 .withComparisonOperator(ComparisonOperator.IN)
                 .withAttributeValueList(hashKeys.stream().map(hashKey -> new AttributeValue(hashKey)).collect(Collectors.toList())));

I also tried using BatchGetItemRequest, but it didn't work, either:

final KeysAndAttributes keysAndAttributes = new KeysAndAttributes() .withConsistentRead(areReadsConsistent);
hashKeys.forEach(hashKey -> { keysAndAttributes.addExpressionAttributeNamesEntry(hashKeyFieldName, hashKey); });

final Map<String, KeysAndAttributes> requestedItemsByTableName = newHashMap();
requestedItemsByTableName.put(tableName, keysAndAttributes);

final BatchGetItemRequest request = new BatchGetItemRequest().withRequestItems(requestedItemsByTableName);

Any suggestion would be much appreciated! Or if someone can tell me this is currently not supported at all, then I guess I'll just move on!

Answer 1

You can do this, in fact its very easy. All you have to do is execute a Query for your bookId and then take the first result.

By the way, your table design sounds absolutely fine, the only problem is the attribute should probably be called borrowed rather than last borrowed.

You can have multiple results for a single bookId, but because lastBorrowed is your range key, the results will come back ordered by that attribute.

You seem to be using Legacy Functions, are you editing old code?

If not, execute your Query something like this:

//Setting up your DynamoDB connection
AmazonDynamoDB client = AmazonDynamoDBClientBuilder.standard()
.withRegion(Regions.US_WEST_2).build();
DynamoDB dynamoDB = new DynamoDB(client);
Table table = dynamoDB.getTable("YOURTABLE");

//Define the Query
QuerySpec spec = new QuerySpec()
    .withKeyConditionExpression("bookId = :book_id)
    .withValueMap(new ValueMap()
        .withString(":book_id", "12345")
    .withScanIndexForward(true);

//Execute the query
ItemCollection<QueryOutcome> items = table.query(spec);

//Print out your results - take the first item
Iterator<Item> iterator = items.iterator();
while (iterator.hasNext()) {
    System.out.println(iterator.next().toJSONPretty());
}