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?

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/