DynamoDB condition expression query

Use Case

I have a table has below schema:

UserId(hashkey), earnedPoints, status

I want to achieve: At any given time, for the same seller, he/she should only have 1 PENDING status record. Example:

Insert user1, 10, PENDING -> successful, as db dose not have record for user1
Insert user1, 30, PENDING -> condition fail, as db already contain user1 record and that record is in PENDING status.
Insert user2, 10, REDEEMED -> successful, as db dose not have record for user2
Insert user2, 10, PENDING -> successful, db has record for user2, but that record is in status of REDEEMED, so inserting PENDING record is allowed.

What I have tried

Try to use DDB Expression: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html to achieve this during putItem operation, using software.amazon.awssdk.enhanced client.

So I wrote:

Expression conditionExpression = Expression.builder()
                                                   .expression("attribute_not_exists(userId) OR #currentStatus <> :pendingStatus")
                                                   .putExpressionName("#currentStatus", "status")
                                                   .putExpressionValue(":pendingStatus",
                                                                       AttributeValue.builder()
                                                                                     .s("PENDING")
                                                                                     .build())
                                                   .build();

Does not work.

Any suggestions?

Solution

Your issue is because DynamoDB does not evaluate the entire database when you put a condition on an attribute, it only evaluates the item you are operating on. So the condition is happening on completely different items.

You will need to make use of transactions to ensure global uniqueness as defined in this blog post:

https://aws.amazon.com/blogs/database/simulating-amazon-dynamodb-unique-constraints-using-transactions/