How can I use Arithmetic operators in PartiQL for DynamoDB?

Question

According to what I have read in the docs, DynamoDB supports PartiQL, which in turn supposedly supports arithmetic operators (such as +, -, *, /).

https://docs.aws.amazon.com/qldb/latest/developerguide/ql-operators.html

When I try to perform a sum in a query, where I try to add 1 to each and every element of a specific column (my_column) which is of type N - number, as in:

SELECT eventTime, my_column + 1
FROM MyTable
WHERE eventTime BETWEEN 1615161600000 AND (1615165200000)

The above statement results in the following error:

What am I missing or doing wrong?

Thanks in advance.

score 1 · Answer 1 · answered Aug 25 '22 at 17:15

PartiQL support arithmetic operators but you use-case in invalid. PartiQL does not have the ability to add a value to a column for each and every in the table, as its a NoSQL table, each item will have to be directly invoked.

In order to do that you would need to Scan the table to retrieve every item, then iterate through the list of items, using an update command:

PSEUDO:

results = `SELECT PK, SK FROM "myTable";`

FOR item in results:
  UPDATE "myTable" 
  SET AwardsWon=AwardsWon+1 
  WHERE PK=item.PK AND SK=item.SK

Keep in mind the above is pseudo code. I personally would prefer using the low level DynamoDB API, I find it easier to do such things.

How can I use Arithmetic operators in PartiQL for DynamoDB?

1 Answers1