DynamoDB query filtered by date less than a given one

Question

I'm doing a java springboot project with DynamoDB. I have this problem:

I have a dynamoDB table that is constructed like this:

aws dynamodb create-table  \
    --table-name my-table \
    --attribute-definitions \
        AttributeName=my_id,AttributeType=S \
        AttributeName=my_type,AttributeType=S \
        AttributeName=creation_date,AttributeType=N \
    --key-schema \
        AttributeName=my_id,KeyType=HASH \
        AttributeName=my_type,KeyType=RANGE \
    --billing-mode PAY_PER_REQUEST \
    --endpoint-url $ENDPOINT:$PORT \
    --global-secondary-indexes \
        "[
            {
                \"IndexName\": \"CreationDateIndex\",
                \"KeySchema\": [{\"AttributeName\":\"creation_date\",\"KeyType\":\"HASH\"}],
                \"Projection\":{
                    \"ProjectionType\":\"ALL\"
                },
                \"ProvisionedThroughput\": {
                    \"ReadCapacityUnits\": 10,
                    \"WriteCapacityUnits\": 5
                }
            }
        ]"

This is the Entity:

    @NotNull
    @DynamoDBHashKey(attributeName = "my_id")
    protected UUID myId;

    @NotNull
    @DynamoDBRangeKey(attributeName = "my_type")
    protected String myType;

    @DynamoDBIndexHashKey(globalSecondaryIndexName = "CreationDateIndex")
    @DynamoDBAttribute(attributeName = "creation_date")
    protected long creationDate;

I want to create a method in the repository that deletes all rows with creation_date less than the given date (actually I saved the epochSeconds as creation_date), but if I build the query I don't have the comparison option to add .withComparisonOperator(ComparisonOperator.LE).

How can I create a query?

The only solution I've found is to use DynamoDBScanExpression but that's not efficient. This is the current solution:

    @Override
    public boolean delete(long creationDateBefore) throws InterruptedException {
        System.out.println("STARTING DELETING objects");
        List<MyEntity> objectsToDelete = findByCreationDateBefore(creationDateBefore);
        System.out.println("DELETING " + objectsToDelete.size() + " objects");
        return batchDelete(objectsToDelete).isEmpty();
    }

    private List<MyEntity> findByCreationDateBefore(long creationDateBefore) {
        DynamoDBScanExpression scanExpression = new DynamoDBScanExpression();
        scanExpression.addFilterCondition(
                "creation_date",
                new Condition()
                        .withComparisonOperator(ComparisonOperator.LE)
                        .withAttributeValueList(new AttributeValue().withN(String.valueOf(creationDateBefore)))
        );

        return dbMapper.scan(MyEntity.class, scanExpression);
    }

Do you have other possible solutions? I could modify the table but the index must always be Hash my_id + Range my_type.

Thanks!

Answer 1

You index currently only has creation_date which only allows you to specify an exact date, as you must always know the entire partition key when Querying.

To overcome this you can add a static value to your items, lets call it GSI1_PK:

GSI_PK	creation_date	data
1	2023-12-16T00:00:000	some data
1	2023-12-16T01:00:000	some data
1	2023-12-16T02:00:000	some data
1	2023-12-16T03:00:000	some data
1	2023-12-16T03:00:000	some data
1	2023-12-16T04:00:000	some data

Now you can issue a Query where GSI_PK = 1 AND creation_date < $DATE