Query DynamoDB with IN Clause

Question

I am new to DynamoDB and wanted to know how can we query on a table in DynamoDB with IN clause using Java.

I have a table named items. It`s schema is

1. Product (Partition Key of type String)
2. ID (Sort Key of type int)
3. Date ( attribute of type String)

I want to query similar to

SELECT ID FROM items WHERE Product IN ("apple","orange"). 

or

SELECT Product FROM items WHERE ID IN (100,200). 

Answer 1

As the requirement is to get the products without the partition key, there are two options available.

1) Create GSI (Global Secondary Index) on sort key attribute Id to use the Query API

2) Scan the entire table to get the products - not very efficient as it scans the full table

Both the options use the IN operator available on DynamoDB.

Sample code for Scan using ID:-

Map<String, AttributeValue> attributeValues = new HashMap<>();
attributeValues.put(":id1", new AttributeValue().withN("100"));
attributeValues.put(":id2", new AttributeValue().withN("200"));

DynamoDBScanExpression dynamoDBScanExpression = new DynamoDBScanExpression()
                                                    .withFilterExpression("ID IN (:id1, :id2)")
                                                    .withExpressionAttributeValues(attributeValues);


PaginatedScanList<yourModelClass> scanList = dynamoDBMapper.scan(yourModelClass.class, dynamoDBScanExpression,
        new DynamoDBMapperConfig(DynamoDBMapperConfig.ConsistentReads.CONSISTENT));

Iterator<yourModelClass> iterator = scanList.iterator();

while (iterator.hasNext()) {
    System.out.println(iterator.next().getTitle());
}

Query by product:-

1) Cannot use IN operator for partition key

2) Cannot use batchLoad API without knowing the sort key

Conclusion:-

The efficient solution is to create GSI (without sort key) on attribute Id and use batchLoad API.

Note: The partition key of GSI need not be unique