Java: Query DynamoDB table for all values for a Range key using Mapper

Question

I have a DynamoDb Table. It has a hash key of "ID" and range key of "Category".

I am trying to find all entries for a "Category", comedy for example.

This is my mapping class:

@DynamoDBTable(tableName = "Videos")
public class VideoDynamoMappingAdapter {

    private String videoID;
    private String videoCategory;
    private String videoArtist;
    private String videoTitle;

    @DynamoDBHashKey(attributeName = "ID")
    public String getVideoID() {
        return videoID;
    }

    public void setVideoID(String videoID) {
        this.videoID = videoID;
    }
    @DynamoDBRangeKey(attributeName = "Category")
    public String getVideoCategory() {
        return videoCategory;
    }

    public void setVideoCategory(String videoCategory) {
        this.videoCategory = videoCategory;
    }

    @DynamoDBAttribute(attributeName = "ArtistName")
    public String getVideoArtist() {
        return videoArtist;
    }

    public void setVideoArtist(String videoArtist) {
        this.videoArtist = videoArtist;
    }

    @DynamoDBAttribute(attributeName = "VideoTitle")
    public String getVideoTitle() {
        return videoTitle;
    }

    public void setVideoTitle(String videoTitle) {
        this.videoTitle = videoTitle;
    }

}

I have been trying to query something like this:

    DynamoDBQueryExpression<VideoDynamoMappingAdapter> queryExpression = new DynamoDBQueryExpression<VideoDynamoMappingAdapter>()
            .withRangeKeyCondition()


    List<VideoDynamoMappingAdapter> itemList = mapper.query(VideoDynamoMappingAdapter.class, queryExpression);

I have been looking over the guide but I can't understand it, I would have thought this would be an easy query that is done all the time so maybe I am missing something:

http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/QueryingJavaDocumentAPI.html#DocumentAPIJavaQueryExample

Thanks in advance for your help

Answer 1

Notionquest was right in his answer that I could not query for all values of a hash key in a given range condition. I solved this by creating a global secondary index on my Category attribute.

This is my mapper class:

@ DynamoDBTable(tableName = "AlarmTable")
public class VideoDynamoMappingAdapter {

    private String videoID;
    private String videoCategory;
    private String videoArtist;
    private String videoTitle;

    @DynamoDBHashKey(attributeName = "ID")
    public String getVideoID() {
        return videoID;
    }

    public void setVideoID(String videoID) {
        this.videoID = videoID;
    }

    @DynamoDBIndexHashKey( globalSecondaryIndexName = "CategoryIndex", attributeName = "Category")
    public String getVideoCategory() {
        return videoCategory;
    }

    public void setVideoCategory(String videoCategory) {
        this.videoCategory = videoCategory;
    }

    @DynamoDBAttribute(attributeName = "VideoArtist")
    public String getVideoArtist() {
        return videoArtist;
    }

    public void setVideoArtist(String videoArtist) {
        this.videoArtist = videoArtist;
    }

    @DynamoDBAttribute(attributeName = "VideoTitle")
    public String getVideoTitle() {
        return videoTitle;
    }

    public void setVideoTitle(String videoTitle) {
        this.videoTitle = videoTitle;
    }

}

Notice the annotation on category for that.

And then my query is like this:

VideoDynamoMappingAdapter videosToFind = new VideoDynamoMappingAdapter();
        videosToFind.setVideoCategory("Other");

        DynamoDBQueryExpression<VideoDynamoMappingAdapter> queryExpression = new DynamoDBQueryExpression<VideoDynamoMappingAdapter>()
                .withIndexName("CategoryIndex")
                .withHashKeyValues(videosToFind)
                .withConsistentRead(false);

        List<VideoDynamoMappingAdapter> itemList = mapper.query(VideoDynamoMappingAdapter.class, queryExpression);
        System.out.println("test" + itemList.size());

I hope it helps someone.

Answer 2

The DynamoDB table can't be queried by Range key alone without the Hash key. However, it can be queried by Hash key alone without the Range key.

When the DynamoDB table is queried by Range key alone without the Hash key, it would throw ValidationException.

Unable to read item. Error JSON: {
  "message": "Query condition missed key schema element",
  "code": "ValidationException"
}

The alternate option would be to scan the database using the Range key. Please note that DynamoDB scan is a costly operation as it will scan the entire table.

Scanning the Video table by categories:-

Please note that "OR" condition is used as "IN" is supported for certain data types only.

public List<VideoDynamoMappingAdapter> getVidoesByCategory(String[] categories) {

        ScanResultPage<VideoDynamoMappingAdapter> videoResultPage = null;
        List<VideoDynamoMappingAdapter> videoList = new ArrayList<>();

        try {

            do {
                DynamoDBMapper dynamoDBMapper = new DynamoDBMapper(dynamoDBClient);

                Map<String, AttributeValue> expressionAttributeValues = new LinkedHashMap<String, AttributeValue>();

                StringBuilder filterExpression = new StringBuilder(); 
                int i = 1;

                for (String category : categories) {
                    if (i==1) {
                        filterExpression.append("Category = " + ":videoCategory" + i);  
                    } else {
                        filterExpression.append(" OR Category = " + ":videoCategory" + i);
                    }

                    expressionAttributeValues.put(":videoCategory" + i, new AttributeValue().withS(category));
                    i++;
                }

                System.out.println("Filterexpression =====>" + filterExpression.toString());

                DynamoDBScanExpression scanExpression = new DynamoDBScanExpression()                
                        .withFilterExpression(filterExpression.toString())
                        .withExpressionAttributeValues(expressionAttributeValues);

                if (videoResultPage != null) {
                    scanExpression.setExclusiveStartKey(videoResultPage.getLastEvaluatedKey());
                }

                videoResultPage = dynamoDBMapper.scanPage(VideoDynamoMappingAdapter.class, scanExpression);

                System.out.println("Results ==========>" + videoResultPage.getResults());

                videoList.addAll(videoResultPage.getResults());

            } while (videoResultPage.getLastEvaluatedKey() != null);

        } catch (Exception db) {

            db.printStackTrace();

        }

        System.out.println(videoList.toString());
        return videoList;

    }