How to update multiple items with single key value in dynamodb?

Question

I am trying to update the items in the Dynamodb table. But I am facing a problem so how can I update multiple rows with one key value.

Condition:

set isActive=False where systemId='si11'

+-------------------+----------+----------+
| id(partition_key) | systemId | isActive |
+-------------------+----------+----------+
|               123 | si11     | True     |
|               124 | si11     | True     |
|               125 | si11     | True     |
|               126 | si12     | True     |
|               127 | si12     | True     |
+-------------------+----------+----------+

So how can I update multiple rows with the key systemId?

I have a solution but it is not a best practice.

1. I will create a GSI index with ID (range key) and SystemID (hash key).
2. I will query the data with the key systemId.
3. Then use the result to update each row with a hash key (id).

Please suggest a better solution to this problem.

I only have systemId for the filter key, so I can only update withsystemId and I cannot create a GSI index for systemId because it is not unique.

Answer 1

You should create a GSI for systemId.

GSI's don't have to be unique;

In a DynamoDB table, each key value must be unique. However, the key values in a global secondary index do not need to be unique.

I'd do the GSI with a hash key of systemId and possibly a range key of isActive.

Note: "sort key" and "range key" mean the same thing.

Lot's of other ways to build the GSI, mostly depends on rather or not the GSI is used only for this access pattern, or is overloaded to support multiple.

Answer 2

When first working in DynamoDB, you may be tempted to bring your SQL database knowledge with you. I know because I used to do the same thing. Don't do that! DynamoDB is not a SQL database, and the patterns you use to implement a feature in SQL may not exist in DynamoDB. This use case is one of them.

So how can you update a field for multiple items in DynamoDB? From the AWS docs:

In DynamoDB, you use the UpdateItem action to modify a single item. (If you want to modify multiple items, you must use multiple UpdateItem operations.)

So, what can you do to implement your access pattern? One way is to query(or scan) your table for the items you want to update. Then issue seperate updateItem operations one each item, setting isActive to 'false'.

Let's get a bit more detailed by describing how you could implement this with a secondary index.

Let's say you define a secondary index with a partition key of systemId (GSIPK) and the sort key of isActive (GSISK). Your table would look like this:

and the secondary index would look like this (same data, just a different view):

Notice that secondary indexes do not have a uniqueness constraint. This makes it super easy to fetch items by systemId where isActive is true. From here, your application could issue multiple updateItem operations to set the isActive field to false on items in the base table.

SUPER DUPER IMPORTANT POINT HERE: You cannot write to a secondary index.

Consider secondary indexes as another way to view your data. You defined your secondary index on what DynamoDB calls your "base table", and Dynamo takes care of maintaining that view (adding/removing items). If you create/update/delete items in your base table, the index will be updated to reflect the new reality.