How to execute multiple operations while updating an item in Amazon DynamoDB?

user_11503700 0
Viewed 326

I'm trying to update an item in Amazon DynamoDB. I want to execute an operation that consists of one sum and one subtraction, but I'm not able to accomplish my goal.

Here is the function, ddb is an instance of the class AWS.DynamoDB.DocumentClient:

function updateLecture(event){
    const params = {
        TableName: 'lecture',
        Key: {
            'lecture_id': Number.parseInt(event.lecture_id)
        },
        UpdateExpression: 'SET #free = :total - (#total + #free)',
        ExpressionAttributeValues: {
            ':total': event.total
        },
        ExpressionAttributeNames: {
            "#total" : "total",
            "#free" : "free"
        },
        ReturnValues : 'UPDATED_NEW'
    }
    
    return ddb.update(params).promise();
}

When I try to run the function, I get the following error:

"errorType": "ValidationException",
"errorMessage": "Invalid UpdateExpression: Syntax error; token: \"+\", near: \"#total + #free\""

I've tried to work with parenthesis, but I always receive the same error.

I'm afraid I can't use more than one operation, but I couldn't find any trace of that in the docs. Anyway, is there a way to use multiple operations?

amazon-dynamodb node.js amazon-web-services
1 Answers

Just tried the following PartiQL statement in the DynamoDB console, and it worked fine:

// Works just fine
UPDATE "ddb-playground" 
SET myNumA = 10 - (myNumB + myNumA)
WHERE PK = 'foo' AND SK = 'bar'

It runs just fine, so at least there's no fundamental limitation here.

However, just like you, I'm not able to get the JS SDK to do this with an UpdateExpression, I get syntax error no matter what I try. So it seems like the JS SDK is limited to a single arithmetic expression on numeric values.

// Fails with:
//
// ValidationException: Invalid UpdateExpression: Syntax error; token: "+", near: "myNumB + myNumA"
import AWS from "aws-sdk"
const ddb = new AWS.DynamoDB()

await ddb.putItem({
    TableName: "ddb-playground",
    Item: {
        PK: {S: "foo"},
        SK: {S: "bar"},
        myNumA: {N: "10"},
        myNumB: {N: "20"}
    }
}).promise()


await ddb.updateItem({
    TableName: "ddb-playground",
    Key: {
        PK: {S: "foo"},
        SK: {S: "bar"}
    },
    UpdateExpression: "SET myNumA = :newVal - (myNumB + myNumA)",
    ExpressionAttributeValues: {
        ":newVal": {N: "30"}
    }
}).promise()

So perhaps your only option here is to actually use PariQL for this one?

await ddb.executeStatement({
  Statement: 'UPDATE "ddb-playground" SET myNumA = ? - (myNumB + myNumA) WHERE PK = ? AND SK = ?',
  Parameters: [{N: "30"}, {S: "foo"}, {S: "bar"}]
}).promise()
user_260510
Related