DynamoDB | How to update an item, if the item does not exist yet with PartiQL?

Question

I would like to make an update call to dynamoDB which:

1. If the ddb item exists, update the existing string set for its new values (adding not replacing),
2. or if the ddb item does not exist yet, create a new item.
3. But both cases should be covered with just one api call.

This is currently possible for me by using the UpdateItem operation:

val requestParameters =
  s"""
     |{
     |    "TableName": "custom_table_name",
     |    "Key": {
     |          "id": {
     |              "S": "custom_id"
     |          }
     |    },
     |    "UpdateExpression": "ADD user_list :newItem",
     |    "ExpressionAttributeValues": {
     |        ":newItem": { "SS": ["user1","user2"] }
     |    }
     |}
     |""".stripMargin

But I now would like to switch to PartiQL and I came up with the following idea via ExecuteStatement operation usage:

val requestParameters =
  s"""
     |{
     |    "Statement": "UPDATE custom_table_name SET user_list=SET_ADD(user_list, ?) WHERE id='custom_id'",
     |    "Parameters": [
     |     {
     |       "SS": ['user1','user2']
     |     }
     |    ]
     |}
     |""".stripMargin

For the PartiQL solution, if the item does exist, it all works as expected, but if the item does not exist I get:

"400 Bad Request"/"The conditional request failed" (as the key doesn't exist yet)

But I don't want to now having to make a second call to insert a new item under PartiQL, I would rather like both cases to be handled with just 1 API call, as it was possible via UpdateItem usage.

Any ideas how this might be possible under PartiQL?

---

Documentations:

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.update.html -> PartiQL has only SET and REMOVE https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.UpdateExpressions.html#Expressions.UpdateExpressions -> Older UpdateExpressions has SET, REMOVE, ADD & DELETE

---

(I'm making the requests directly to the dynamoDB low level api without AWS SDK usage)

Answer 1

What you're looking for is called an "upsert".

Here's a twitter thread discussing how it isn't possible in PartiQL: https://twitter.com/__steele/status/1445605614248366088