DynamoDB condition expression query

Question

### 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?

Accepted Answer

Ah, I think I misunderstood your design. In your original message you mentioned that userId is the partition (hash) key for your table, but did not mention a sort key. Now starting to believe that you might be using one of the attributes (perhaps status) as the sort key? I think that if you take an approach where you have a simple primary key (just the partition key attribute userId - no sort key), my proposed solution will work for you. It does not maintain a complete history of the status stages that a given user might go through - instead, updating an existing item in place for that userId if their status changes in a way that meets your set of conditions. Does that make sense?

Using transactions per the blog you linked may be an option, but it's heavy, and I think not justified.

Answer

Hi Dawn - I was able to get the conditional PutItem you're looking for to work with the basic Java v1 SDK as follows....

```
    private static PutItemRequest createPutItemRequest() {
        PutItemRequest putItemRequest = new PutItemRequest();
        putItemRequest.setTableName("condexpr");
        putItemRequest.setItem(getItem());
        String conditionExpression = "attribute_not_exists(#3a350) Or NOT (#3a351 = :3a351)";
        putItemRequest.setConditionExpression(conditionExpression);
        putItemRequest.setExpressionAttributeNames(getExpressionAttributeNames());
        putItemRequest.setExpressionAttributeValues(getExpressionAttributeValues());
        return putItemRequest;
    }

private static Map getItem() {
        Map item = new HashMap(); 
        item.put("id", new AttributeValue("user1"));
        item.put("score", new AttributeValue().withN("110"));
        item.put("status", new AttributeValue("REDEEMED"));
        return item;
    }

private static Map getExpressionAttributeNames() {
        Map expressionAttributeNames = new HashMap(); 
        expressionAttributeNames.put("#3a350", "id");
        expressionAttributeNames.put("#3a351", "status");
        return expressionAttributeNames;
    }

private static Map getExpressionAttributeValues() {
        Map expressionAttributeValues = new HashMap(); 
        expressionAttributeValues.put(":3a351", new AttributeValue("PENDING"));
        return expressionAttributeValues;
    }
```

Maybe you could try something like the following?

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

Answer

Interesting - it worked in my testing. Any chance you didn't match the case on "PENDING" ? The expression check is case sensitive.

DynamoDB condition expression query

Use Case

What I have tried

Relevant content